DIV error

[Scottbell1234]

Hi,

This is my first forum post.

I am trying to divide values in 2 cells to show the % difference in another. However if the base value is 0 I get the DIV error and if the % difference is 100% I get the DIV error. See sample of workbook for my issue:

sample workbook.xlsx

Any help would be really appreciated.

Scott

[alansidman]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[Scottbell1234]

I am dividing "Q1 actual vs forecasted/Quarter 1".

Ideally I would like a 0% or 100%

[Scottbell1234]

I have attached a sample workbook.

[alansidman]

change your formula to =IFERROR(C2/A2,0) and copy down.

[Scottbell1234]

Hi,

Thanks for getting back so quick. It works to a certain degree. However when there has been a 100% increase this still shows as 0%.

For example if you look at row 6 there has been a 100% increase but this shows as 0%.

[edioquino]

technicaly I think row 6 should result in 0% since A6 is 0, but if you want it to return 100%, you can use the formula below.

Please Login or Register  to view this content.

[Scottbell1234]

Wow, excellent. I was trying the IF and AND commands but couldn't get it to work.

Thanks so much