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))
-
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))
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))
-
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))
-
Thank you all for your quick response and your valued time! Each solution works as expected.
-
Happy to help! 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives