Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible to use address function in average function | Excel Discussion (Misc queries) | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
I want to use the MATCH function with the AVERAGE function but I . | Excel Worksheet Functions | |||
How do I nesting subtotal function within average function in Exc | Excel Worksheet Functions | |||
Average function with If | Excel Worksheet Functions |