conditional response function
Posted by Michael Kedor on March 29, 2001 3:34 PM
I need help with writing a formula for a conditional response...I'm a relatively new user, so forgive me if this is basic...cell C2 calculates a percentage for me...cell E15 needs to read that percentage, and based on the range needs to multiply cell E13 by an accelerator/decelerator...for example: If 0% is less than C2 and C2 is less than 50% then E13*0, If 49% is less than C2 and C2 is less than 75% then E13*.5, etc. Any help would be great...
thank you,
mikedor
Posted by Aladin Akyurek on March 29, 2001 4:08 PM
I had same trouble with your previous post as well.
Why is your categorization overlapping?
C2 LT 0% AND C2 LT 50% --> E13*0.0 [1]
C2 LT 49% AND C2 LT 75% --> E13*0.5 [2]
When C2=49, [1] and [2] both apply!
Aladin
Posted by michael kedor on March 29, 2001 5:13 PM
The categorization should not overlap...it should be if c2 LT 50% then e13*0...c2 GT (or equal to) 50% and LT 75%...and so on as it goes up...if you get me started with the formula, I'm sure that I can figure out the rest...my big problem is entering more than one function for an individual cell...
Posted by Aladin Akyurek on March 29, 2001 5:30 PM
Enter in a column
0
50
75
100
.
.
.
and in column next to the previous your multipliers
0
5
.
.
Then select the values that you entered and name the range that they occupy MULTIPLIERS via the Name Box or via the option Insert|Name|Define.
Apply the following formula in E15:
=VLOOKUP(C2,MULTIPLIERS,2)*E13
Aladin
Posted by mikedor on March 29, 2001 6:35 PM
I'm not understanding, Aladin...isn't there some way to do an if/then statement?
Posted by Dave Hawley on March 29, 2001 10:31 PM
Hi Micheal
You can use a conditional IF formula nested up to 7 deep, like below:
=IF(AND(0<C2,C2<50%),E13*0,IF(AND(49%<C2,C2<75%),E13*0.5))
Dave
OzGrid Business Applications
Posted by Dave Hawley on March 29, 2001 10:34 PM
Damn HTML!
Forgot the HTML will stuff it up.
=IF(AND(0 LessThan C2,C2 Less Than 50%),E13*0,IF(AND(49% Less Than C2,C2 Less Than75%),E13*0.5))
Replace "Less Than" with it's sign
Dave
OzGrid Business Applications
Posted by Aladin Akyurek on March 30, 2001 1:20 AM
Mike,
You can use the IF-function of course. But too many IFs, say more than 3, in a formula is hard to understand and to debug when needed. In many situations in which IF is used as a filter VLOOKUP is more convenient.
But if you have just 3 percentages against which the c2-value is going to be tested, then use the following IF-formula:
=IF(C2 LT 50%,E13*x,IF(AND(C2>=50%,C2 LT 75%),E13*y,E13*z))
where x, y, and z are the multipliers.
If you have more than 3 filter values (that is, percentages) and multipliers, I'd opt for the VLOOKUP formula that I proposed.
Aladin
