Average function
 

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.

Average function
 

On Fri, 10 Jul 2009 12:28:24 -0700, Shannakinz
wrote:

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:BA130,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

Average function
 

What version of Excel are you using?

This array formual** will work in all versions:

=AVERAGE(IF(AP13:BA130,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.

--
Biff
Microsoft Excel MVP


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

Average function
 

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.


"Lars-Ã…ke Aspelin" wrote:

On Fri, 10 Jul 2009 12:28:24 -0700, Shannakinz
wrote:

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:BA130,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

Average function
 

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

"T. Valko" wrote:

What version of Excel are you using?

This array formual** will work in all versions:

=AVERAGE(IF(AP13:BA130,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.

--
Biff
Microsoft Excel MVP


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

Average function
 

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.

"T. Valko" wrote:

What version of Excel are you using?

This array formual** will work in all versions:

=AVERAGE(IF(AP13:BA130,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.

--
Biff
Microsoft Excel MVP


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

Average function
 

Then I suggest you try the following:

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

Hope this helps / Lars-Åke


On Fri, 10 Jul 2009 13:23:17 -0700, Shannakinz
wrote:

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.


"Lars-Åke Aspelin" wrote:

On Fri, 10 Jul 2009 12:28:24 -0700, Shannakinz
wrote:

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:BA130,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

Average function
 

That worked wonderfully! Thank you so much!

"Lars-Ã…ke Aspelin" wrote:

Then I suggest you try the following:

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

Hope this helps / Lars-Ã…ke


On Fri, 10 Jul 2009 13:23:17 -0700, Shannakinz
wrote:

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.


"Lars-Ã…ke Aspelin" wrote:

On Fri, 10 Jul 2009 12:28:24 -0700, Shannakinz
wrote:

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:BA130,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

Average function
 

Ok, try this version. Still array entered:

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

--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:

What version of Excel are you using?

This array formual** will work in all versions:

=AVERAGE(IF(AP13:BA130,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.

--
Biff
Microsoft Excel MVP


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

Average function
 

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

That leaves out the exclusion of 0s.

--
Biff
Microsoft Excel MVP


"Lars-Åke Aspelin" wrote in message
...
Then I suggest you try the following:

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

Hope this helps / Lars-Åke


On Fri, 10 Jul 2009 13:23:17 -0700, Shannakinz
wrote:

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.


"Lars-Åke Aspelin" wrote:

On Fri, 10 Jul 2009 12:28:24 -0700, Shannakinz
wrote:

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:BA130,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