Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#DIV/0! error
I have the following formula entered to compute the average of a range of
cells and discount zeros. Until I change one of the zeros to a number, I get #DIV/0!, How do I get rid of this My formula is: =AVERAGE(Q8:Q18,"<0",Q8:Q18) -- dbconn |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
#DIV/0! error
That formula will not give you either your required value or a #DIV/0!
error. It will return #VALUE! because one of the 23 values which you have asked it to average is the text string "<0". The other 22 numbers which you are asking it to average are two instances each of the elements in the array Q8:Q18. Perhaps you have fallen into the trap of typing into the newsgroup the formula that you thought you were using, rather than copying the actual formula from the formula bar and pasting it here. The formula which you may have intended might have been the *array* formula =AVERAGE(IF(Q8:Q18<0,Q8:Q18,"")) [entered with Control Shift Enter]. That formula will indeed return #DIV/0! if there are no non=zero numbers in the array Q8:Q18, so you may want something like =IF(COUNT(Q8:Q18)-COUNTIF(Q8:Q18,0)=0,"",AVERAGE(IF(Q8:Q18<0,Q8:Q18 ,""))) [again as an array formula]. -- David Biddulph "dbconn" wrote in message ... I have the following formula entered to compute the average of a range of cells and discount zeros. Until I change one of the zeros to a number, I get #DIV/0!, How do I get rid of this My formula is: =AVERAGE(Q8:Q18,"<0",Q8:Q18) -- dbconn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 - error saving file & error loading dll | Excel Discussion (Misc queries) | |||
Error of slope taking into account error of the data points | Excel Worksheet Functions | |||
Error: "Excel encountered an error and had to remove some formatti | Excel Discussion (Misc queries) | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions |