Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Same formulas, but one returns a #DIV/0! error
In one cell I have the formula
=IF(ISBLANK(C16)," ",G16/C16) which returns a 0 value in a particular cell if the numbers in the corresponding cells are 0. The problem is, I have the same formula copied to reflect a different row's calculations =IF(ISBLANK(C23)," ",G23/C23) but see instead a #DIV/0! instead of a 0. What am I missing? Thank you for your time. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Same formulas, but one returns a #DIV/0! error
Maybe you can just check to see if the cell is empty or equal to 0?
=IF(C23=0,"",G23/C23) Judy wrote: In one cell I have the formula =IF(ISBLANK(C16)," ",G16/C16) which returns a 0 value in a particular cell if the numbers in the corresponding cells are 0. The problem is, I have the same formula copied to reflect a different row's calculations =IF(ISBLANK(C23)," ",G23/C23) but see instead a #DIV/0! instead of a 0. What am I missing? Thank you for your time. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Same formulas, but one returns a #DIV/0! error
What am I missing?
What's in C23? For instance if you have a space in C23 (and Tools/Options/Compatibility - Transition formula evaluation checked) you'll get #DIV/0. In article , Judy .(donotspam) wrote: In one cell I have the formula =IF(ISBLANK(C16)," ",G16/C16) which returns a 0 value in a particular cell if the numbers in the corresponding cells are 0. The problem is, I have the same formula copied to reflect a different row's calculations =IF(ISBLANK(C23)," ",G23/C23) but see instead a #DIV/0! instead of a 0. What am I missing? Thank you for your time. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Same formulas, but one returns a #DIV/0! error
In those cases where you're seeing the #DIV/0! error, the cell in column C
probably has an 'invisible character, such as " " (a single space). Best way to handle this is to specifically look for an error condition: =IF(ISERR(G23/C23),"",G23/C23) same for the other formulas as = IF(ISERR(G16/C16),"",G16/C16) etc. etc. "Judy" wrote: In one cell I have the formula =IF(ISBLANK(C16)," ",G16/C16) which returns a 0 value in a particular cell if the numbers in the corresponding cells are 0. The problem is, I have the same formula copied to reflect a different row's calculations =IF(ISBLANK(C23)," ",G23/C23) but see instead a #DIV/0! instead of a 0. What am I missing? Thank you for your time. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Same formulas, but one returns a #DIV/0! error
Try this =IF(C23=0,"",G23/C23) If C23 is empty then it equals zero in this formula -- Regards, Peo Sjoblom "Judy" .(donotspam) wrote in message ... In one cell I have the formula =IF(ISBLANK(C16)," ",G16/C16) which returns a 0 value in a particular cell if the numbers in the corresponding cells are 0. The problem is, I have the same formula copied to reflect a different row's calculations =IF(ISBLANK(C23)," ",G23/C23) but see instead a #DIV/0! instead of a 0. What am I missing? Thank you for your time. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Same formulas, but one returns a #DIV/0! error
"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... In those cases where you're seeing the #DIV/0! error, the cell in column C probably has an 'invisible character, such as " " (a single space). Best way to handle this is to specifically look for an error condition: =IF(ISERR(G23/C23),"",G23/C23) same for the other formulas as = IF(ISERR(G16/C16),"",G16/C16) etc. etc. I beg to disagree, it is better to go to the root of the problem, ISERR might hide errors that are beneficial to know that they exit whereas the div error derives from dividing by zero and can be easily fixed by C23=0 -- Regards, Peo Sjoblom |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Same formulas, but one returns a #DIV/0! error
Thank you so much for the responses. This suggestion worked...sort of.
After using the suggested formula I have a blank cell (no more errors!) but the cell formatting is lost. So even though the cell formatting indicates percentage with one decimal point (the cell should read 0.0%) it is blank instead. I'll keep trying.... "Peo Sjoblom" wrote: Try this =IF(C23=0,"",G23/C23) If C23 is empty then it equals zero in this formula -- Regards, Peo Sjoblom "Judy" .(donotspam) wrote in message ... In one cell I have the formula =IF(ISBLANK(C16)," ",G16/C16) which returns a 0 value in a particular cell if the numbers in the corresponding cells are 0. The problem is, I have the same formula copied to reflect a different row's calculations =IF(ISBLANK(C23)," ",G23/C23) but see instead a #DIV/0! instead of a 0. What am I missing? Thank you for your time. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Same formulas, but one returns a #DIV/0! error
So, whatever you put in the if formula will be returned, just change it to
=IF(C23=0,0,G23/C23) -- Regards, Peo Sjoblom "Judy" .(donotspam) wrote in message ... Thank you so much for the responses. This suggestion worked...sort of. After using the suggested formula I have a blank cell (no more errors!) but the cell formatting is lost. So even though the cell formatting indicates percentage with one decimal point (the cell should read 0.0%) it is blank instead. I'll keep trying.... "Peo Sjoblom" wrote: Try this =IF(C23=0,"",G23/C23) If C23 is empty then it equals zero in this formula -- Regards, Peo Sjoblom "Judy" .(donotspam) wrote in message ... In one cell I have the formula =IF(ISBLANK(C16)," ",G16/C16) which returns a 0 value in a particular cell if the numbers in the corresponding cells are 0. The problem is, I have the same formula copied to reflect a different row's calculations =IF(ISBLANK(C23)," ",G23/C23) but see instead a #DIV/0! instead of a 0. What am I missing? Thank you for your time. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF - returns name error | Excel Worksheet Functions | |||
SUM returns #VALUE! error | Excel Worksheet Functions | |||
GetpivotData returns a #REF! error | Excel Worksheet Functions | |||
Condition now returns error | Excel Worksheet Functions | |||
RATE returns the #NUM! error value | Excel Worksheet Functions |