#DIV/0! with a percent

[Executioner]

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
	D	E	F	G
14	N00377	8	-75.0%
15	N1595	11	18.2%
16	EDD2047808	14	7.1%
17	N00377	2	-300.0%
18	N1595	3	-266.7%
19	EDD2047808	0	#DIV/0!
CAPA

 

[venkat1926]

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]

=if(E19=0,"",((E19-E16)/E19))

 

[Executioner]

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]

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?

 

[Aladin Akyurek]

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
	A	B	C	D
1
2	14	0		1300.00%
3	8	2		300.00%
4
5
Sheet1

D2:

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

 

[mortgageman]

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]

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

=(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]

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]

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.