How To Identify Max Value

Kamil Ismail

Hello,

Need help, I want to identify which questions have the highest "No" respond. On column "MAX - NO" will appear the name of the questions that have the highest No respond. Currently, I can identify the maximum of respond on each questions.

I don't know which suitable formula to combined with MAX to capture which question have highest No.

Genevieve P.

Hi Kamil,

There are potentially a few different ways to do this. We could build an IF statement to return the text of the question question IF the Max value in your column is the same as the NO value in the other column, but what will happen if there are more than one questions with the same number? That might end up being a lot of text in one cell.

I have an idea for a slightly different process, but only if this would make sense for your tracking. What if you set up a Flag column on the left, and built a statement that said if the Max Number is the same as this row, raise the flag.

This would make it very clear if that question has the highest "No"s, or if there are a few questions tied for the highest "No". You could also use Conditional Formatting rules to change the colour of the cell with the question if the flag is raised, to make it stand out more. I'm thinking this might be a bit cleaner to look at (so you're not duplicating text). This will also help show if there are more than one that meet the same criteria... what do you think?

Here's an example of the possible end result:

Here's the formula I used for the flag:

=IF(NO@row = MAX(NO$2:NO$5), 1, 0)

I added in the absolute references (the dollar signs before the numbers in the range) so that I could drag-fill it and the row references wouldn't change.

Let me know if you have any questions or if this would work for you!

Cheers,

Genevieve

Genevieve P.

Hi Kamil,

There are potentially a few different ways to do this. We could build an IF statement to return the text of the question question IF the Max value in your column is the same as the NO value in the other column, but what will happen if there are more than one questions with the same number? That might end up being a lot of text in one cell.

I have an idea for a slightly different process, but only if this would make sense for your tracking. What if you set up a Flag column on the left, and built a statement that said if the Max Number is the same as this row, raise the flag.

This would make it very clear if that question has the highest "No"s, or if there are a few questions tied for the highest "No". You could also use Conditional Formatting rules to change the colour of the cell with the question if the flag is raised, to make it stand out more. I'm thinking this might be a bit cleaner to look at (so you're not duplicating text). This will also help show if there are more than one that meet the same criteria... what do you think?

Here's an example of the possible end result:

Here's the formula I used for the flag:

=IF(NO@row = MAX(NO$2:NO$5), 1, 0)

I added in the absolute references (the dollar signs before the numbers in the range) so that I could drag-fill it and the row references wouldn't change.

Let me know if you have any questions or if this would work for you!

Cheers,

Genevieve

Kamil Ismail

Hi Genevieve,

I have follow your method and it works brilliantly. Thank you Genevieve :)

Below I provide snapshot how it looks like in a dashboard.

Flag column added with a formula to check which questions is the highest in each section

I compile it in report which is flagged

And then put it in dashboard using the report page

Genevieve P.

Hi Kamil,

Wow, that is wonderful!!! I'm so glad this worked for you; the Report and Chart widget look great!