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
- 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
Check out the Formula Handbook template!