Find largest result after converting to percentage

[Shamusvw]

I am familiar with the LARGE function of finding the largest, 2nd to largest etc. value of a range.
I have the situation where I have a range of marks, and then below it another range of what the mark is out of.

E.g I have the following in A1 to E1: 1,2,3,4,5
Then in A2 to E2 I have 3,4,4,4,10

These are marks from 5 tests written, so first test was 1/3, 2nd test 2/4, 3rd = 3/4, 4th 4/4, and 5th 5/10

I want to retrieve the value in the range A1:E1 and the corresponding value in A2:E2 that gives me the biggest percentage.
So in the example above I want F1 to store 4 (=D1) & F2 to also store 4 (=D2) since 4/4 gives 100% which is the highest mark attained.

What I have accomplished so far is to calculate the highest percentage, but I don't want the percentage, I want the 2 cells representing the numerator & denominator of the mark.

This is the formula I have for calculating the highest percentage, using ctrl-shift-enter : {=LARGE(A1:E1/A2:E2,1)} which gives the result 1 (=100%)
How do I use this result to now give me the correct cells D1 & D4?
Thank you

 

[Eric W]

Maybe:

	A	B	C	D	E	F

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]F1[/TH]
[TD="align: left"]{=LOOKUP(MAX($A$1:$E$1/$A$2:$E$2),$A$1:$E$1/$A$2:$E$2,A1:E1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Copy the formula to F2.

 

[Shamusvw]

Thanks Eric for the reply.
My apologies, I should have mentioned that the reason I am using LARGE function is that I don't just want to see the single highest result, I need to also get the 2nd highest result, i.e. I need the top 2 highest results, so this won't work for the 2nd one. I was just going to put a 2 into the LARGE formula for the 2nd highest when I got it working.
To explain, the kids will do maybe 5 tests, and then the marks for the top 2 tests get used for reporting on.

 

[Shamusvw]

Erik, I used your formula and changed it to use LARGE instead, and it works perfectly, thank you very much.
This is what I ended up with:

{=LOOKUP(LARGE($A$1:$E$1/$A$2:$E$2;1);$A$1:$E$1/$A$2:$E$2;A1:E1)}

and again using the ctrl+shift+enter combination to accept the formula after editing it.

 

[Shamusvw]

This solution is almost, but not quite working.
The problem is that the range that it (LOOKUP function) searches needs to be in order of magnitude, not randomly placed, else it can give the incorrect result (which it is!)
To put the marks in order isn't feasible, because the are placed in order of being written.
Can anyone please help me further?

 

[Eric W]

Could you please provide an example where it does not work to your requirements?


I devised some formulas to return the values in decreasing order of ratios, and allows duplicates:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
Dummy values	Highest ratio	Next highest ratio	Next highest ratio	Next highest ratio	Next highest ratio	Next highest ratio

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]{=INDEX($A2:$F2,SMALL(IF($A$2:$F$2/$A$3:$F$3*LCM($A$3:$F$3)=LARGE($A$2:$F$2/$A$3:$F$3*LCM($A$3:$F$3),COLUMNS($I2:I2)),COLUMN($A$2:$F$2)),SUM(IF($H$2:H$2/$H$3:H$3*LCM($A$3:$F$3)=LARGE($A$2:$F$2/$A$3:$F$3*LCM($A$3:$F$3),COLUMNS($I2:I2)),1))+1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Copy I2 to I3, then drag both rows to the right.

The values in H2:H3 are required to make the formulas work, and must be some ratio that won't occur in your actual list. The entire list must be used. In case of ties (2/4, 5/10, 1/2), the formula looks at preceding values to see which ones should not be used again. You can't just put in 4 into the LARGE function and get 5/10. Also, the values in A2:F3 must be integers. I used LCM in the formulas to avoid rounding errors, but non-integers in the source data will re-introduce them.

But these formulas notwithstanding (although they were interesting to derive), I'm still not sure what you want, so an example would help.

 

[Shamusvw]

Hi Eric

If I have the following setup, then it gets it wrong
The first 5 columns are the marks, e.g. 1/2, 3/4... and then right beneath the marks I have added the mark as a percentage in order to check the results.
In the 6th column it must pull the highest mark, in the 7th the 2nd to highest.
If you look at the percentages, 3/4 (column 2) is the highest mark, but doesn't get pulled through to col. 6, but it does pull the correct 2nd highest mark through correctly to column 7.

These are the formulae in col6, row 1 & 2: = highest mark
{=LOOKUP(LARGE($A$1:$E$1/$A$2:$E$2;1);$A$1:$E$1/$A$2:$E$2;A1:E1)}
{=LOOKUP(LARGE($A$1:$E$1/$A$2:$E$2;1);$A$1:$E$1/$A$2:$E$2;A2:E2)}

These are the formulae in col7, row 1 & 2: = 2nd highest mark
{=LOOKUP(LARGE($A$1:$E$1/$A$2:$E$2;2);$A$1:$E$1/$A$2:$E$2;A1:E1)}
{=LOOKUP(LARGE($A$1:$E$1/$A$2:$E$2;2);$A$1:$E$1/$A$2:$E$2;A2:E2)}
[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]0.50[/TD]
[TD]0.75[/TD]
[TD]0.67[/TD]
[TD]0.6[/TD]
[TD]0.63[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Sorry, I forgot that LOOKUP requires the values to be in order, which will not necessarily be the case for your data. If you don't want to worry about duplicates, this formula should work:

Excel 2012

	A	B	C	D	E	F	G

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F1[/TH]
[TD="align: left"]{=INDEX($A1:$E1,SMALL(IF(LARGE($A$1:$E$1/$A$2:$E$2,COLUMNS($F1:F1))=$A$1:$E$1/$A$2:$E$2,COLUMN($A$1:$E$1)-COLUMN($A$1)+1),1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



If you do care about duplicates, use the longer formula from post # 6.

 

[Shamusvw]

Thank you Eric, I appreciate your help.