Return the Column name and value for MAX in a row
Hi, I've been looking through the Community questions and can't quite find what I'm looking for here. I have a row of survey rating results and related comments. Each row has the Dept name and ratings for each section. The information is gathered using a form. I have a formula that calculates the Max value in the rating cells for each row.
=MAX(COLLECT([Column 1]@row:[Column 10]@row, [Column 1]@row:[Column 10]@row, ISNUMBER(@cell)))
I need to generate a report that shows the Max value by department. In order to do this I'm trying to create a formula that will return the Dept name and the MAX value for each row. Then I can report on them and/or sum them.
I tried using the MAX function in a report but it doesn't distinguish between the departments like I need it to. So I'm looking for help with a formula that will return for each row the MAX value and the Column name for the value as well as the Department column. I'm thinking something similar to the formula above but I can't seem to figure it out.
Thanks in advance!
Answers
-
Are you able to provide a screenshot for reference?
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!
-
Sure thing!
I capture Dept and Vendor info as well as ratings for several categories. For each row (rating) I have a formula that captures the MAX rating for that row. I need to see by Department and Vendor, the highest rating for each category. Example or desired output:
Where the number in this pic is the MAX rating for all rows by Dept and by Category.
In lay(wo)mans terms I need to find all rows for a particular department, find the MAX value for the Ratings in that department (have that formula) in each category. The Categories being the column headings labeled Partner and Contractor Access, Analytics and Reports, etc. There are more columns, this is just an example.
Thanks!
-
Is this going to be on the same sheet or a different sheet?
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!
-
Could be either, the same sheet might be simpliest.
Thanks!
-
@Paul Newcome Hi Paul, I know you must be busy, just wondering if you have had any insight into this?
Thanks,
Jeana
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!