getting div error

[accablues]

hi, I am trying to return the forumale value to "-". see below



D11 cell = 0
F11 cell = 58,000

i am calculating year on year increase in expense where D11=2011 EXPENSE and F11=2012 EXPENSE

formuale used is =SUM(F11-D11)/D11 which returns with #Div/0! error

I want this to change to "-" regardless the D11 or F11 value becomes zero. please help it's looking very odd on my report

I hope i have explained correctly.

thanks for the help in advance

[Catalin.B]

Normally, you dont need SUM in this formula =(F11-D11)/D11
To remove the error:
Try this: =IF(ISERR((F11-D11)/D11*100)=TRUE;"-";(F11-D11)/D11*100)

[accablues]

thanks for your help. the formulae worked with a slight change, i removed =TRUE because it was giving me some message and also *100 because I already had the cell formated to percentage.
now its changing to "-" many thanks for your help

[accablues]

i just realised on my sheet that with the above formulae its giving me error

S-1
2012 figures = 0 (D10)
2013 figures = 34,000 (F10)

it should show 100% increase in expense but it's giving me "-". the forumale i am using

=IF(ISERROR((F10-D10)/D10),"-",(F10-D10)/D10)

i also tried this formula, but still "-"

=IF(ISERROR((F10-D10)/D10)=TRUE,"-",(F10-D10)/D10)

help please thanks

[Catalin.B]

the formula is corect, be careful to your regional settings marked in red in formula, instead of ; you use ,
=IF(ISERROR((F10-D10)/D10)=TRUE;"-";(F10-D10)/D10)

look at the sample attached

[Kimberly]

=(f10-d10)/f10

[Kimberly]

It won't error, but if you want to suppress errors:

=IFERROR((F10-D10)/F10,"-")

[Catalin.B]

Sorry Kimberly, but this formula does not calculate the increase

=IFERROR((F10-D10)/F10,"-")

I guess you intended =IFERROR((F10-D10)/D10,"-")
But maybe i am wrong , anyway, there are more than one solution to a problem, another could be: IFERROR(F1/D1-1,"-")

[Kimberly]

I couldnt tell what the question was. I was pretty sure this wasn't the best answer

=IF(ISERROR((F10-D10)/D10)=TRUE;"-";(F10-D10)/D10)

He complained that it should equal 100%, even though D10 is zero, so I don't know what he's after.

You don't have to use Iserr or Iserror anymore. You never had to say =TRUE.

[Catalin.B]

Originally Posted by accablues

hi, I am trying to return the forumale value to "-". see below

D11 cell = 0
F11 cell = 58,000

i am calculating year on year increase in expense where D11=2011 EXPENSE and F11=2012 EXPENSE



thanks for the help in advance

well, this is his first post, he did say what he wants, and yes, your solution is the easiest

wish you a wonderful day and hope for a real challenge