#DIV/0! - why

[cj21]

I insert a formula into my spreadsheet and i end up with the above
message. However my formula does not divide by zero. Any ideas?

Chris

 

[Lady Layla]

What is your formula?

If your formula is a1/b1 and b1's value is = 0 or is blank, you will get the
#DIV/0 error message. You can change the formula in several ways to remove that
message

One way instead of hving +A1/B1 as the formula, have IF(B1=0,0,A1/B1) I am
sure there are more efficient ways to write this.



:
: I insert a formula into my spreadsheet and i end up with the above
: message. However my formula does not divide by zero. Any ideas?
:
: Chris
:
:
: --
: cj21
: ------------------------------------------------------------------------
: cj21's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=25673
: View this thread: http://www.excelforum.com/showthread.php?threadid=500178
:

 

[Guest]

Well, first off, your formula must divide by 0 to get this error message.

What is your formula? It may be you are calculating on what appears to be
numbers, but they are formatted as text. Try this: Copy an empty cell,
select the range of "numbers" you are trying to calculate on, then do a
"Paste Special->Add" and see if this error disappears.

Does that help?

 

[cj21]

=AVERAGE(IF(--(LEFT(TEXT($D$2:$D$5988,"00000000"),2)=TEXT(M3,"00")),$H$2:$H$5988))

This is my formula. It is a bit complicated but there is not a mistake.
I have used it for other data sets which are exactly the same and it
works.

Chris

 

[cj21]

=AVERAGE(IF(--(LEFT(TEXT($D$2:$D$5988,"00000000"),2)=TEXT(M3,"00")),$H$2:$H$5988))

colomn D is a list of product codes. Some of which begin with a zero so
the column is formated as text.

column M3 is formated as a number.

Column H is formated as a number.

This has been the same for other work i have done and my formula works.
For some reason in this case it does not.

Chris

 

[Bob Phillips]

It is because none of the cells match the criteria so there is nothing to
average.

Try this array formula to demonstrate it

=IF(SUMPRODUCT(--LEFT(TEXT($D$2:$D$5988,"00000000"),2)=TEXT(M3,"00"))=0,"No
matches",AVERAGE(IF((--LEFT(TEXT($D$2:$D$5988,"00000000"),2)=TEXT(M3,"00")),
$H$2:$H$5988)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

This is an array formula, are you commiting it by pressing CTRL+SHIFT+ENTER?

 

[cj21]

i do press: CTRL+SHIFT+ENTER

sorry i think i messed up in my explanation. i use many formulas th
sum is one the average is another, i also find the mean, mode etc
These are all seperate and reported in there own column. The criteri
is right, it has worked before.

However there is a green triangle in the top left corner of the data i
column H and D

Chri

 

[Bob Phillips]

I didn't say it was because you didn't array-enter, I said no data met the
condition! And I gave you a formula to prove it.
--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Dave Peterson]

Another way to write your formula:

=AVERAGE(IF(--($D$2:$D$5988<1000000),$H$2:$H$5988))
(still ctrl-shift-entered)

But don't you get results that are misleading if you have empty cells?

This looks like it would do the same:
=SUMIF($D$2:$D$5988,"<"&1000000,$H$2:$H$5988)/COUNTIF($D$2:$D$5988,"<"&1000000)

And you could add that check for dividing by 0:

=IF(COUNTIF($D$2:$D$5988,"<"&1000000)=0,"no data",
SUMIF($D$2:$D$5988,"<"&1000000,$H$2:$H$5988)/COUNTIF($D$2:$D$5988,"<"&1000000))

And I'd check for div/0 errors in the original range, too.