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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!