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!

Paul Newcome

Are you able to provide a screenshot for reference?

Jeana

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

Is this going to be on the same sheet or a different sheet?

Jeana

Could be either, the same sheet might be simpliest.

Thanks!

Jeana

@Paul Newcome Hi Paul, I know you must be busy, just wondering if you have had any insight into this?

Thanks,

Jeana