Return the Column name and value for MAX in a row

Jeana
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!

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Jeana
    Jeana ✭✭✭✭✭✭
    edited 01/21/22

    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!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Jeana
    Jeana ✭✭✭✭✭✭
    edited 01/21/22

    Could be either, the same sheet might be simpliest.

    Thanks!

  • Jeana
    Jeana ✭✭✭✭✭✭

    @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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!