Calculating averages when some cells are blank?

  • Thread starter Thread starter Pheasant Plucker®
  • Start date Start date
P

Pheasant Plucker®

Hi there,

I am hoping some kind soul may be able to help me please?

I am not sure what the correct term is for what I am trying to achieve so
the Help file is useless to me at the moment...:-(

I have a simple spreadsheet where the sum of six columns are summed at the
end to give the average i.e. =SUM(C19:H19)/6

That causes no problem when all six columns contain data but...and there's
always a but...what if some cells are empty and will be filled at a later
date?

If I leave the formula as (C19:H19)/6 and have any blank cells then it won't
reflect the true average.

I still want to maintain the current average but I might only have two cells
that contain data with four blank cells.

Obviously I would not want to keep changing the formula */2 and then */3
etc. as the cells fill with data so what formula would I use to set the
division number by only the amount of cells that contain data?

I guess this would then keep a running tally and automatically update itself
as the other cells fill with data.

I hope I am making myself clear...if I feel confused typing this then I have
no hope in hell of figuring out the formula without assistance! ;^)
 
Hi,

Use the AVERAGE function for this task, it will ignore
empty cells and cells with text.

Regards,

Jan Karel Pieterse
Excel TA/MVP
 
Many thanks for the quick reply Andy...

How did I overlook the obvious?? ;^)

It really was as simple as that...

Thanx & regards,
-pp-
 
Hi Jan,

Many thanks - so simple & obvious I couldn't see it!

<blush>

Thanx & regards,
-pp-
 
Hi Myrna,

Thanks for the detailed response.

What a lot to learn - I am lucky I managed to stay away from Excel for so
long...;^)

<sound of mind being blown>

Next question coming soon...:-)

Regards,
-pp-

Myrna Larson said:
The solution here is to use AVERAGE, as others have mentioned. But there may be cases where
Excel doesn't have a built-in function. In that case, to eliminate the need to manually change
the denominator as the number of filled cells changes, you write the formula as

=SUM(C19:H19)/COUNT(C19:H19)

Or, let's say you wanted to exclude cells containing 0's:

=SUM(C19:H19)/COUNTIF(C19:H19,"<>0")
Click to expand...
 
Back
Top