# IF Formula with multiple layers

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

Tags:

• ✭✭✭✭✭✭

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 Hall

ILLA Canna

• ✭✭✭✭✭✭

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 Hall

ILLA Canna

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

• ✭✭✭✭✭✭

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.

Katy Hall

ILLA Canna

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!