calculating an ongoing percentage

[judoist]

I have a column which will be filled with numbers and blank spaces. I'
trying to calculate an ongoing percentage to compare the amount o
numbers with the total cells in the column.

eg

A1 =5, result =100%
A2 =7, =100%
A3 is blank =66%
A4 =4 =75%

So far i have the formula, B1: =COUNTA(A$1:A1)/ROW()
which if copied down the column should calculate the percetage for eac
row.

I keep getting the same answer of 100%. Does anyone know why

 

[Peo Sjoblom]

How are the result derived in your A column, if the "blank" is a "" then
counta will
include it, try

=COUNTIF($A$1:A1,"<>")/ROW()

or

=SUMPRODUCT(--(LEN($A$1:A1)<>0))/ROW()

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Peo Sjoblom]

Actually countif will include it too so you can go with the latter formula I
gave you

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

How are the result derived in your A column, if the "blank" is a "" then
counta will
include it, try

=COUNTIF($A$1:A1,"<>")/ROW()

or

=SUMPRODUCT(--(LEN($A$1:A1)<>0))/ROW()

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[judoist]

Hi

Both solutions don't appear to recognise whats in the A cell. It jus
seems to be dividing the total number of cells and showing result as
percentage. Results are as follows...

A1 =5, B1= 100%
A2 =8, B2= 50%
A3 is blank, B3 = 33%
A4 =7, B4= 25%
Could you please check?

Thank

 

[judoist]

Hi

The results i got was exactly the same for both formulae.

Any suggestions

 

[Peo Sjoblom]

This formula

=SUMPRODUCT(--(LEN($A$1:A1)<>0))/ROW()

put in B1 and copied down will return

100.00%
100.00%
66.67%
75.00%

using your values of

5
8
blank
7


You need to use absolute reference on the first A1, i.e. $A$1:A1

I suspect that you missed that and that you used the countif formula, as I
said the countif formula does not
work if the cell is not blank but ""

Hopefully you are not using a space to indicate blank " " as opposed to ""

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[judoist]

Excellent... that works!

Hope this helps everyone 'cause this type of calculation might be quit
common.

Regard