Better way to write this nested if statiement?

Options
✭✭✭✭✭

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:

• ✭✭✭✭✭
Options

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))
```

```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!

• ✭✭✭✭✭
Options

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))
```

```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!

• ✭✭✭✭✭
Options

Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!