View Full Version : If Formula combined with max and min problem .

Dpocahontas

2012-01-29, 07:23 PM

Hi All:

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

I have attached the data:

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 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!!

scenography

2012-01-30, 01:03 AM

I don't understand Formula 1. I think Formula 2 works.

Formula 2 Works

Formula 2 calculates the target achievement. Using the second line of your attachment as an example, the actual change was from 10% to 7%, which is -3%. The planned change was from 10% to 8%, which is -2%. The difference between the actual -3% and the planned -2% is an extra -1%.

Compare the extra -1% to the planned -2%. The extra -1% is 50% of the planned -2%. The target achievement is 50%.

In Formula 2, the extra is the difference between Actual 2011 in column L and Planned 2011 in column K. The planned change is the difference between Planned 2011 in column K and Actual 2010 in column J. Divide the extra by the planned change and display the result as a percentage.

On row 6, the target achievement is (L6-K6)/(K6-J6), which is Formula 2.

Add Max and Min

To display a maximum and minimum, expand Formula 2 with an IF() for the maximum and an IF() for the minimum, as show below:

=IF((L6-K6)/(K6-J6)>2, "Better than 200%", IF((L6-K6)/(K6-J6)<-2, "Worse than -200%", (L6-K6)/(K6-J6)))

Dpocahontas

2012-01-30, 01:33 PM

Thank you Scenography..your formula worked great,

and you are right formula 1, doesn't make any sence but at least it gives the same result as no 2. For sure I agree with your logic as that was also mine, the problem is that I don't agree in logic for number 7, as to my logic it should be -6666.7% and not positive as I see it as: it was 7% and the plan was 8% and it grow to 48% which is the non performing loans rate so if you want to rate it how good it did, the rate should be lower than the plan to be excellent, but if is higher it bad..so it is the opposite of other data. Hope I make sense!!:). But i guess i should use this formula as i can't interfere it it as i have to applied to a lot of data in different sheets. So it will be a max of 200% points but it should be -200%, in this case it will have point that it doesn't deserve:). what can you do!!!

your formula of Max and Min worked super :). so thank you a ton, very kind of you. And someone gave me the other option, which the result is the same but only for others if they will be interested is:

=SIGN((Act11-Plan11)/(Plan11-Act10))*MIN(2, ABS((Act11-Plan11)/(Plan11-Act10)))

have a nice day.

scenography

2012-01-30, 03:06 PM

I think you need another column. Two columns can express the plan.

Add another column to express the plan

The number 7% in the Plan 2011 column doesn't express whether it is good or bad to exceed the target. However, the plan can be expressed in two columns as "7%" and "or lower." On another row, the plan can be expressed in two columns as "21%" and "or higher."

Use the new column in a new formula to determine whether the target achievement is positive or negative.

I like the other formula you received, which uses the Sign(), Min(), and Abs() functions.

Dpocahontas

2012-01-30, 10:01 PM

yeah it doesn't say but I know it. two columns will not be permitted by the format. I will check still it tomorrow at work.

Yeah was nice formula, very helpful also yours as long as it works :).

Thank you once again for your response. I really appreciate it. :)

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.