formula help

  • Thread starter Thread starter jocky
  • Start date Start date
J

jocky

My skill level can't crack this one, can some one help?...

Values as follows
b5= sales budget
b6= actual sales

If sales are 3% or more ahead of budget I need the value of 3% o
b6-b5, on the other hand if actual sales are 3% or more under budge
then I need the value of 3% of b6-b5.

I can get individual formulas to calculate the value ie
=IF((B6-B5)/B5>0.03,((B6-B5)*3)/100,0)

or for the negative option
=IF((B6-B5)/B5<-0.03,((B6-B5)*3)/100,0)

I'm trying to string the 2 seperate formulas into one, at present if
know a negative value will be produced I manually ammend the formula
Any ideas?

many thank
 
Hi
try
=IF((B6-B5)/B5>0.03,((B6-B5)*3)/100,IF((B6-B5)/B5<-0.03,((B6-B5)*3)/100
,0))

Frank
 
Back
Top