Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages formula/0
I have an entire column with #DIV/0! because it is awaiting data from
adjacent cells. The sum of the formulas result in a %. Example: B2 10% B3 15% B4 #DIV/0! B5 #DIV/0! B6 25% QUESTION: How can I get the % average of B2:B6? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages formula/0
Try this:
Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(ISNUMBER(B2:B6),B2:B6)) Format as % You'd be better off fixing the errors so that those formulas return a blank ("") rather than the #DIV/0! error. Then a simple =AVERAGE(B2:B6) will work. Biff "rldjda" wrote in message ... I have an entire column with #DIV/0! because it is awaiting data from adjacent cells. The sum of the formulas result in a %. Example: B2 10% B3 15% B4 #DIV/0! B5 #DIV/0! B6 25% QUESTION: How can I get the % average of B2:B6? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages formula/0
=SUM(B2:B6)/(COUNTA(B2:B6)-COUNTBLANKS(B2:B6))
Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "rldjda" wrote: I have an entire column with #DIV/0! because it is awaiting data from adjacent cells. The sum of the formulas result in a %. Example: B2 10% B3 15% B4 #DIV/0! B5 #DIV/0! B6 25% QUESTION: How can I get the % average of B2:B6? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages formula/0
Put your functions in this
=IF(ISERROR(function),"",function) -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "rldjda" wrote: I have an entire column with #DIV/0! because it is awaiting data from adjacent cells. The sum of the formulas result in a %. Example: B2 10% B3 15% B4 #DIV/0! B5 #DIV/0! B6 25% QUESTION: How can I get the % average of B2:B6? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages formula/0
You could do that by using the formula
=AVERAGE(IF(ISNUMBER(B2:B6),B2:B6)) confirmed with CTRL+SHIFT+ENTER althought it might be better to replace your #DIV/0! error with a blank, allowing you to use just =ABVERAGE(B2:B6) If your formula in B2 is something like =X3/B1 change to =IF(B1,X3/B1,"") "rldjda" wrote: I have an entire column with #DIV/0! because it is awaiting data from adjacent cells. The sum of the formulas result in a %. Example: B2 10% B3 15% B4 #DIV/0! B5 #DIV/0! B6 25% QUESTION: How can I get the % average of B2:B6? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages formula/0
I don't get an error but the formula results with 0% and there are % values
entered in the column. "T. Valko" wrote: Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(ISNUMBER(B2:B6),B2:B6)) Format as % You'd be better off fixing the errors so that those formulas return a blank ("") rather than the #DIV/0! error. Then a simple =AVERAGE(B2:B6) will work. Biff "rldjda" wrote in message ... I have an entire column with #DIV/0! because it is awaiting data from adjacent cells. The sum of the formulas result in a %. Example: B2 10% B3 15% B4 #DIV/0! B5 #DIV/0! B6 25% QUESTION: How can I get the % average of B2:B6? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages formula/0
The only thing that I can see that might be causing this is if your values
are actually smaller than you think they are and you have the format set in a certain way. See this screencap: http://img126.imageshack.us/img126/7095/averagemp3.jpg The result in B8 is based on your posted example. The numbers in column C are formatted as GENERAL. Biff "rldjda" wrote in message ... I don't get an error but the formula results with 0% and there are % values entered in the column. "T. Valko" wrote: Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(ISNUMBER(B2:B6),B2:B6)) Format as % You'd be better off fixing the errors so that those formulas return a blank ("") rather than the #DIV/0! error. Then a simple =AVERAGE(B2:B6) will work. Biff "rldjda" wrote in message ... I have an entire column with #DIV/0! because it is awaiting data from adjacent cells. The sum of the formulas result in a %. Example: B2 10% B3 15% B4 #DIV/0! B5 #DIV/0! B6 25% QUESTION: How can I get the % average of B2:B6? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averages formula/0
Just wanted to thank you all and tell anyone else looking who is dealing with
#DIV/0 the formula below is the one that works. Be certain to press CTRL+SHIFT+ENTER once you've entered the formula and format that cell for percentage. "daddylonglegs" wrote: You could do that by using the formula =AVERAGE(IF(ISNUMBER(B2:B6),B2:B6)) confirmed with CTRL+SHIFT+ENTER althought it might be better to replace your #DIV/0! error with a blank, allowing you to use just =ABVERAGE(B2:B6) If your formula in B2 is something like =X3/B1 change to =IF(B1,X3/B1,"") "rldjda" wrote: I have an entire column with #DIV/0! because it is awaiting data from adjacent cells. The sum of the formulas result in a %. Example: B2 10% B3 15% B4 #DIV/0! B5 #DIV/0! B6 25% QUESTION: How can I get the % average of B2:B6? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating averages (olympic style) | Excel Worksheet Functions | |||
I figured out how to do total averages in Pivot Charts! | Charts and Charting in Excel | |||
Pivot Chart--I want total average, not sum of averages | Charts and Charting in Excel | |||
similar to sumif() but for averages | Excel Discussion (Misc queries) | |||
Calculating weighted averages | Excel Discussion (Misc queries) |