IF Statement Help
I am in need of some assistance with creating a formula to identify the cost group based on where the estimated cost falls within the cost range. Thank you!
Best Answers
-
You would need to adjust the data in your Range column. You would either want the low end or the high end (or separate columns for each) so that they can be stored as numerical values instead of text strings. then (assuming you are using the low end of each range) your formula would look like this:
=INDEX({Cost Group}, MATCH(MAX({Low End}, {Low End}, @cell <= [Estimated Cost]@row), {Low End}, 0))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Taking it a bit further..
This would be a very big IF Statement. I would recommend the following with an INDEX/MATCH
I recommend creating a sheet let's call it COSTGROUP that has your upper and lower ranges (only need the lowers but you might need the uppers at some point
Create two References in the main sheet to the COSTGROUP FILE
COSTGROUP_LOW -> The CG_LOW Range in the COSTGROUP FILE
COSTGROUP_GROUP -> The GROUP Range in the COSTGROUP FILE
This formula will return what you need
=INDEX({COSTGROUP_GROUP}, MATCH([Primary Column]@row, {COSTGROUP_LOW}, 1))
Will return what you need and as long as it is always sorted lowest to highest you do not need to evaluate it with any IF. The MATCH will always take the number in the lower range equal to or under
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
-
Yes. I forgot to type in the COLLECT function. Sorry about that.
=INDEX({Cost Group}, MATCH(MAX(COLLECT({Low End}, {Low End}, @cell <= [Estimated Cost]@row)), {Low End}, 0))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
You would need to adjust the data in your Range column. You would either want the low end or the high end (or separate columns for each) so that they can be stored as numerical values instead of text strings. then (assuming you are using the low end of each range) your formula would look like this:
=INDEX({Cost Group}, MATCH(MAX({Low End}, {Low End}, @cell <= [Estimated Cost]@row), {Low End}, 0))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Taking it a bit further..
This would be a very big IF Statement. I would recommend the following with an INDEX/MATCH
I recommend creating a sheet let's call it COSTGROUP that has your upper and lower ranges (only need the lowers but you might need the uppers at some point
Create two References in the main sheet to the COSTGROUP FILE
COSTGROUP_LOW -> The CG_LOW Range in the COSTGROUP FILE
COSTGROUP_GROUP -> The GROUP Range in the COSTGROUP FILE
This formula will return what you need
=INDEX({COSTGROUP_GROUP}, MATCH([Primary Column]@row, {COSTGROUP_LOW}, 1))
Will return what you need and as long as it is always sorted lowest to highest you do not need to evaluate it with any IF. The MATCH will always take the number in the lower range equal to or under
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
-
I can't think of a more efficient approach than Paul's. His approach, however, requires COLLECT() to work. You want to take the MAX() of all of the "Low End" values less than or equal to the "Estimated Cost". COLLECT() grabs all of the low end values,
COLLECT({Low End}, {Low End}, @cell <= [Estimated Cost]@row)
. Next, you take the greatest value or MAX(), MAX(all_low_end_values_LTE_estimated_cost).It is this max value that you're using to locate the "Cost Group", or INDEX(). MATCH() returns the row where the appropriate "Cost Group" can be found, MATCH( MAX( all_low_end_values_LTE_estimated_cost )).
=INDEX({Cost Group}, MATCH( MAX( COLLECT({Low End},{Low End}, @cell <= [Estimated Cost]@row) ),{Low End} , 0))
-
Yes. I forgot to type in the COLLECT function. Sorry about that.
=INDEX({Cost Group}, MATCH(MAX(COLLECT({Low End}, {Low End}, @cell <= [Estimated Cost]@row)), {Low End}, 0))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you all for your quick response and your valued time! Each solution works as expected.
-
Happy to help! 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives