Conversation Re: comparing cell values to determine which is highest, then returning the name assocaited with it in Excel
https://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/901874#M41713
<P><LI-USER uid="422781"></LI-USER> </P>
<P>For such sample</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 462px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/136066i37B80ADCF20E1BB9/image-size/large?v=1.0&px=999" title="image.png" alt="image.png" /></span></P>
<P>for the first case formula could be</P>
<LI-CODE lang="markup">=IF(MAX(A2:B2)<1000, "no winner", INDEX($A$1:$B$1,(B2>A2)+1))</LI-CODE>
<P>for the second one</P>
<LI-CODE lang="markup">=IF( MAX(F2:G2)<MAX(AGGREGATE(14,6,1/($E$2:$E$6=$E2)*$G$2:$G$6,1),AGGREGATE(14,6,1/($E$2:$E$6=$E2)*$F$2:$F$6,1))+(MAX(F2:G2)<1000), "no winner", INDEX(F$1:G$1,(AGGREGATE(14,6,1/($E$2:$E$6=$E2)*$G$2:$G$6,1)>AGGREGATE(14,6,1/($E$2:$E$6=$E2)*$F$2:$F$6,1))+1))</LI-CODE>Wed, 09 Oct 2019 11:28:36 GMTSergei Baklan2019-10-09T11:28:36Zcomparing cell values to determine which is highest, then returning the name assocaited with it
https://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/901681#M41709
<P>Okay, so I've exhausted everything I can find and I'm still nowhere on this. The problem is this:</P><P> </P><P><STRONG>Part 1:</STRONG></P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Capture.PNG" style="width: 200px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/136055i9DE2E6BA98E710A1/image-size/small?v=1.0&px=200" title="Capture.PNG" alt="Capture.PNG" /></span></P><P> </P><P>The formula to look at both A2 and B2 to determine which is highest, then in C2 output the 'header' for the winner. In the example above, "Yellow" for C2, "Blue" for C3 etc. To add an additional twist, the minimum 'winner' would need to be above the number 1000. If below then return something like, "no winner"</P><P> </P><P>I also require the opposite, which I guess would be simple to work out once the above is figured out, whereby it would display to the biggest loser, but this time with a maximum return value of 1000</P><P> </P><P><STRONG>Part 2</STRONG></P><P>Now to add a twist of lemon to the cocktail.</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Capture.PNG" style="width: 200px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/136057i8754030C9818B586/image-size/small?v=1.0&px=200" title="Capture.PNG" alt="Capture.PNG" /></span></P><P>Same as above, however, this time it takes a mean average of all entries in a row with a "1" associated with it to determine which is again the 'winner/loser'. So D2 would simply be the winner between B2 and C2, but D4 would be the winner between the average of B2 and B4, and C2 and C4.</P><P>--</P><P>I have looked high and low, and can't find anything on it. It may be due to not knowing the parlance which describes the issue properly, but the closest I can find is some kind of vlookup or indexing, which doesn't seem to get me there <LI-EMOJI id="lia_disappointed-face" title=":disappointed_face:"></LI-EMOJI></P><P> </P><P>Thank you in advance to anyone that can help; its driving me up the wall</P><P> </P><P> </P><P> </P>Wed, 09 Oct 2019 10:20:17 GMThttps://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/901681#M41709kemble9992019-10-09T10:20:17ZRe: comparing cell values to determine which is highest, then returning the name assocaited with it
https://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/901871#M41712
<P>HI<LI-USER uid="422781"></LI-USER> </P><P> </P><P>Both scenarios of <STRONG>part 1</STRONG> are done and attached in sample file for your reference. Please let me know if it works as desired .</P><P>Please elaborate more the second part of your requirement, for example what criteria you need for D3, D5 or D6. </P><P> </P><P>Thanks</P><P>Tauqeer</P>Wed, 09 Oct 2019 11:26:15 GMThttps://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/901871#M41712tauqeeracma2019-10-09T11:26:15ZRe: comparing cell values to determine which is highest, then returning the name assocaited with it
https://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/901874#M41713
<P><LI-USER uid="422781"></LI-USER> </P>
<P>For such sample</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 462px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/136066i37B80ADCF20E1BB9/image-size/large?v=1.0&px=999" title="image.png" alt="image.png" /></span></P>
<P>for the first case formula could be</P>
<LI-CODE lang="markup">=IF(MAX(A2:B2)<1000, "no winner", INDEX($A$1:$B$1,(B2>A2)+1))</LI-CODE>
<P>for the second one</P>
<LI-CODE lang="markup">=IF( MAX(F2:G2)<MAX(AGGREGATE(14,6,1/($E$2:$E$6=$E2)*$G$2:$G$6,1),AGGREGATE(14,6,1/($E$2:$E$6=$E2)*$F$2:$F$6,1))+(MAX(F2:G2)<1000), "no winner", INDEX(F$1:G$1,(AGGREGATE(14,6,1/($E$2:$E$6=$E2)*$G$2:$G$6,1)>AGGREGATE(14,6,1/($E$2:$E$6=$E2)*$F$2:$F$6,1))+1))</LI-CODE>Wed, 09 Oct 2019 11:28:36 GMThttps://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/901874#M41713Sergei Baklan2019-10-09T11:28:36ZRe: comparing cell values to determine which is highest, then returning the name assocaited with it
https://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/901885#M41714
<P><LI-USER uid="422781"></LI-USER> </P><P> </P><P>Hi</P><P>Please take a look at attached file which hopefully covers both cases (part 1 and part 2). </P><P><A href="https://kteamch-my.sharepoint.com/:x:/g/personal/pascal_kiefer_kteam_ch/ERyix6L1a2BOr74NSmaZxjABlX0mzT_DpgSZEaF2uCcF9A?e=0ufQlc&Download=1" target="_self">File</A></P><P>Let me know if this solved the issue or if I misunderstood your requirement</P>Wed, 09 Oct 2019 13:17:50 GMThttps://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/901885#M41714PascalKTeam2019-10-09T13:17:50ZRe: comparing cell values to determine which is highest, then returning the name assocaited with it
https://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902176#M41727
<P><LI-USER uid="239215"></LI-USER> </P><P> </P><P>Hey, Thanks for taking the time to look at this, for me.</P><P> </P><P>I'm not sure your solution is correct. I changed the blue to a lower value than the yellow, and it returned no winner each time, instead of yellow</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Capture.PNG" style="width: 200px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/136073i05D47CD28EF3E82F/image-size/small?v=1.0&px=200" title="Capture.PNG" alt="Capture.PNG" /></span></P><P>As for part 2, it is essentially the same problem, however it is looking at an aggregate of numbers, rather than just one number to compare. To reword it, instead of 1,2 lets call them Q1, Q2, Q3, Q4 of a year for example. I want to look at what the average 'Blue' number was in say 'Q1' going back in time, and compare that against its 'Yellow' counterpart to determine which is higher/lower.</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Capture.PNG" style="width: 167px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/136075iBC83952376369E18/image-size/small?v=1.0&px=200" title="Capture.PNG" alt="Capture.PNG" /></span></P><P>So in the image above, I've highlighted Q1 for the sake of ease. so in cell D1, it would just be the winner between B2 and C2, which is Blue (and loser in the opposite condition). when we get to cell D6, it would then be the mean average of B2 and B6 vs the mean average of C2 and C6. Then in cell D10, it would be the average of B2, B6 and B10, vs C2, C6, C10 ad nauseam.</P><P> </P><P>Do let me know if that makes more sense, and thanks again for your time on this!</P>Wed, 09 Oct 2019 13:07:10 GMThttps://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902176#M41727kemble9992019-10-09T13:07:10ZRe: comparing cell values to determine which is highest, then returning the name assocaited with it
https://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902207#M41730
<P><LI-USER uid="521"></LI-USER> </P><P> </P><P>Thank you for looking at this problem for me.</P><P> </P><P>In your solution for part 2, there are two instances of no winners, when there is:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Capture.PNG" style="width: 200px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/136077i36D50D17EF2DFF90/image-size/small?v=1.0&px=200" title="Capture.PNG" alt="Capture.PNG" /></span></P><P>H2 would show yellow as the winner as being higher than blue 1183 vs 1001, and the same for H3</P><P> </P><P>Do let me know if I need to expound on my explanation of the problem, and thanks again</P>Wed, 09 Oct 2019 13:14:07 GMThttps://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902207#M41730kemble9992019-10-09T13:14:07ZRe: comparing cell values to determine which is highest, then returning the name assocaited with it
https://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902208#M41731
<P><LI-USER uid="422781"></LI-USER> </P>
<P>In general, better if you attach your sample file to the post together with screenshorts - you'll save time for the people who are answering on generating new test file copying your data from the screen into Excel sheet.</P>Wed, 09 Oct 2019 13:14:48 GMThttps://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902208#M41731Sergei Baklan2019-10-09T13:14:48ZRe: comparing cell values to determine which is highest, then returning the name assocaited with it
https://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902214#M41733
<P><LI-USER uid="422781"></LI-USER> </P>
<P>When I misunderstood your logic. In second case I checked all pairs marked as 1 (or 2, etc.), found among them highest result and for this pair return the winner. All other "1" pairs are not winners.</P>Wed, 09 Oct 2019 13:18:00 GMThttps://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902214#M41733Sergei Baklan2019-10-09T13:18:00ZRe: comparing cell values to determine which is highest, then returning the name assocaited with it
https://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902218#M41734
<P><LI-USER uid="422781"></LI-USER> </P><P> </P><P>Still not sure I understood the issue, does attached file do the job?</P>Wed, 09 Oct 2019 13:19:24 GMThttps://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902218#M41734PascalKTeam2019-10-09T13:19:24ZRe: comparing cell values to determine which is highest, then returning the name assocaited with it
https://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902224#M41735
<P><LI-USER uid="422482"></LI-USER> </P><P> </P><P>Hey, and thanks for helping me on this problem.</P><P> </P><P>I think its almost correct, however, every other cell under E is empty?</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Capture.PNG" style="width: 200px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/136082i7BFF391FFFBF20F8/image-size/small?v=1.0&px=200" title="Capture.PNG" alt="Capture.PNG" /></span></P>Wed, 09 Oct 2019 13:21:11 GMThttps://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902224#M41735kemble9992019-10-09T13:21:11ZRe: comparing cell values to determine which is highest, then returning the name assocaited with it
https://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902244#M41736
Noted, ThanksWed, 09 Oct 2019 13:24:24 GMThttps://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902244#M41736kemble9992019-10-09T13:24:24ZRe: comparing cell values to determine which is highest, then returning the name assocaited with it
https://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902260#M41737
<P><LI-USER uid="422781"></LI-USER> </P><P> </P><P>i thought part 2 was only for rows which are marked with "1". But then it's the same for the rows with "2". Check out the new file and see if it works</P><P> </P>Wed, 09 Oct 2019 13:25:25 GMThttps://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902260#M41737PascalKTeam2019-10-09T13:25:25ZRe: comparing cell values to determine which is highest, then returning the name assocaited with it
https://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902302#M41739
<P>Hi <LI-USER uid="422781"></LI-USER> </P><P> </P><P>As far as the first part is concerned it is showing 'no winner' in all cases because of the below condition that you mentioned in in your first post, </P><P><STRONG><FONT color="#0000FF">'the minimum 'winner' would need to be above the number 1000. If below then return something like, "no winner"</FONT></STRONG></P><P> </P><P>If you simply input 1001, it will show Yellow as winner</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Part_1.JPG" style="width: 280px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/136085iC1DB11E7AE226B7E/image-size/large?v=1.0&px=999" title="Part_1.JPG" alt="Part_1.JPG" /></span></P><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV><P>Thanks</P><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV><P> </P>Wed, 09 Oct 2019 13:28:36 GMThttps://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902302#M41739tauqeeracma2019-10-09T13:28:36ZRe: comparing cell values to determine which is highest, then returning the name assocaited with it
https://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902323#M41741
<P><LI-USER uid="422482"></LI-USER> </P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Capture.PNG" style="width: 200px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/136087i9A5EC0363A86D03D/image-size/small?v=1.0&px=200" title="Capture.PNG" alt="Capture.PNG" /></span></P><P>Couple observations. I changed the first set to below 1000 each, and yet yellow is showing as the winner still in part 2 rather than no winner. Also, how would the formula look to show the opposite, ie the lowest?</P><P> </P><P>In application, I will be looking to put this across maybe 100 different inputs (blue, yellow, etc) and I'm essentially looking for the highest colour from 1000 and the lowest colour from 1000.</P><P> </P><P> </P>Wed, 09 Oct 2019 13:31:00 GMThttps://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902323#M41741kemble9992019-10-09T13:31:00ZRe: comparing cell values to determine which is highest, then returning the name assocaited with it
https://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902328#M41743
Ah, I think there has been a misunderstanding. The winning entry so to speak must be above the 1000 mark to be the winner, but what its comparing against could very well be below 1000Wed, 09 Oct 2019 13:33:49 GMThttps://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902328#M41743kemble9992019-10-09T13:33:49ZRe: comparing cell values to determine which is highest, then returning the name assocaited with it
https://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902380#M41748
<P><LI-USER uid="422781"></LI-USER> </P><P> </P><P>OK the 1000 rules also needs to be in part 2, I didn't know. Find attached the new version.</P><P>If this is anything close to the result you would like to have maybe you can try to do the finetuning of the formulare yourself</P>Wed, 09 Oct 2019 13:52:51 GMThttps://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902380#M41748PascalKTeam2019-10-09T13:52:51ZRe: comparing cell values to determine which is highest, then returning the name assocaited with it
https://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902386#M41749
Hi,<BR /><BR />Its still showing no winner if yellow is below 1000. I've looked at your formula, and to be honest, I don't know where to start to adjust it!Wed, 09 Oct 2019 13:57:47 GMThttps://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/902386#M41749kemble9992019-10-09T13:57:47ZRe: comparing cell values to determine which is highest, then returning the name assocaited with it
https://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/904732#M41798
<P>Hi <LI-USER uid="422781"></LI-USER>, </P><P>To enhance the flavor of your cocktail, I added a red color in the attached file. The formula in D3 is: </P><P><STRONG>=IF(MAX(A3:C3)>D$1,</STRONG><BR /><STRONG>LOOKUP(2,1/(FREQUENCY(0,1/A3:C3)),A$2:C$2),</STRONG><BR /><STRONG>"None")</STRONG></P><P>Conversely, the array formula (entered with Ctrl+Shift+Enter) in J3 is: </P><P><STRONG>=IF(MAX(MMULT(TRANSPOSE(--(F$3:F3=F3)),G$3:I3))>J$1,</STRONG><BR /><STRONG>LOOKUP(2,1/(FREQUENCY(0,1/MMULT(TRANSPOSE(--(F$3:F3=F3)),G$3:I3))),G$2:I$2),</STRONG><BR /><STRONG>"None")</STRONG></P><P>Note that the values in K7:M14 were presented only for verification purposes. Thus, they are not referred to in the foregoing formula. </P><P>Cheers, </P><P><LI-USER uid="280482"></LI-USER> </P>Thu, 10 Oct 2019 07:17:04 GMThttps://techcommunity.microsoft.com/t5/excel/comparing-cell-values-to-determine-which-is-highest-then/m-p/904732#M41798Twifoo2019-10-10T07:17:04Z