LinkBack URL
About LinkBacks
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
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
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" <[email protected]> wrote in message
news:[email protected]...
:
: 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
:
=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
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
>
>
=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
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" <[email protected]> wrote in message
news:[email protected]...
>
>
=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
>
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
>
>
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
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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks