Home |
Search |
Today's Posts |
#1
|
|||
|
|||
cell reference from =max
What i am trying to do is find the maximum value from a list of ammounts,
then from that maximum value (which i need in one cell as a value) i need to find who that max figure relates to from a list of names ie. Sheet2 A B 1 fred 1.00 2 gareth 2.00 3 laura 3.00 Sheet3 A B 1 =max('sheet2'!B1:B3) =3.00 this cell needs to display name ie laura god what a nightmare, also from this i need to do the same with =large('sheet2'!B1:B2,2) Please can someone help, thanks |
#2
|
|||
|
|||
=INDEX(A:A,MATCH(MAX(B:B ),B:B,0))
-- HTH RP (remove nothere from the email address if mailing direct) "Garethm1976" wrote in message ... What i am trying to do is find the maximum value from a list of ammounts, then from that maximum value (which i need in one cell as a value) i need to find who that max figure relates to from a list of names ie. Sheet2 A B 1 fred 1.00 2 gareth 2.00 3 laura 3.00 Sheet3 A B 1 =max('sheet2'!B1:B3) =3.00 this cell needs to display name ie laura god what a nightmare, also from this i need to do the same with =large('sheet2'!B1:B2,2) Please can someone help, thanks |
#3
|
|||
|
|||
Try:
=INDEX(Sheet2!A:A,MATCH(MAX(Sheet2!B:B),Sheet2!B:B ,0)) for the max. For the 2nd largest: =INDEX(Sheet2!A:A,MATCH(LARGE(Sheet2!B:B,2),Sheet2 !B:B,0)) HTH Jason Atlanta, GA "Garethm1976" wrote: What i am trying to do is find the maximum value from a list of ammounts, then from that maximum value (which i need in one cell as a value) i need to find who that max figure relates to from a list of names ie. Sheet2 A B 1 fred 1.00 2 gareth 2.00 3 laura 3.00 Sheet3 A B 1 =max('sheet2'!B1:B3) =3.00 this cell needs to display name ie laura god what a nightmare, also from this i need to do the same with =large('sheet2'!B1:B2,2) Please can someone help, thanks |
#4
|
|||
|
|||
=INDEX(Sheet2!A1:A3,MATCH(MAX(Sheet2!B1:B3),Sheet2 !B1:B3,0),1)
-- Kind Regards, Niek Otten Microsoft MVP - Excel "Garethm1976" wrote in message ... What i am trying to do is find the maximum value from a list of ammounts, then from that maximum value (which i need in one cell as a value) i need to find who that max figure relates to from a list of names ie. Sheet2 A B 1 fred 1.00 2 gareth 2.00 3 laura 3.00 Sheet3 A B 1 =max('sheet2'!B1:B3) =3.00 this cell needs to display name ie laura god what a nightmare, also from this i need to do the same with =large('sheet2'!B1:B2,2) Please can someone help, thanks |
#5
|
|||
|
|||
Thanks,
It worked, thank you very very much. Regards Gareth. "Jason Morin" wrote: Try: =INDEX(Sheet2!A:A,MATCH(MAX(Sheet2!B:B),Sheet2!B:B ,0)) for the max. For the 2nd largest: =INDEX(Sheet2!A:A,MATCH(LARGE(Sheet2!B:B,2),Sheet2 !B:B,0)) HTH Jason Atlanta, GA "Garethm1976" wrote: What i am trying to do is find the maximum value from a list of ammounts, then from that maximum value (which i need in one cell as a value) i need to find who that max figure relates to from a list of names ie. Sheet2 A B 1 fred 1.00 2 gareth 2.00 3 laura 3.00 Sheet3 A B 1 =max('sheet2'!B1:B3) =3.00 this cell needs to display name ie laura god what a nightmare, also from this i need to do the same with =large('sheet2'!B1:B2,2) Please can someone help, thanks |
#6
|
|||
|
|||
Also of interest:
http://tinyurl.com/aft5l for it looks like you want to extract a Top N (Top 2) list. Garethm1976 wrote: What i am trying to do is find the maximum value from a list of ammounts, then from that maximum value (which i need in one cell as a value) i need to find who that max figure relates to from a list of names ie. Sheet2 A B 1 fred 1.00 2 gareth 2.00 3 laura 3.00 Sheet3 A B 1 =max('sheet2'!B1:B3) =3.00 this cell needs to display name ie laura god what a nightmare, also from this i need to do the same with =large('sheet2'!B1:B2,2) Please can someone help, thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
up to 7 functions? | Excel Worksheet Functions | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
Formula with text and reference to a date cell | Excel Discussion (Misc queries) | |||
international absulute cell reference | Excel Discussion (Misc queries) | |||
Using the results from two seperate cells to create cell reference | Excel Worksheet Functions |