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!
Best 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
-
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
-
This is perfect!!! Thank you very much for the help!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 283 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!