Calculate % with empty cells

[Jean-Paul De Winter]

Hi,
Suppose I have a range from A1 to C6.
I need to calculate a % out of these 18 cells
The formula I use is: (sum(A1:C6)*18)/100
This works perfectly if all cells contain a value, but what if some cells
are left empty....
What should the formula be in that case??
Thanks

 

[Myrna Larson]

If your current formula is correct when all 18 cells are filled with numbers,

=SUM(A1:C6)*COUNT(A1:C6)/100

 

[MahaRaj®]

Hi,
Suppose I have a range from A1 to C6.
I need to calculate a % out of these 18 cells
The formula I use is: (sum(A1:C6)*18)/100
This works perfectly if all cells contain a value, but what if some cells
are left empty....
What should the formula be in that case??
Thanks

I would use another Cell say "E15" with formula =Count(A1:C6)
it will count the cell that have any data in it leaving empty cells.
Say you have numbers in only 15 cells so E15 would be 15.

Change your % formula to
=(Sum(A1:C6)*E15)/100

Hope this help.

MahaRaj

 

[Myrna Larson]

Just curious, but why do you suggest using another cell rather than embedding
the COUNT function in the original formula?

 

[MahaRaj®]

I agree with Myrna Larson. the formula is simple to use and you do not need
another cell i.e. E15

I actually took long time to write the first post while Myrna Posted her
post in the mean time.

 

[MahaRaj®]

could you look into my query if you can help.

I posted it 17.54 (UK). From- MahaRaj.

Thanks