Better way to write this nested if statiement?

I need to replicate this formula a few different times, and I am wondering if there is a better way?


=IF([Team-MRR]@row >= [Team-125%]@row, "Top commission reached", IF([Team-MRR]@row >= [Team-110%]@row, [Team-125%]@row - [Team-MRR]@row, IF([Team-MRR]@row >= [Team-75%]@row, [Team-110%]@row - [Team-MRR]@row, IF([Team-MRR]@row >= [Team-50%]@row, [Team-75%]@row - [Team-MRR]@row, IF([Team-MRR]@row <= [Team-50%]@row, [Team-50%]@row - [Team-MRR]@row)))))

Any help would be GREATLY appreciated!

Tags:

Best Answer

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓

    Hi, @Mtmoroni

    Another approach is to use MIN( COLLECT( ) ). COLLECT() uses a range of cells that you can define by dragging or with a few keystrokes.

    When the team's Team-MRR value has not surpassed the value in Team-125%, use MIN(COLLECT()) to find the next tier you need for your calculation. I.e., MIN( COLLECT( range , criterion_range , criterion ) ).

    The range you're collecting is [Team-50%]@row:[Team-125%]@row .

    Your criterion range is [Team-50%]@row:[Team-125%]@row and the criterion is >=[Team-MRR]@row . The COLLECT() expression would be, COLLECT([Team-50%]@row:[Team-125%]@row, [Team-50%]@row:[Team-125%]@row, >=[Team-MRR]@row).

    MIN( Collected_Values ) will return the value of this tier. The complete expression is,

    MIN(COLLECT([Team-50%]@row:[Team-125%]@row, [Team-50%]@row:[Team-125%]@row, >=[Team-MRR]@row))
    

    Your revised formula would be:

    IF( [Team-MRR]@row >= [Team-125%]@row 
        , "Top commission reached." 
        , MIN(COLLECT([Team-50%]@row:[Team-125%]@row, [Team-50%]@row:[Team-125%]@row, >=[Team-MRR]@row)) - [Team-MRR]@row 
       )
    

    Cheers!


Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓

    Hi, @Mtmoroni

    Another approach is to use MIN( COLLECT( ) ). COLLECT() uses a range of cells that you can define by dragging or with a few keystrokes.

    When the team's Team-MRR value has not surpassed the value in Team-125%, use MIN(COLLECT()) to find the next tier you need for your calculation. I.e., MIN( COLLECT( range , criterion_range , criterion ) ).

    The range you're collecting is [Team-50%]@row:[Team-125%]@row .

    Your criterion range is [Team-50%]@row:[Team-125%]@row and the criterion is >=[Team-MRR]@row . The COLLECT() expression would be, COLLECT([Team-50%]@row:[Team-125%]@row, [Team-50%]@row:[Team-125%]@row, >=[Team-MRR]@row).

    MIN( Collected_Values ) will return the value of this tier. The complete expression is,

    MIN(COLLECT([Team-50%]@row:[Team-125%]@row, [Team-50%]@row:[Team-125%]@row, >=[Team-MRR]@row))
    

    Your revised formula would be:

    IF( [Team-MRR]@row >= [Team-125%]@row 
        , "Top commission reached." 
        , MIN(COLLECT([Team-50%]@row:[Team-125%]@row, [Team-50%]@row:[Team-125%]@row, >=[Team-MRR]@row)) - [Team-MRR]@row 
       )
    

    Cheers!


  • Mtmoroni
    Mtmoroni ✭✭✭✭✭

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!