MAX + COLLECT Formula Help
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.
Answers

I actually think this is a MATCH/MAX formula

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

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

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.

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!

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.

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.

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")))
Help Article Resources
Categories
Check out the Formula Handbook template!