Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a name with biggest number
Hi, I've got another problem which I can't figure out :) I have a hockey scoring sheet, which has player names, goals and assist with it. What I want to do, is to print in to a different sheet player's name and goal amount, who has scored the most goals. Example of a scoring sheet: Code: -------------------- ----A---- ----B---- ----C---- Player Goals Assists Jagr 33 43 Staal 33 36 Alfredsson 32 38 Kovalchuk *35* 33 Heatley 32 36 Ovechkin 34 31 -------------------- The generated result I'm looking for would look like this: Code: -------------------- ----A---- ----B---- ----C---- Player Goals Assists Kovalchuk 35 33 -------------------- I know this sounds silly, but what I need is more complicated than this and I want to search the best scorer overall from different result sheets. I want to do daily/monthly/ect statistics so Pivot Point system isn't useful (Or at least I think so). The generated sheet should always change if someone else has scored more goals. Is this possible to do with a normal excel function? -- Handyy ------------------------------------------------------------------------ Handyy's Profile: http://www.excelforum.com/member.php...o&userid=30958 View this thread: http://www.excelforum.com/showthread...hreadid=507942 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a name with biggest number
Hi,
Lets say your source data is in Sheet 2 andin the range A1:C8, use this formula in Sheet 1 to get the details of the highest scorer: in Cell A1, to get the player name = =OFFSET(Sheet2!A1,MATCH(LARGE(Sheet2!$B$1:$B$8,1), Sheet2!$B$1:$B$8,0)-1,0) and copy it across to cell B1 and C1 to get the Goals and Assists. Regards Govind. Handyy wrote: Hi, I've got another problem which I can't figure out :) I have a hockey scoring sheet, which has player names, goals and assist with it. What I want to do, is to print in to a different sheet player's name and goal amount, who has scored the most goals. Example of a scoring sheet: Code: -------------------- ----A---- ----B---- ----C---- Player Goals Assists Jagr 33 43 Staal 33 36 Alfredsson 32 38 Kovalchuk *35* 33 Heatley 32 36 Ovechkin 34 31 -------------------- The generated result I'm looking for would look like this: Code: -------------------- ----A---- ----B---- ----C---- Player Goals Assists Kovalchuk 35 33 -------------------- I know this sounds silly, but what I need is more complicated than this and I want to search the best scorer overall from different result sheets. I want to do daily/monthly/ect statistics so Pivot Point system isn't useful (Or at least I think so). The generated sheet should always change if someone else has scored more goals. Is this possible to do with a normal excel function? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a name with biggest number
What happens if more than one player has the most goals?
Biff "Handyy" wrote in message ... Hi, I've got another problem which I can't figure out :) I have a hockey scoring sheet, which has player names, goals and assist with it. What I want to do, is to print in to a different sheet player's name and goal amount, who has scored the most goals. Example of a scoring sheet: Code: -------------------- ----A---- ----B---- ----C---- Player Goals Assists Jagr 33 43 Staal 33 36 Alfredsson 32 38 Kovalchuk *35* 33 Heatley 32 36 Ovechkin 34 31 -------------------- The generated result I'm looking for would look like this: Code: -------------------- ----A---- ----B---- ----C---- Player Goals Assists Kovalchuk 35 33 -------------------- I know this sounds silly, but what I need is more complicated than this and I want to search the best scorer overall from different result sheets. I want to do daily/monthly/ect statistics so Pivot Point system isn't useful (Or at least I think so). The generated sheet should always change if someone else has scored more goals. Is this possible to do with a normal excel function? -- Handyy ------------------------------------------------------------------------ Handyy's Profile: http://www.excelforum.com/member.php...o&userid=30958 View this thread: http://www.excelforum.com/showthread...hreadid=507942 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a name with biggest number
Here's a non-array formulas play which draws from source data in 2 separate,
identically structured sheets, and ultimately provides an auto full descending sort of all players by Goals (using an arb tiebreaker) A sample construct is available at: http://www.savefile.com/files/4087988 Full Descending Sort with TieBreaks From 2 Sheets.xls Assume 2 source tables in sheets named: X and Y data in cols A to C, from row2 down to row11 (say) In A1:C1 are col headers: Player, Goals, Assists (players listed in each sheet are assumed unique) In a new sheet: Z List the sheetnames down in D2:D3 : X, Y Put in E2: =IF(INDIRECT("'"&INDEX($D:$D,COLUMN(A1)+1)&"'!A"&R OW(2:2))="","",ROW()) Copy E2 to F2, then fill down by as many rows as required to cover the max expected extents in X and Y. As the max expected data in X and Y is 10 rows each, fill down to F11. Paste the same col headers in A1:C1, viz.: Player, Goals, Assists Put in A2: =IF(ISERROR(SMALL($E:$E,ROW(E1))), IF(ISERROR(SMALL($F:$F,ROW(E1)-COUNT($E:$E))),"", INDEX(Y!A:A,MATCH(SMALL($F:$F,ROW(E1)-COUNT($E:$E)),$F:$F,0))), INDEX(X!A:A,MATCH(SMALL($E:$E,ROW(E1)),$E:$E,0))) Copy A2 to C2, then fill down to cover the *total* extent of data in X and Y, i.e. in this case, filled down by 10 rows per sheet x 2 sheets = 20 rows, to C21 Z auto-returns a combined stacked listing of data from the source tables in X and Y (data from X stacked above Y) Then, in a sheet: Rank (say) Paste the same col headers in A1:C1, viz.: Player, Goals, Assists Put in A2: =IF(ISERROR(LARGE($D:$D,ROW(A1))),"", INDEX(Z!A:A,MATCH(LARGE($D:$D,ROW(A1)),$D:$D,0))) Copy A2 to C2 Put in D2: =IF(Z!B2="","",Z!B2-ROW()/10^10) (Leave D1 empty) (Col D is the arb tie-breaker col) Select A2:D2, fill down to C21 (cover the same extent as the full list in Z) The above will auto-return a full descending sort of all the players by the Goals col. Players with tied goals, if any, will appear in the same relative order that they appear in the combined list in Z. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Handyy" wrote in message ... Hi, I've got another problem which I can't figure out :) I have a hockey scoring sheet, which has player names, goals and assist with it. What I want to do, is to print in to a different sheet player's name and goal amount, who has scored the most goals. Example of a scoring sheet: Code: -------------------- ----A---- ----B---- ----C---- Player Goals Assists Jagr 33 43 Staal 33 36 Alfredsson 32 38 Kovalchuk *35* 33 Heatley 32 36 Ovechkin 34 31 -------------------- The generated result I'm looking for would look like this: Code: -------------------- ----A---- ----B---- ----C---- Player Goals Assists Kovalchuk 35 33 -------------------- I know this sounds silly, but what I need is more complicated than this and I want to search the best scorer overall from different result sheets. I want to do daily/monthly/ect statistics so Pivot Point system isn't useful (Or at least I think so). The generated sheet should always change if someone else has scored more goals. Is this possible to do with a normal excel function? -- Handyy ------------------------------------------------------------------------ Handyy's Profile: http://www.excelforum.com/member.php...o&userid=30958 View this thread: http://www.excelforum.com/showthread...hreadid=507942 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a name with biggest number
Typo in line:
Select A2:D2, fill down to C21 Should read as: Select A2:D2, fill down to D21 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a name with biggest number
Just magnificiant replies in this forum for problems, I can't thank you experts enough! Govind Wrote: Hi, in Cell A1, to get the player name = =OFFSET(Sheet2!A1,MATCH(LARGE(Sheet2!$B$1:$B$8,1), Sheet2!$B$1:$B$8,0)-1,0) and copy it across to cell B1 and C1 to get the Goals and Assists. This seems useful for my purposes but the problem occurs when there is more than one player who has scored same amount of goals. Can it be altered somehow, that if there is several players with same goal amount, it would print "x players tied with x goals"? That would come in good use, if it's possible to do easily. Max Wrote: Here's a non-array formulas play which draws from source data in 2 separate, identically structured sheets, and ultimately provides an auto full descending sort of all players by Goals (using an arb tiebreaker) Thank you very much for this great effort, I'm not much an Excel expert so I have to study that more before I learn to do that myself. I downloaded your example file and it looks excellent for statistical purposes! -- Handyy ------------------------------------------------------------------------ Handyy's Profile: http://www.excelforum.com/member.php...o&userid=30958 View this thread: http://www.excelforum.com/showthread...hreadid=507942 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a name with biggest number
You're welcome !
... but the problem occurs when there is more than one player who has scored same amount of goals. The earlier sample provided already takes care of ties, but is more involved because there were 2 separate source lists. Here's a revised "simpler" example (assumes only a single source sheet), which focuses on just the auto-extracting of the full descending list by goals (ties are catered for) in another sheet: http://cjoint.com/?cdxwPg1vTo ExtractDescendingSortedList_Handyy_wks.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Handyy" wrote in message ... Just magnificiant replies in this forum for problems, I can't thank you experts enough! Govind Wrote: Hi, in Cell A1, to get the player name = =OFFSET(Sheet2!A1,MATCH(LARGE(Sheet2!$B$1:$B$8,1), Sheet2!$B$1:$B$8,0)-1,0) and copy it across to cell B1 and C1 to get the Goals and Assists. This seems useful for my purposes but the problem occurs when there is more than one player who has scored same amount of goals. Can it be altered somehow, that if there is several players with same goal amount, it would print "x players tied with x goals"? That would come in good use, if it's possible to do easily. Max Wrote: Here's a non-array formulas play which draws from source data in 2 separate, identically structured sheets, and ultimately provides an auto full descending sort of all players by Goals (using an arb tiebreaker) Thank you very much for this great effort, I'm not much an Excel expert so I have to study that more before I learn to do that myself. I downloaded your example file and it looks excellent for statistical purposes! -- Handyy ------------------------------------------------------------------------ Handyy's Profile: http://www.excelforum.com/member.php...o&userid=30958 View this thread: http://www.excelforum.com/showthread...hreadid=507942 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a name with biggest number
I'm having difficulties with that simplier sorting worksheet. It works great when I do exactly like you did, but when I try to insert it into my own sheet, it doesn't work anymore. I tried to figure out why it doesn't work, and it seems that it needs to start from the first row of the sheet. I can't really understand why is that and is there a way to go around this problem? Since I would need the descending sorted results in the middle of a sheet, I just can't get it work. Here's a link to an example of the problem: http://www.kolumbus.fi/handyy/misc/problem.xls -- Handyy ------------------------------------------------------------------------ Handyy's Profile: http://www.excelforum.com/member.php...o&userid=30958 View this thread: http://www.excelforum.com/showthread...hreadid=507942 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a name with biggest number
For each "set", we need to reset the top left anchor cell's row incrementer
part of the formula back to point to row #1, i.e. use ROW(A1)** in the anchor cell's formula. Also, we could omit referencing the header row (row11) for neatness, so just use instead in the 2nd set's anchor cell G2: =IF(ISERROR(LARGE($K$12:$K$16,ROW(A1))),"", INDEX(A$12:A$16,MATCH(LARGE($K$12:$K$16,ROW(A1)),$ K$12:$K$16,0))) Then copy G2 to J2, fill down (No change is required to the criteria formula as filled in K12:K16) **Using ROW(A1) is just a "std" practice. We could also use ROW(B1) or ROW(G1) instead, all will evaluate to the same result. Here's your sample, with the corrected formulas implemented: http://cjoint.com/?cghRlF0E1u Handyy_wks_problem.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Handyy" wrote in message ... I'm having difficulties with that simplier sorting worksheet. It works great when I do exactly like you did, but when I try to insert it into my own sheet, it doesn't work anymore. I tried to figure out why it doesn't work, and it seems that it needs to start from the first row of the sheet. I can't really understand why is that and is there a way to go around this problem? Since I would need the descending sorted results in the middle of a sheet, I just can't get it work. Here's a link to an example of the problem: http://www.kolumbus.fi/handyy/misc/problem.xls -- Handyy ------------------------------------------------------------------------ Handyy's Profile: http://www.excelforum.com/member.php...o&userid=30958 View this thread: http://www.excelforum.com/showthread...hreadid=507942 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a name with biggest number
Sorry, correction to typos in lines:
... so just use instead in the 2nd set's anchor cell G2: .... Then copy G2 to J2, fill down Should read as: ... so just use instead in the 2nd set's anchor cell G12: .... Then copy G12 to J12, fill down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a name with biggest number
Excellent, it works now smoothly. Thanks for all the help and effort! -- Handyy ------------------------------------------------------------------------ Handyy's Profile: http://www.excelforum.com/member.php...o&userid=30958 View this thread: http://www.excelforum.com/showthread...hreadid=507942 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding a name with biggest number
You're welcome !
Thanks for the feedback .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Handyy" wrote in message ... Excellent, it works now smoothly. Thanks for all the help and effort! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding the right number | Excel Worksheet Functions | |||
Number of labels on X-axis one more than number of values on Y-axi | Charts and Charting in Excel | |||
Need number of Saturdays and number of Sundays between 2 dates | Excel Worksheet Functions | |||
Finding a number in a table? | Excel Discussion (Misc queries) | |||
finding row number? | Excel Discussion (Misc queries) |