Return the Column name and value for MAX in a row

Jeana ✭✭✭✭✭✭

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!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!