SOLVED
Home

Formula that works on individual cells return absurd value when run on merged cells

%3CLINGO-SUB%20id%3D%22lingo-sub-141518%22%20slang%3D%22en-US%22%3EFormula%20that%20works%20on%20individual%20cells%20return%20absurd%20value%20when%20run%20on%20merged%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-141518%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22%22%3EI%20have%20developed%20this%20formula%20that%20enables%20me%20to%20match%20the%20highest%20number%20in%20a%20column%20with%20name%20of%20that%20product%20from%20another%20column%2C%20and%20add%20some%20text.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX(A81%3AA104%3BMATCH(MAX(M81%3AM104)%3BM81%3AM104%3B0))%26amp%3B%22%20i%3CSPAN%3Es%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ehighest%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eperforming%20YTD%20at%26nbsp%3B%3C%2FSPAN%3E%22%26amp%3BTEXT(MAX(M81%3AM104)%3B%220%2C00%25%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20my%20sheet%20%22Best%20%26amp%3B%20Worst%22%2C%20that%20formula%20would%20write%20%22Rare-earth%20is%20highest%20performing%20YTD%20at%2070%2C25%20%25%22%20in%20B82.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ETHE%20PROBLEM%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CSPAN%3Eis%20that%20I%20get%20crazy%20numbers%20in%20B73%20(Sheet%3A%20Best%20%26amp%3B%20Worst)%20when%20running%20this%20formula%20on%20merged%20cell%20categories%20(Cat.%20YTD).%20You%20find%20%22Cat.%20YTD%22%20on%20sheet%20%22Case-by-Case%22%20column%20J.%20You%20see%20that%202307%2C82%20%25%20YTD%20for%20Energy%20(in%20B73)%20is%20absurd%2C%20since%20Energy%20gets%2015%2C51%20%25%20YTD%20in%20J81%20(Sheet%3A%20Case-by-Case%22.%20However%2C%20the%20formula%20works%20in%20the%20other%20instances%20on%20sheet%20%22Best%20%26amp%3B%20Worst%22.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20initially%20thought%20the%20problem%20is%20that%26nbsp%3BI%20cant%20use%26nbsp%3Bcolon%20(B81%3AB104)%20on%26nbsp%3Bmerged%20cells.%20Hence%2C%20I%20tried%20to%20choose%20the%20merged%20category%20cells%20individually%2C%20and%20separate%20with%20semicolon.%20Here%20is%20the%20formula%20I%20have%20so%20far%2C%20but%20it%20does%20not%20work.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX((B81%3BB87%3BB94%3BB97%3BB99)%3BMATCH(MAX(J81%3BJ87%3BJ94%3BJ97%3BJ99)%3B(J81%3BJ87%3BJ94%3BJ97%3BJ99)%3B0))%26amp%3B%22%20is%20highest%20performing%20category%20YTD%20at%20%22%26amp%3BTEXT(MAX(J81%3BJ87%3BJ94%3BJ97%3BJ99)%3B%220%2C00%25%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-141518%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-141884%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20that%20works%20on%20individual%20cells%20return%20absurd%20value%20when%20run%20on%20merged%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-141884%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20allot!%20%3ADI%20had%20no%20idea%20there%20were%20so%20many%20junk%20formulas%20in%20my%20merged%20cells.%20I%20cleaned%20up%2C%20and%20it%20is%20correct%20now.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-141876%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20that%20works%20on%20individual%20cells%20return%20absurd%20value%20when%20run%20on%20merged%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-141876%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Alexander%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMerged%20cells%20is%20always%20an%20issue.%20I%20guess%20here%20is%20not%20with%20INDEX%20formula.%3C%2FP%3E%0A%3CP%3EYour%20merged%20J81..%20cell%20looks%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20100px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F26395i62A3AFC744E3A8C3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22MergedCell.JPG%22%20title%3D%22MergedCell.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20if%20unmerge%20it%20it%20has%20a%20lot%20of%20side%20formulas%20inside%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%2092px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F26396i6F39A33CE467E274%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22UmergedCell.JPG%22%20title%3D%22UmergedCell.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E(by%20the%20way%2C%20here%20is%20your%20current%20max%20%25).%26nbsp%3B%20If%20remove%20all%20junk%20formulas%20and%20keep%20only%20one%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%2096px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F26397iA715D72EF6252421%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22UmergedCellOneFormula.JPG%22%20title%3D%22UmergedCellOneFormula.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20now%20merge%20cells%20back%20(it%20will%20look%20as%20on%20first%20screenshot)%20you%20have%20correct%20result.%20At%20least%20here%2C%20perhaps%20in%20other%20places%20you%20have%20more%20side%20effects.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Alexander Bjerkvik
New Contributor

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))&" ihighest 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%")

2 Replies
Solution

Hi Alexander,

 

Merged cells is always an issue. I guess here is not with INDEX formula.

Your merged J81.. cell looks like

MergedCell.JPG

and if unmerge it it has a lot of side formulas inside

UmergedCell.JPG

(by the way, here is your current max %).  If remove all junk formulas and keep only one

UmergedCellOneFormula.JPG

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.

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. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies