IF Statement Help

Ronald Anderson

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!

Paul Newcome

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

Brent Wilson

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

Paul Newcome

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

Paul Newcome

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

Brent Wilson

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

TVang

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

Paul Newcome

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

Ronald Anderson

Thank you all for your quick response and your valued time! Each solution works as expected.

Paul Newcome

Happy to help! 👍️