Jan 03 2018
11:00 PM
- last edited on
Jul 25 2018
10:40 AM
by
TechCommunityAP
Jan 03 2018
11:00 PM
- last edited on
Jul 25 2018
10:40 AM
by
TechCommunityAP
I have developed this formula that enables me to match the highest number in a column with name of that product from another column, and add some text.
=INDEX(A81:A104;MATCH(MAX(M81:M104);M81:M104;0))&" is highest performing YTD at "&TEXT(MAX(M81:M104);"0,00%")
In my sheet "Best & Worst", that formula would write "Rare-earth is highest performing YTD at 70,25 %" in B82.
THE PROBLEM is that I get crazy numbers in B73 (Sheet: Best & Worst) when running this formula on merged cell categories (Cat. YTD). You find "Cat. YTD" on sheet "Case-by-Case" column J. You see that 2307,82 % YTD for Energy (in B73) is absurd, since Energy gets 15,51 % YTD in J81 (Sheet: Case-by-Case". However, the formula works in the other instances on sheet "Best & Worst".
I initially thought the problem is that I cant use colon (B81:B104) on merged cells. Hence, I tried to choose the merged category cells individually, and separate with semicolon. Here is the formula I have so far, but it does not work.
=INDEX((B81;B87;B94;B97;B99);MATCH(MAX(J81;J87;J94;J97;J99);(J81;J87;J94;J97;J99);0))&" is highest performing category YTD at "&TEXT(MAX(J81;J87;J94;J97;J99);"0,00%")
Jan 04 2018 02:07 PM
SolutionHi Alexander,
Merged cells is always an issue. I guess here is not with INDEX formula.
Your merged J81.. cell looks like
and if unmerge it it has a lot of side formulas inside
(by the way, here is your current max %). If remove all junk formulas and keep only one
and now merge cells back (it will look as on first screenshot) you have correct result. At least here, perhaps in other places you have more side effects.
Jan 04 2018 02:13 PM
Thanks allot! :D I had no idea there were so many junk formulas in my merged cells. I cleaned up, and it is correct now.
Jan 04 2018 02:07 PM
SolutionHi Alexander,
Merged cells is always an issue. I guess here is not with INDEX formula.
Your merged J81.. cell looks like
and if unmerge it it has a lot of side formulas inside
(by the way, here is your current max %). If remove all junk formulas and keep only one
and now merge cells back (it will look as on first screenshot) you have correct result. At least here, perhaps in other places you have more side effects.