Excel Remove error #DIV/0 in Excel 2003

Johfra

Johfra

Joined
Aug 30, 2012
Messages
13
Reaction score
0
Per month, sales reps enter information of calls made out, emails, etc. to clients. I want to know if he's communicated with the same client per month and how many times and what is the increase or decrease percentage.

From that, I used formula =round((february-january)/januay,2). In that cell, I get a +/- result. BUT, when I set up the conditional formatting to remove #DIV/0, it also removes the negative results, which I need to see.

I know there's a formula out there and that doesn't need the conditional formatting but I have yet to find it. I have searched for days, tried many and none produced the end result I wanted.

Thank you.
 
=IF(ISERROR(ROUND((February-January)/January,2)),"",ROUND((February-January)/January,2))

Give that a try, I haven't tested it, just typed it out here, so there may be some user error involved, but I believe it will do the trick. If you want to show a 0 for places with an error, you would replace "" with "0". Also, just to let you know, this will hide ANY errors in the formula, not just #DIV/0!
 
alow said:
=IF(ISERROR(ROUND((February-January)/January,2)),"",ROUND((February-January)/January,2))

Give that a try, I haven't tested it, just typed it out here, so there may be some user error involved, but I believe it will do the trick. If you want to show a 0 for places with an error, you would replace "" with "0". Also, just to let you know, this will hide ANY errors in the formula, not just #DIV/0!
Click to expand...
Hi - thanks for the tip. I tried it and it didn't work. Ugh....this is so frustrating!

Believe it or not, I have JUST been upgraded to Excel 2010. Tried the formula there, too and it didn't work.

Again, my goal is to have a percentage of increase OR decrease from sales from the current month to the previous month.
 
What do you get with the formula I gave you? It should show the correct results unless there's an error. I'm assuming by the formula that you have that February and January are named ranges?
 
Also, if you still have the conditional formatting applied, remove it.
 
Hi again,

Believe me, I do thoroughly appreciate your time and patience in helping me. Where I work, I am but a handful of people who is knowledgeable in Excel (work in a hospital with over 1000 administrators). I love Excel but there are times when I am stumped...as is this case.

I am self-taught and have been able to figure pretty much most of it out, except complex functions, such as this.

The result stays at 0 despite any changes. I mean, to test, I put 2 in January and in February a 1. End result should be a decrease of 50% (-50%). Which is correct with your formula. However, if I put 2 in February and 1 in January, the results remain at 0 where it should be 100% (or +100%) - even if putting a 0 in January and a 1 in February, it should also be an increase of 100% but it still puts it as 0.

Thanks again. :o
 
It works for me. Select the cells that have the formula, go to the Home Tab and click on Clear | Formats. then you should see -.5 for a 50% decrease or 1 for a 100% increase. If that's the case, then you have formatting that's causing the issue.
 
Back
Top