MAX + COLLECT Formula Help

JSabillon

Hello!

I am trying to create a formula that will find the largest % from the % cells in each row and then label it correctly. For example, if the largest % is Novice in the first row, I want the blank box column to note that the Overall Expertise Level is Novice. Please advise how to set up this formula. I think it is a combination of max and collect, but I am not sure how to format it.

Thank you.

JSabillon

I actually think this is a MATCH/MAX formula

Paul Newcome

What if there is more than one that have the same percentage?

JSabillon

Paul, I am not sure. That's a great question.

Paul Newcome

Either way, you are going to need a series of IF statements. The order and exact syntax depends on how to handle duplicates though.

If there will never be a duplicate in the same row, then it is a basic nested IF in any order.

If there are duplicates:

Displaying all would be "adding" IF statements together.

Displaying the lowest (novice) would be a nested IF moving from left to right.

Displaying the highest (expert) would be a nested IF moving from right to left.

JSabillon

Could you help me with a formula? I have this formula already to find the max number, but now I need it to tell me where that number came from. Will I need a helper row since it won't read the column header?

=MAX(Novice@row:Expert@row)

That formula reads the 4 cells on that row (Novice, Intermediate, Skilled, Expert) and tells me the highest number. But now, I want it to tell me which column that came from and give me the name instead of the number itself.

Thank you!

Paul Newcome

You would need either a helper row or a series of IF statements. Whether you use a helper row or not, we would still need to know how to deal with the possibility of two of them being the same on a single row (if that is even a possibility).

I would be happy to help you with a formula, but we are looking at a possibility of at least 8 different formulas depending on helper row or not and duplicates or not. I just need to know which one to help with.

JSabillon

Let's go with there will be no duplicates.

I can create a helper row if that will be more helpful, but currently it is not set up that way.

Paul Newcome

IN that case you would use a series of nested IFs to compare each cell in turn to the MAX.

=IF(Novice@row = MAX(Novice@row:Expert@row), "Novice", IF(Intermediate@row = MAX(Novice@row:Expert@row), "Intermediate", IF(Skilled@row = MAX(Novice@row:Expert@row), "Skilled", "Expert")))