How to create a formula with multiple contraints and answers
 

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!

One way:

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

Regards

Trevor


"torky1" wrote in message
...
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!

=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

"torky1" wrote:

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!

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.

On Fri, 4 Mar 2005 15:35:01 -0800, torky1
wrote:

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!

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

"tjtjjtjt" wrote:

=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

"torky1" wrote:

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!

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" wrote:

One way:

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

Regards

Trevor


"torky1" wrote in message
...
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!

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

Regards


"torky1" wrote in message
...
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" wrote:

One way:

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

Regards

Trevor


"torky1" wrote in message
...
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!