Hi All:

I am getting too confused about a formula I have and I really need some 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)
whe 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 the formula should give the opposite, I mean if Actual Dec 2011 is bigger than plan or Actual 2010, it is not good. It means that is below target achievement as it is about. so From 8% it has gone to 48% and the target was 7%.

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

It is really important to know this one for me!!
__________________
---------------------------
Excel 2007, "Always try as you will find the soluation"

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

Diagram is

Hi:)

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

thank you a ton.
regards