Calculate % with empty cells
 

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

Calculate % with empty cells
 

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

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

On Tue, 19 Oct 2004 20:49:19 GMT, "Jean-Paul De Winter"
wrote:

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

Calculate % with empty cells
 

"Jean-Paul De Winter" wrote in message
...
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

Calculate % with empty cells
 

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

On Tue, 19 Oct 2004 21:07:15 GMT, "MahaRaj®" wrote:


"Jean-Paul De Winter" wrote in message
...
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

Calculate % with empty cells
 

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®" wrote in message
...

"Jean-Paul De Winter" wrote in message
...
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

Calculate % with empty cells
 

could you look into my query if you can help.

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

Thanks


"Myrna Larson" wrote in message
...
Just curious, but why do you suggest using another cell rather than
embedding
the COUNT function in the original formula?

On Tue, 19 Oct 2004 21:07:15 GMT, "MahaRaj®"
wrote:


"Jean-Paul De Winter" wrote in message
...
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