Average Function

[Stacy C]

Is there anyway to establish an Average function that adjusts to only
populated fields?

Example:

I have a spreadsheet established that pulls from 12 other sheets. As a
conseqence,the fields on this sheet only update each month (the other sheets
are monthly sheets, and thus each one is used within the appopriate month).

I would like to AVERAGE the fields on the13th sheet, showing a monthly
average of the values derived from the other 12. However, I want the AVERAGE
to reflect only the populated fields, so that I receive an accurate "running"
average. Ex: if only January and February have data, then the average would
be divided by 2, not all 12 fields.

Is there any way to accomplish this? I figure it is a combination of the IF
and AVERAGE formulas, but I've no idea how to do this.

Thank you in advance.

 

[JoeU2004]

I want the AVERAGE
to reflect only the populated fields, so that I receive an accurate
"running"
average. Ex: if only January and February have data, then the average
would
be divided by 2, not all 12 fields.

Are you using AVERAGE, as you say, or AVERAGEA?

AVERAGE, not AVERAGEA, behaves the way you wish, ignoring empty cells as
well as cells with text, notably "" (null string).

I suspect the root cause of your problem is the way in which you are pulling
data from the other worksheets into the 13th worksheet. Use a formula like
the following:

=if(sheet2!A1="", "", sheet2!A1)

If you do simply =sheet2!A1, you will get zero instead of "" in the
corresponding cell in the 13th worksheet.


----- original message -----

 

[Stacy C]

I am using Average, but as you say, it is the pull of the data. I will adjust
my pull method and try it out. Thank you.