Excel Remove error #DIV/0 in Excel 2003


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.
 
Ad

Advertisements

Joined
Mar 20, 2012
Messages
764
Reaction score
4
=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!
 
Joined
Aug 30, 2012
Messages
13
Reaction score
0
=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!
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.
 
Joined
Mar 20, 2012
Messages
764
Reaction score
4
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?
 
Joined
Mar 20, 2012
Messages
764
Reaction score
4
Also, if you still have the conditional formatting applied, remove it.
 
Joined
Aug 30, 2012
Messages
13
Reaction score
0
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. :blush:
 
Ad

Advertisements

Joined
Mar 20, 2012
Messages
764
Reaction score
4
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top