Excel Remove error #DIV/0 in Excel 2003

[Johfra]

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.

 

[alow]

=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!

 

[Johfra]

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

 

[alow]

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?

 

[alow]

Also, if you still have the conditional formatting applied, remove it.

 

[Johfra]

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.

 

[alow]

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.