IF Statement Help

Ronald Anderson
Ronald Anderson ✭✭✭✭
edited 07/07/22 in Smartsheet Basics

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    edited 07/07/22 Answer ✓

    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    edited 07/07/22 Answer ✓

    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

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    edited 07/07/22

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Ronald Anderson
    Ronald Anderson ✭✭✭✭

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com