# #DIV/0! with a percent

#### Executioner

##### Board Regular
Hi everyone,
I can't figure out what I'm doing wrong with a simple calculation. I have a spreadsheet that determines what percent increase over a previous quarter. The values can be negative or positive; however, I have one entry that I'm trying to divide zero by a number which results in the #DIV/0! error message. I rather have it say -1000% since that is the value I'm looking for. I now how to deal with a simple division by using an IF statement such as IF(B1,A1/B1,0), but this one is throwing me a curve. I would appreciate any help.

The attached spread sheet is a quarterly percent increase over the last one. In the example, N00377 represents a machine in cell D14 and D17, where cell D17 is the last quarter, and I'm comparing it to cell D14 which should show an increase or decrease in cell F.
CEDR Top 10 History.xls
DEFG
14N003778-75.0%
15N15951118.2%
16EDD2047808147.1%
17N003772-300.0%
18N15953-266.7%
19EDD20478080#DIV/0!
CAPA

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### venkat1926

##### Well-known Member
the formula in F19 is =(E19-E16)/E19 that is division by E19 which is zero. you cannot divide by zero (principle in Arithmetic)

go to F14 change the formula as below

=if (e14=0,"",(E14-E11)/E14)

copy the formula in F14 down.

#### zzjasonzz

##### Well-known Member
=if(E19=0,"",((E19-E16)/E19))

#### Executioner

##### Board Regular
Thanks for the formatting of the equation (yeah I know you can't divided by zero). In my situation in the spreadsheet, I have to show a percent increase or decrease from the previous quarter. It just happens that I had this one beginning with zero and did not want to show the divide by zero error on the spreadsheet.

#### Executioner

##### Board Regular
Just another update on this. After doing more research and asking other math whizzes, I can get my answer by changing my equation. In my example above:
14 (represents the previous quarter)
0 (represents the current ending quarter)
I was using the simple formula: (0-14)/0 *100 = ±percent

I should be using this one:
14=100% and 0 is 14 times less than 14 so:
100% + x = 14(100%)
x=1,400-100
x=1,300%

I can also use it for the others, and it comes out correctly:
Q1=8
Q2=2
8=100%. Then 2 is 4 times less than 8 so:
100% + X = 4(100%)
x=400-100
x=300%
(old way: (2-8)/2 *100 = -300%)

How would this look in an Excel formula?

##### MrExcel MVP
Just another update on this. After doing more research and asking other math whizzes, I can get my answer by changing my equation. In my example above:
14 (represents the previous quarter)
0 (represents the current ending quarter)
I was using the simple formula: (0-14)/0 *100 = ±percent

I should be using this one:
14=100% and 0 is 14 times less than 14 so:
100% + x = 14(100%)
x=1,400-100
x=1,300%

I can also use it for the others, and it comes out correctly:
Q1=8
Q2=2
8=100%. Then 2 is 4 times less than 8 so:
100% + X = 4(100%)
x=400-100
x=300%
(old way: (2-8)/2 *100 = -300%)

How would this look in an Excel formula?
Book3
ABCD
1
21401300.00%
382300.00%
4
5
Sheet1

D2:

=A2/IF(B2,B2,1)-100%

#### mortgageman

##### Well-known Member
Just another update on this. After doing more research and asking other math whizzes, I can get my answer by changing my equation. In my example above:
14 (represents the previous quarter)
0 (represents the current ending quarter)
I was using the simple formula: (0-14)/0 *100 = ±percent

I should be using this one:
14=100% and 0 is 14 times less than 14 so:
100% + x = 14(100%)
x=1,400-100
x=1,300%

I can also use it for the others, and it comes out correctly:
Q1=8
Q2=2
8=100%. Then 2 is 4 times less than 8 so:
100% + X = 4(100%)
x=400-100
x=300%
(old way: (2-8)/2 *100 = -300%)

How would this look in an Excel formula?

Huh?? You go from 14 to zero and you call that a 1300% increase? And BTW zero is NOT 14 TIMES less than 14. zero is 14 less (no times) than 14.

#### Executioner

##### Board Regular
I need the answer in negative or positive format. I think this will work:

Code:
``=(IF(E19<E16,-1*((IF(E19=0,E16,(E16/E19))*100)-100),(IF(E16=0,E19,(E19/E16))*100)-100))``

Huh?? You go from 14 to zero and you call that a 1300% increase? And BTW zero is NOT 14 TIMES less than 14. zero is 14 less (no times) than 14.
No, it's a decrease. Any negative value will be a decrease. I'm targeting +25% as my point where I have to generate a CAPA report for the FDA.

Thanks everyone for their input! #### barry houdini

##### MrExcel MVP
What you seem to want is the percentage increase (shown as negative if decrease) from E16 to E19.

You do this by dividing the change by one of the values. It makes more sense to me to divide the change by the start value but you seem to want to divide the change by the end value.

The formula for that is

=(E19-E16)/E19

which is the same as

=1-E16/E19

format as %

This gives an error when E19 is zero as you describe above. If you want to use 1 instead of zero in this case (which is what your formula above does) then change to

=1-E16/IF(E19,E19,1)

format as %

I'd still be sceptical about the mathematical validity of this approach though, it means that a reduction from 10 to 1 is a -900% change, a reduction from 10 to zero is also a -900% change but a reduction to an amount between those, 10 to 0.5, is a -1900% change!

#### Executioner

##### Board Regular
barry - you might be on to something. What I did is to show all 3 different ways of doing this, and if I start by saying my previous quarter is the start value, then my answer for the example is 100% improvement which makes sense.
I don't know why I was thinking that the new quarter would be the start value.

Replies
3
Views
247
Replies
4
Views
139
Replies
10
Views
1K
Replies
3
Views
750
Replies
0
Views
157

### Forum statistics

1,191,685
Messages
5,988,002
Members
440,125
Latest member
vincentchu2369 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back