#DIV/0! How do I remove this answer?!?!?

[Rebekah]

I have multiple columns of data in another sheet and am using this formula
(entered as an array) to provide an average based on conditions, one column
reads "2a", and another reads "38" etc.

i thought that by by entering the IF formula to include "<>0", this would
count all the data and return a zero value for a blank or zero value. (I
certainly hope this is making sense to somebody!!!)

=AVERAGE(IF('Summary Days'!$D$3:$D$65536="2A",(IF('Summary
Days'!$K$3:$K$65536=38,(IF('Summary Days'!$O$3:$O$1500<>"0",'Summary
Days'!$O$3:$O$1500))))))

This formula works if in all columns there is a value above zero, but
returns "#DIV/0!" if not. This would be ok if my table wasn't presenting
data for a fixed period....
Please help!!!

 

[Bernard Liengme]

I would use SUMPRODUCT for this but since you are otherwise happy with your
array formula:
=IF(ISERROR(=AVERAGE(IF('Summary Days'!$D$3:$D$65536="2A",(IF('Summary
Days'!$K$3:$K$65536=38,(IF('Summary Days'!$O$3:$O$1500<>"0",'Summary
Days'!$O$3:$O$1500))))))),""<=AVERAGE(IF('Summary
Days'!$D$3:$D$65536="2A",(IF('Summary Days'!$K$3:$K$65536=38,(IF('Summary
Days'!$O$3:$O$1500<>"0",'Summary Days'!$O$3:$O$1500)))))))

That is
=IF(ISERROR(your_formula),"", your-formula)

best wishes

 

[Peo Sjoblom]

I don't understand what you mean, the formula works fine for me are you
entering it with ctrl + shift & enter? The only way I can get a div error is
if there are only text, zeros or blanks in column O.. You might also want to
change the size of your ranges unless you really have over 65000 entries in
your columns

Btw, you have too many parentheses

=AVERAGE(IF(D31000="2A",IF(K3:K1000=38,IF(O3:O1000<>0,O3:O1000))))

--


Regards,


Peo Sjoblom

 

[Max]

One way

Try, array-entered:
=IF(ISERROR(AVERAGE(IF(('Summary Days'!$D$3:$D$1500="2A")*('Summary
Days'!$K$3:$K$1500=38)*('Summary Days'!$O$3:$O$1500<>0),'Summary
Days'!$O$3:$O$1500))),"",AVERAGE(IF(('Summary
Days'!$D$3:$D$1500="2A")*('Summary Days'!$K$3:$K$1500=38)*('Summary
Days'!$O$3:$O$1500<>0),'Summary Days'!$O$3:$O$1500)))

(with ranges in your posted formula corrected to be identically sized)

 

[Rebekah]

Hi! Thanks for your speedy response!
I have tried what you have said and entered the formula:
=IF(ISERROR(=AVERAGE(IF('Summary Days'!$D$3:$D$65536="2A",(IF('Summary
Days'!$K$3:$K$65536=38,(IF('Summary Days'!$P$3:$P$1500<>0,'Summary
Days'!$P$3:$P$1500))))))),"0",(=AVERAGE(IF('Summary
Days'!$D$3:$D$65536="2A",(IF('Summary Days'!$K$3:$K$65536=38,(IF('Summary
Days'!$P$3:$P$1500<>0,'Summary Days'!$P$3:$P$1500))))))))

This generates a pop up box stating that there is an error within the
formula...
Sorry! Any further ideas? Or am I being quite simple and have not followed
your instructions properly?!?!?
Beks

 

[Max]

The ranges in your posted formula are inconsistent. These have to be
corrected -- all ranges identically sized -- before the array formula will
work. See my response.

 

[Rebekah]

Many thanks Max!
You are a star!

One way

Try, array-entered:
=IF(ISERROR(AVERAGE(IF(('Summary Days'!$D$3:$D$1500="2A")*('Summary
Days'!$K$3:$K$1500=38)*('Summary Days'!$O$3:$O$1500<>0),'Summary
Days'!$O$3:$O$1500))),"",AVERAGE(IF(('Summary
Days'!$D$3:$D$1500="2A")*('Summary Days'!$K$3:$K$1500=38)*('Summary
Days'!$O$3:$O$1500<>0),'Summary Days'!$O$3:$O$1500)))

(with ranges in your posted formula corrected to be identically sized)

 

[Popey]

I don't know if I am reading this right, but you do not want the "#DIV/0!" to
show?

Why not simply select all the errors in that column, open the drop down menu
that should appear immediately to the left of the error, and select "ignore
error"? This will make all those annoying error messages in the selected
column disappear. It's what I do, and it works effectively. Saves all that
messing about with altering/adding complex formulas for the same result.

 

[Peo Sjoblom]

How is a blank cell or a zero an error?


--


Regards,


Peo Sjoblom

 

[Rebekah]

Popey, this doesn't work... It gets rid of the green arrow from the cornor
but doesn't remove the #DIV/1! text and replace with a zero or blank cell...

 

[Popey]

Sorry Rebekah.

I might have thought of another simple solution:

If the values that are supposed to be in the cells are numerical, then set
the default pen colour for that cell the same as the fill colour (for
example, black pen on black background. Then using "Condtional Formatting"
instruct Excel to place any value greater than zero in white 'ink'. In that
way the errors will not show as they will be the same colour as the
background, and any value higjher than 0 will show as white (or whatever
colour you choose.)

 

[Harlan Grove]

One way

Try, array-entered:
=IF(ISERROR(AVERAGE(IF(('Summary Days'!$D$3:$D$1500="2A")
*('Summary Days'!$K$3:$K$1500=38)
*('Summary Days'!$O$3:$O$1500<>0),'Summary Days'!$O$3:$O$1500))),
"",AVERAGE(IF(('Summary Days'!$D$3:$D$1500="2A")
*('Summary Days'!$K$3:$K$1500=38)
*('Summary Days'!$O$3:$O$1500<>0),'Summary Days'!$O$3:$O$1500)))

....

Too inclusive. The OP's formula would only have returned #DIV/0! if
there had been no cells matching the criteria. That's easily tested
using COUNT.

=IF(COUNT(('Summary Days'!$D$3:$D$1500="2A")
*('Summary Days'!$K$3:$K$1500=38)
*ISNUMBER('Summary Days'!$O$3:$O$1500)),
AVERAGE(IF(('Summary Days'!$D$3:$D$1500="2A")
*('Summary Days'!$K$3:$K$1500=38)
'Summary Days'!$O$3:$O$1500)),"")

This has the advantage of propagating error values in these ranges, if
any, as well as not masking errors arising from nonconforming ranges.

 

[porter444]

How about just hidding the error response by conditionally formatting the
text white?

I run into this often, and have found this solution really good (most of the
time).

Formula for the conditional format is like:

=ISERROR(A2)

Then just set the text color to white in the "format" area.

 

[Rebekah]

Hi
Still having problems... Sorry to bother you again!
Max's result worked as long as the result was supposed to be zero, not if
the result was 1 or above... I have tried the following as you suggested, but
this again is throwing back an error message. I have tried entering the
formula with parenthesis around the last section (in case you omitted this in
error) but it still won't work?
=IF(COUNT(('Summary Days'!$D$3:$D$1500="2A")*('Summary
Days'$K$3:$K$1500=38)*ISNUMBER('Summary
Days'!$O$3:$O$1500)),AVERAGE(IF(('Summary Days'!$D$3:$D$1500="2A")*('Summary
Days'!$K$3:$K$1500=38)'Summary Days'!$O$3:$O$1500)),"")

Any more suggestions?!?!?!

Beks

 

[David Biddulph]

Two IFs, only one comma. I think Harlan may have missed a bit when he was
copying the formula.
I haven't been through the formula in detail, but comparing it with the
earlier version I wonder whether it may be missing the part that says
*('Summary Days'!$O$3:$O$1500<>0),
towards the end ?

 

[Rebekah]

thank you so much for everyone's help with this, i think I have fixed it with
a very untidy formula, although it appears to work, so I am not
complaining!!!!