Formula for Max Value and Row Name

Hello -

I am trying to pull in the max value of column A and the name(s) associated with that value.

I am trying to use the following formula to pull in the max value and taking into consideration there could be multiples of the max value:

=LARGE(DISTINCT([B]$1:[B]$7), #)

And then I would like to show the value(s) in column A that are tied to the maximum value. I was going to try a "Join" and "Collect" formula but didn't get very far.

Any assistance would be greatly appreciated.

Thanks!


Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Justin Tackitt

    Your Join/Collect was on the right track, but you need to do it after finding the MAX/DISTINCT

    I used the Sheet Summary fields (right hand menu on sheet) to house the formulas. You first find the Max Distinct, then you use that Max value as a criteria in a Join Collect.

    I used the entire columns as the ranges, rather than limiting it to 7 rows. You can change the range length if the data set is only 7 rows long. If you want to keep the range dynamic, then use the entire column so the range will grow as the column length grows. The criteria [Max Distinct]# is how a sheet summary field references another Sheet summary field. If you are not using Sheet Summary fields, then you would have the specific cell reference to where your Max/Distinct in that place.


    Will this work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Justin Tackitt

    Your Join/Collect was on the right track, but you need to do it after finding the MAX/DISTINCT

    I used the Sheet Summary fields (right hand menu on sheet) to house the formulas. You first find the Max Distinct, then you use that Max value as a criteria in a Join Collect.

    I used the entire columns as the ranges, rather than limiting it to 7 rows. You can change the range length if the data set is only 7 rows long. If you want to keep the range dynamic, then use the entire column so the range will grow as the column length grows. The criteria [Max Distinct]# is how a sheet summary field references another Sheet summary field. If you are not using Sheet Summary fields, then you would have the specific cell reference to where your Max/Distinct in that place.


    Will this work for you?

    Kelly

  • Justin Tackitt
    Justin Tackitt ✭✭✭✭

    This is perfect!!! Thank you very much for the help!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!