IF Formula with multiple layers
Hi community,
I'm looking for help on creating a multilayered IF formula for the following guidelines. Anyone an expert that can help me build it out?
IF amount is > $100K Then this should be 4
IF amount is <$100K  $125K Then this should be 6
IF amount is <$125K  $150K Then this should be 8
IF amount is <$150K  $175K Then this should be10
IF amount is <$175K  $200K Then this should be 12
IF amount is <$200K  $225K Then this should be 14
IF amount is <$225 + Then this should be 16
Best Answer

Let's pretend the column with the financial amounts is: "Employee Bonus"
This is the formula:
=IF([Employee Bonus]@row>225000, 16, IF(AND([Employee Bonus]@row>=200000, [Employee Bonus]@row<=225000), 14, IF(AND([Employee Bonus]@row>=175000, [Employee Bonus]@row<=199999), 12, IF(AND([Employee Bonus]@row>=150000, [Employee Bonus]@row<=174999), 10, IF(AND([Employee Bonus]@row>=125000, [Employee Bonus]@row<=149999), 8, IF(AND([Employee Bonus]@row>=100000, [Employee Bonus]@row<=124999), 6, IF([Employee Bonus]@row<100000, 4, "")))))))
Answers

Let's pretend the column with the financial amounts is: "Employee Bonus"
This is the formula:
=IF([Employee Bonus]@row>225000, 16, IF(AND([Employee Bonus]@row>=200000, [Employee Bonus]@row<=225000), 14, IF(AND([Employee Bonus]@row>=175000, [Employee Bonus]@row<=199999), 12, IF(AND([Employee Bonus]@row>=150000, [Employee Bonus]@row<=174999), 10, IF(AND([Employee Bonus]@row>=125000, [Employee Bonus]@row<=149999), 8, IF(AND([Employee Bonus]@row>=100000, [Employee Bonus]@row<=124999), 6, IF([Employee Bonus]@row<100000, 4, "")))))))

@Katy H Thank you so much!! SO helpful!
I’m also looking for a formula to create a ceiling/maximum on a value. For example, X = (Y*2) up to 30. Would you happen to know a formula for this scenario?

@danielle.taylor apologies for the radio silence. I missed this notification!
Can you provide a bit more information? I'm not sure I understand what you are after. If you can explain it in the context of the work that might help.
Help Article Resources
Categories
Check out the Formula Handbook template!