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!
Best 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
-
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!
-
Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!