#DIV/0! - why
 

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...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=500178

#DIV/0! - why
 

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.



"cj21" wrote in message
...
:
: 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...o&userid=25673
: View this thread: http://www.excelforum.com/showthread...hreadid=500178
:

#DIV/0! - why
 

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?
--
Regards,
Dave


"cj21" wrote:


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...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=500178

#DIV/0! - why
 

=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
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=500178

#DIV/0! - why
 

=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


--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=500178

#DIV/0! - why
 

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)

"cj21" wrote in message
...


=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
------------------------------------------------------------------------
cj21's Profile:
http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=500178

#DIV/0! - why
 

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

--
Regards,
Dave


"cj21" wrote:


=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


--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=500178

#DIV/0! - why
 

i do press: CTRL+SHIFT+ENTER

sorry i think i messed up in my explanation. i use many formulas the
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 criteria
is right, it has worked before.

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

Chris


--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=500178

#DIV/0! - why
 

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)

"cj21" wrote in message
...

i do press: CTRL+SHIFT+ENTER

sorry i think i messed up in my explanation. i use many formulas the
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 criteria
is right, it has worked before.

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

Chris


--
cj21
------------------------------------------------------------------------
cj21's Profile:
http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=500178

#DIV/0! - why
 

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.


cj21 wrote:

=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
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=500178

--

Dave Peterson