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
|
|||
|
|||
#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
|
|||
|
|||
#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:Q180,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:Q180,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 |
Thread Tools | |
Display Modes | |
|
|