how do I get rid of my /div0 error

[danleaf]

I have a 2014 figure and a 2015 figure and i want to calculate the increase or decrease as a figure and as a %.
when each year has a figure that is not 0 then its ok and it works
but as soon as one of the years has a zero in then the /DIV0#! monster comes out.

One key point is that either one of the years figures could be a negative number.

PLEASE HELP..


the formula i have is;
Difference in value: =sum(2015-2014) ok it works (lets say this formula is in a cell called AM08)

Difference as a percentage: =IF(AM8>0,(SUM(2015-2014)/2014),(IF(AM8<0,(SUM(2015-2014)/2014),0)))

but i still get \div0 error.. is one of the numbers is a zero

what can i do??

i want if there is no number in 2014 or 2015 ;
if the difference is a negative, it shows -100%
if the difference is a positive, it shows 100%

PLEASE can you help me.

 

[Special-K99]

1). =SUM(2015-2014)
Is that actually what you have or are you referring to cells that represent the year 2014 and 2015?
Or are these named ranges?
If that's what you actually have that should simply be

=2015-2014

2. =IF(AM8>0,(SUM(2015-2014)/2014),(IF(AM8<0,(SUM(2015-2014)/2014),0)))

So whether AM8 is > 0 or < 0 the result is the same

SUM(2015-2014)/2014

This too can be replaced.
=IF(AM8=0,0,SUM(2015-2014)/2014)
or
=IF(AM8=0,0,IFERROR(SUM(2015-2014)/2014,0))

 

[danleaf]

1). =SUM(2015-2014)
Is that actually what you have or are you referring to cells that represent the year 2014 and 2015?
Or are these named ranges?
If that's what you actually have that should simply be

=2015-2014

2. =IF(AM8>0,(SUM(2015-2014)/2014),(IF(AM8<0,(SUM(2015-2014)/2014),0)))

So whether AM8 is > 0 or < 0 the result is the same

SUM(2015-2014)/2014

This too can be replaced.
=IF(AM8=0,0,SUM(2015-2014)/2014)
or
=IF(AM8=0,0,IFERROR(SUM(2015-2014)/2014,0))

I tried but i cannot get the result.. I think maybe i have not explained it.
here is a link to dropbox, that shows an example file.

https://www.dropbox.com/s/c63erejw1uqixc0/temp.xlsx?dl=0

if you could have a look and tell me how to do it.

thanks very much

 

[deletedalien]

Looks like you need

=IFERROR((F1-D1)/D1,1)

and format as percentage

 

[danleaf]

Thanks very much Deletedalien, but i see that that formula do not work for all my scenarios.

I have updated my file on dropbox to show the result of this formula... https://www.dropbox.com/s/c63erejw1uqixc0/temp.xlsx?dl=0

It does not work for when the difference is a negative, it should show -100%
and when the difference is zero, it should show +100%

so its not quite working.

thanks guys for all your help I just cannot get the formula.

 

[danleaf]

Hey guys

I think i cracked it but its a long long formula

can you take a look and comment on this please.

=IF(IF(prevcol>0,IFERROR((2015-2014)/2014,1),(IF(prevcol<0,IFERROR((2015-2014)/2014,-1)))),IF(prevcol>0,IFERROR((2015-2014)/2014,1),(IF(prevcol<0,IFERROR((2015-2014)/2014,-1)))),0)

prevcol = the cell that has the difference as a number.

here is my revised sheet.

Thanks for all the help guys

https://www.dropbox.com/s/c63erejw1uqixc0/temp.xlsx?dl=0

Danleaf !!