IF Formula with multiple layers

danielle.taylor

Hi community,

I'm looking for help on creating a multi-layered 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

Katy H

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

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, "")))))))


danielle.taylor

@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?

Katy H

@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.