Average function

[Shannakinz]

I have columns set up for each month where we enter the total number of
hours. After these columns, i have a column for "total for the year (hours)"
and for "average of the year (hours)". Right now, i've just put in a basic
average function to calculate using the columns for the months. Example:
=AVERAGE(AP13:BA13).

My problem is that some months do not have values, and i would like to
exclude them from the average calculations. Is there a way to tell excel that
it should only calculate the average of the months that have a value >0 ?

Thanks.

 

[Lars-Åke Aspelin]

I have columns set up for each month where we enter the total number of
hours. After these columns, i have a column for "total for the year (hours)"
and for "average of the year (hours)". Right now, i've just put in a basic
average function to calculate using the columns for the months. Example:
=AVERAGE(AP13:BA13).

My problem is that some months do not have values, and i would like to
exclude them from the average calculations. Is there a way to tell excel that
it should only calculate the average of the months that have a value >0 ?

Thanks.

If the cells for the months that "do not have values" are blank, you
don't have to do anythink. AVERAGE does not include blank cells in the
calculation.
But if the cells for the months that "do not have value " have 0 or
some negative number, you may try the following formula:

=AVERAGE(IF(AP13:BA13>0,AP13:BA13))

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-Åke

 

[T. Valko]

What version of Excel are you using?

This array formual** will work in all versions:

=AVERAGE(IF(AP13:BA13>0,AP13:BA13))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Shannakinz]

One of my problems is that i am creating a template for other locations to
fill in. So what i would like is for the monthly columns to start out blank
and the average column to do the calculation automatically. You're right that
if the cells for the months that "do not have values" are blank, AVERAGE does
not include blank cells in the calculation and it calculates it wonderfully
once you input hours into the monthly columns. The problem is that before you
input any values into the monthly columns, the average column shows an error
and says "#DIV/0!". And i can't send this template out with that error
showing because that's just going to confuse everyone who has to fill it out.

 

[Shannakinz]

I am using the 2003 version. When i typed that formula in, it came back with
the "#VALUE!" error.

 

[Shannakinz]

Ok, i forgot the "ctrl, shift, AND enter" part, that's why i got that error
message. But this will still show me "#DIV/0!" if all the monthly columns are
blank (i am making a template for other locations to fill in the information)
and that will confuse everyone if i send it out like that.

 

[Lars-Åke Aspelin]

Then I suggest you try the following:

=IF(COUNT(AP13:BA13)=0,"",AVERAGE(AP13:BA13))

Hope this helps / Lars-Åke

 

[Shannakinz]

That worked wonderfully! Thank you so much!

Then I suggest you try the following:

=IF(COUNT(AP13:BA13)=0,"",AVERAGE(AP13:BA13))

Hope this helps / Lars-Ã…ke

 

[T. Valko]

Ok, try this version. Still array entered:

=IF(COUNTIF(AP13:BA13,">0"),AVERAGE(IF(AP13:BA13>0,AP13:BA13)),"")

 

[T. Valko]

=IF(COUNT(AP13:BA13)=0,"",AVERAGE(AP13:BA13))

That leaves out the exclusion of 0s.