IF Formula with multiple layers

Options

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:

Best Answer

  • Katy H
    Katy H ✭✭✭✭✭✭
    Answer ✓
    Options

    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

    Head of Product Management

    ILLA Canna

    LinkedIn

Answers

  • Katy H
    Katy H ✭✭✭✭✭✭
    Answer ✓
    Options

    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

    Head of Product Management

    ILLA Canna

    LinkedIn

  • danielle.taylor
    Options

    @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
    Katy H ✭✭✭✭✭✭
    Options

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

    Katy Hall

    Head of Product Management

    ILLA Canna

    LinkedIn

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!