How to display the latest 20 dates from a data sheet? (golf handicap index calculation)

[TXT007]

Hi,

I am trying to work on excel to calculate golf handicap index. Can someone help me how to extract the 20 latest dates (if there are 20 games), so i can do the calculation of the lower 10 differentials (or accordingly to numbers of games played by players).

In the Scores sheet, not every player plays the same number of games.
In the latest20scores sheet, I search a lot on internet but still can't find a solution to pull the latest 20 dates played by each player. I wanted to do it on the same sheet, but figure it'd be too complicated as formula, so separated it into two sheet. If i can pull out the latest 20 (if less than 20, then all the dates played) dates of games played by each player. So the next eventual games entered in Scores sheet will be automatically taken into account in Latest20scores sheet.

I have read that min or max could work, but i can't figure how to combien, index/match/min or max (or small/large).

(dont bother Sheet2, i just tried another method, but this only applies to those who have played 20 games)

Appreciate your help.
thanks
txt007

[XLent]

things I'd always recommend:

1. avoid use of merged cells if you can (for horizontal "merging" you can just use 'centre across selection' option)
2. be consistent in your layout

in the attached I have corrected for the above -- thereafter, the key is generating the date value, e.g.:

Please Login or Register  to view this content.

where row 3 now holds the column index of given player, this way you can apply the same formula block (A:F) to each other player block without needing to modify.

hopefully all will become clear when you review the file provided.

[Limor_OP]

Hi,
Sorry to iterrupt, but beginning to review the file I saw that for player A the same game appears twice( lines 6&7 on Latest20scores tab), and on the other hand game "Walt Disney-Palm" (line 34 in Scores tab) is missing. Is this how it should be?

In addition - for the "Eagle Creek Orlando" game there should be 2 different scores (101 &98) but the 20score table shows same score twice (101).

[Limor_OP]

Hi,

Here is my solution using basically the OFFSET formula.

I left the last block empty,
You can fill in player D data by copying the formula from each corresponding title ("Date" of player C to "Date" of player D , "Rating" of player C formula into "Rating of player D column).
Just dont drag the formula, copy it into the cell window.

Then replace "G" column wherever it is present in the formula (that represents player C in the "Scores" tab) to column H which ties to player D.

Hope this helps.

[TXT007]

thank you very much XLent for your advice and help with the aggregate function. However, I will have to find a way to make it doesnt repeat on the same date and score when there are two games on same date on different courses, or two games on same course and same date.

Thanks a bunch.
txt007

[TXT007]

Thank your Belinda200 for your help. The offset function (i don't even know how to use it) does work well for what i want as result. Merci beaucoup!

On the other hand, could I ask you to help check my formula in F2? The rule is on DiffTable. I try to use If(... average(small) ... but it doesnt seem to work

txt007

[Limor_OP]

You're welcome.
Can you explain what you trying to find in F2? I'm not sure I understand what does average of differetial stand for and how does it relate to the DiffTable.
what is the result you were expecting to receive in cell F2? and what does it reflect?

Please explain the rationale behind your formula in words.

Thanks.

[Limor_OP]

OK, I figured it out, you had a problem with the position of the brackets.
see attached the amended file.

Just one thing thatperhaps you already know but can assist greatly when creating excel formulas - is that if you stand on the formula , for example, after the first "If" - there is a line appearing, stating
IF(Logical Test,[value if true],[value if flase]
each one is a kind of a link , so if you click on one of the links it shows you where the statement should begin, and where it ends, so that you know where to put the bracelets in the right place.

[XLent]

...I will have to find a way to make it doesnt repeat on the same date and score when there are two games on same date on different courses, or two games on same course and same date.

For completeness, updated version attached to address the above.

[TXT007]

thanks a bunch Belinda200.
Very much appreciated!

txt007

[TXT007]

Thank you thank you XLent!
I have learned from your solution using the @ and the countif () -1
I never know the @ usage

merci beaucoup!
txt007