How to create a formula with multiple contraints and answers

[Guest]

Please help!
I need a formula that basically says this:
If cell C7 is less than 80%, then C8 equals 0%
If cell C7 is greater than or equal to 80%, but less than 90%, then C8
equals 75%
If cell C7 is greater than or equal to 90%, but less than 100%, then C8
equals 85%
If cell C7 is greater than or equal to 100%, but less than 125%, then C8
equals 100%
If cell C7 is greater than or equal to 125%, then C8 equals 140%

Any help would be appreciated!

 

[Trevor Shuttleworth]

One way:

=IF(C7>=125%,140%,IF(C7>=100%,100%,IF(C7>=90%,85%,IF(C7>=80%,75%,0))))

Regards

Trevor

 

[Guest]

=vlookup(c7,{.00,.0;.80,.75;.90,.85;1.0,100;1.25,1.40},2)
You may want to check out:
http://www.contextures.com/xlFunctions02.html

Format the cell to display as Percent--do not type percent values into the
formula.

You can replace the values in curly brackets with cell references. I'm using
a USA setup, so commas represent the next cell in a row. Semicolons mean
start a new row. You'll see examples on the website I referenced.

tj

 

[Myrna Larson]

This formula eliminates the need to create a table:

=VLOOKUP(A1,{0,0;0.8,0.75;0.9,0.85;1,1;1.25,1.4},2)

But if you have a lot of these formulas, it would save time in the end to
create a table to use with VLOOKUP.

 

[Guest]

Goodness, I'm full of typos tonight. in the 100 should be 1.0.
See Myrna's post for the same thing with no typo.

tj

 

[Guest]

That's exactly what I was looking for! Thank you! I was headed in the right
direction, but was haviing problems with the placement of the parentheses.

 

[Trevor Shuttleworth]

You're very welcome. Thanks for the feedback; it's nice to know it works
for you.

Regards