Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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 : |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
#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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to sum cells and ignore the #div/0! 's ? | Excel Worksheet Functions | |||
Circular Reference will not solve #VALUE! or #DIV/0! | Excel Discussion (Misc queries) | |||
replace "#DIV/0!" error with blanks | Excel Worksheet Functions | |||
# DIV/0! error in Excel | Excel Discussion (Misc queries) | |||
"Average" with error DIV/0 | Excel Discussion (Misc queries) |