Formula Problem with IF (Negative and Positive Numbers)

[Dpocahontas]

Hi All:

I am getting too confused about a formula I have and I really need som
help.

I have the data attached.

Formula 1
=IF(L6-K6<0,(L6-K6)/(K6-J6),IF(L6-J6<1,(L6-K6)/(K6-J6),(L6-J6)/(K6-J6)))
Formula 2 : =(L6-K6)/(K6-J6)
where: L6 is Actual December 2011
K6 is Plan 2011
J6 is Actual 2010

Both give the same result, but I don't agree with the No. 7 as th
formula should give the opposite, I mean if Actual Dec 2011 is bigge
than plan or Actual 2010, it is not good. It means that is below targe
achievement as it is about. so From 8% it has gone to 48% and the targe
was 7%.

Did I do something wrong? Also, I would like to combine this formul
with a MIN and Max, I mean - let say the value 6666.7% even it if i
positive or negative shouldn't be more than 200% or -200%! How can I d
that??

It is really important to know this one for me!!
__________________

 

[pingtom]

You can mapping your problem into diagram below:

your formula is
=IF(L6-K6<0,(L6-K6)/(K6-J6),IF(L6-J6<1,(L6-K6)/(K6-J6),(L6-J6)/(K6-J6)))
where
L6 is Actual December 2011
K6 is Plan 2011
J6 is Actual 2010

diagram is
'[image: http://www.freeimagehosting.net/t/eshoq.jpg]
(http://www.freeimagehosting.net/eshoq

 

[Dpocahontas]

Hi

Your diagram is really interesting...a very good way to understand th
formula . But i think the one with min and abs is the one that
wanted for my result...but at least i did understand better it with th
diagram . It reminds me a subject we did in management and is calle
the Tree ...so you can analyse the options.

thank you a ton.
regard