Better way to write this nested if statiement?

Mtmoroni

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!

Toufong Vang

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!

Toufong Vang

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

Thank you!