|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 20th, 2010, 05:46 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average with #DIV/0!
I'm having a problem with an Average formula. When I use a solution to a
similar question =AVERAGE(IF(ISNUMBER(B8:M8),B8:M8,FALSE)), it eliminates the #DEV/O! errors. However; I do not want to include cells with a zero value. I have gotten around this by using: IF(B8:M8=0,"",AVERAGE(IF(B8:M80,B8:M8,""))) BUT - if there is no value in cell B8 - the formula returns with nothing, if there is a value in B8, the formula works as expected. I find the very bizarre. 
|
|
#2
May 20th, 2010, 05:57 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average with #DIV/0!
Try this array formula** :
=AVERAGE(IF(ISNUMBER(B8:M8),IF(B8:M80,B8:M8))) ** 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 "TG Engel" TG wrote in message ... I'm having a problem with an Average formula. When I use a solution to a similar question =AVERAGE(IF(ISNUMBER(B8:M8),B8:M8,FALSE)), it eliminates the #DEV/O! errors. However; I do not want to include cells with a zero value. I have gotten around this by using: IF(B8:M8=0,"",AVERAGE(IF(B8:M80,B8:M8,""))) BUT - if there is no value in cell B8 - the formula returns with nothing, if there is a value in B8, the formula works as expected. I find the very bizarre.
|
|
#3
May 20th, 2010, 06:25 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average with #DIV/0!
=IF(COUNT(B8:M8),AVERAGE(IF(ISNUMBER(B8:M8),IF(B8: M80,B8:M8))),"")
ctrl+shift+enter, not just enter "TG Engel" wrote: I'm having a problem with an Average formula. When I use a solution to a similar question =AVERAGE(IF(ISNUMBER(B8:M8),B8:M8,FALSE)), it eliminates the #DEV/O! errors. However; I do not want to include cells with a zero value. I have gotten around this by using: IF(B8:M8=0,"",AVERAGE(IF(B8:M80,B8:M8,""))) BUT - if there is no value in cell B8 - the formula returns with nothing, if there is a value in B8, the formula works as expected. I find the very bizarre.
|
|
#4
May 20th, 2010, 09:41 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average with #DIV/0!
Another one:
=SUMIF(b8:m8,""&1E+199) / (COUNT(b8:m8)-COUNTIF(b8:m8,0)) 1E+199 is a very large number in scientific format. TG Engel wrote: I'm having a problem with an Average formula. When I use a solution to a similar question =AVERAGE(IF(ISNUMBER(B8:M8),B8:M8,FALSE)), it eliminates the #DEV/O! errors. However; I do not want to include cells with a zero value. I have gotten around this by using: IF(B8:M8=0,"",AVERAGE(IF(B8:M80,B8:M8,""))) BUT - if there is no value in cell B8 - the formula returns with nothing, if there is a value in B8, the formula works as expected. I find the very bizarre. -- Dave Peterson
|
|
#5
May 23rd, 2010, 05:35 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average with #DIV/0!
Hi,
You may try this array formula (Ctrl+Shift+Enter) =average(if((isnumber(B8:M8)*(B8:M80)),B8:M8)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "TG Engel" TG wrote in message ... I'm having a problem with an Average formula. When I use a solution to a similar question =AVERAGE(IF(ISNUMBER(B8:M8),B8:M8,FALSE)), it eliminates the #DEV/O! errors. However; I do not want to include cells with a zero value. I have gotten around this by using: IF(B8:M8=0,"",AVERAGE(IF(B8:M80,B8:M8,""))) BUT - if there is no value in cell B8 - the formula returns with nothing, if there is a value in B8, the formula works as expected. I find the very bizarre.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
