To select the second largest, use the following command:
=Index(A2:A9,Match(Large(B2:B9,2),B2:B9,0)
To select the third largest, replace the "2" in the Large command by a "3", etc...
To select the second largest, use the following command:
=Index(A2:A9,Match(Large(B2:B9,2),B2:B9,0)
To select the third largest, replace the "2" in the Large command by a "3", etc...
Any ideas on how to pick up ties for the first place with up to 5 participants (if say all 5 scored the highest)?
C D E F G
2 max 8
3 cnt 5
4 cnt ent scrs ties
5 - a 7 1 b
6 1 b 8 2 c
7 2 c 8 3 g
8 - d 7 4 i
9 - e 7 5 j
10 - f 7
11 3 g 8
12 - h 7
13 4 i 8
14 5 j 8
the formulas are as follows:
D2: =MAX(E5:E14)
D3: =COUNTIF($E$5:$E$14,"="&D2)
C5: =IF(E5=MAX($E$5:$E$14),COUNTIF($E$5:E5,"="&MAX($E$5:$E$14)),"-")
- drag this until C14
F5: =IF(ISERROR(IF(C5=MIN($D$5:$D$14),C5,SMALL($C$5:$C$14,(ROW(D5)-4)))),"",IF(C5=MIN($D$5:$D$14),C5,SMALL($C$5:$C$14,(ROW(D5)-4))))
- drag this until F14
G5: =IF(ISNA(VLOOKUP(IF(MAX($C$5:$C$14)>=COUNT($E$5:E5),COUNT($E$5:E5),""),$C$5:$E$14,2,0)),"",VLOOKUP(IF(MAX($C$5:$C$14)>=COUNT($E$5:E5),COUNT($E$5:E5),""),$C$5:$E$14,2,0))
- drag this until G14
Note that values in:
D5 to D14: refer to entities
E5 to E14: refer to scores
F5 to F14: refer to the number of entities who tied
G5 to G14: refer to the entities who tied
Hope this helps. ;-)
I have a question, though, how is the Offset Function used?
Thanks.
Problem:
Column B contains the number of points scored by each player listed in column A.
We want to retrieve the name of the player who scored the highest number of points.
Solution:
Use the INDEX, MATCH, and MAX functions in the following formula:
=INDEX(A2:A9,MATCH(MAX(B2:B9),B2:B9,0))
How can the formula be amended to pick the 2nd and 3rd highest person. I have a list with names, would like to be able to chose and show the score and name of the top 3 people
Thanks for the initial post here. I'd been trying to use the IF function to pick the lowest value in a series of prices from a given row and then look up the supplier at the head of that column. Of course Excel's IF will only process a max of 7 numbers and I have many times that.
Using INDEX, MATCH and MIN it works a treat to pickout the lowest value and then display the suppliers name in row 1. Prices in the following example are in row 12.
=INDEX($G$1:$BK$1,MATCH(MIN(G12:BK12),G12:BK12,0))
Can see from the example in the earlier posts how to pick the 2nd and 3rd largest numbers.
To select the second largest, use the following command:
=Index(A2:A9,Match(Large(B2:B9,2),B2:B9,0)
To select the third largest, replace the "2" in the Large command by a "3", etc...
What sort of formula or change would you make to my formula to pick out the 2nd and 3rd lowest values?
Thanks in anticipation. This is solving problems I've had in the "too hard" basket for years.
I think I might have solved my own problem
Where row 1 lists the suppliers names, row 14 their prices, the following picks
out the name of the 2nd cheapest seller.
=INDEX($I$1:$BM$1,MATCH(SMALL(I14:BN14,2),I14:BN14,0)
To pick out the name of the 3rd cheapest change the "2" in the small function to a "3"
=INDEX($I$1:$BM$1,MATCH(SMALL(I14:BN14,3),I14:BN14,0)
Still to quite understand what the final I14:BN14,0 in each formula does.
But the formula works and thanks to this website and the heading for this post for the initial lead!
Hi,
I'm trying to find a formula to retrieve the score of the darts player who scored the highest peg of the night. Have tried a couple of the formulas posted here with a couple of minor adjustments, but can't get the result I require.
I have attached a copy of my worksheet.
Last edited by Ghostcoy; 04-09-2010 at 06:22 AM.
You're more likely to get a response if you post this in one of the question forums.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks