Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
=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! |
#4
|
|||
|
|||
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! |
#5
|
|||
|
|||
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! |
#6
|
|||
|
|||
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! |
#7
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to compare multiple rows values based on another column? | Excel Worksheet Functions | |||
How do I create a formula that would allow me to subtract from a d | New Users to Excel | |||
Formula to return cell contents based on multiple conditions | Excel Worksheet Functions | |||
Count If Formula for multiple conditions?? How To?? | Excel Worksheet Functions | |||
How do I create a formula in Excel that will countif or sumif bef. | Excel Worksheet Functions |