Formula Help

[bankerlady]

I need help with a formula today:

I have four columns, A through D, headings Threshold, Target, Stretch and Actual.

I have a goal in the threshold, target and stretch columns and an actual production result in the actual. Let's assume these numbers are on row 3.

In column E I want it to display a value as follows:

If D3=>A3,10 **this part of the formula works great, it's the additional qualifiers that I can't get right, probably a punctuation or parenthesis error on my part.

Or if D3=>B3,40 or if D3=>C3,75, otherwise 0.

Any help on how to structure this formula ??

Thanks so much, this board is great!!!

 

[ADAMC]

Try..

Try:

=IF(D3=>A3,"10",IF(D3>B3,"40",IF(D3>C3,"75","0")))

 

[bankerlady]

I copied this formula, and my number in the actual should have returned a value of 75 but it returned a value of 10 which tells me it is reading the first part of this formula and not the statements that come after. Other suggestions ??

 

[Seti]

You have to check the other way like this:

=IF(D3>=C3,75,IF(D3>=B3,40,IF(D3>=A3,10,0)))

The other formula checks to see if you have exceeded target and if so, never checks to see if you are over threshold or stretch, so you need to start at the high end. Alternatively, you could use < signs like this:

=IF(D3<A3,0,IF(D3<B3,10,IF(D3<C3,40,75)))

You could also use a lookup type formula, but I'll leave that to others to play with.