Calculating handicap from table in descending order using sumproduct

[chuckf201]

I'm trying to calculate a billiard players handicap (wins/Total games) using 'Lname', 'Total Wins', and 'Total Games'. The formula I'm
using works great for the complete table. The table is sorted by date, ascending order with oldest date in row 1.

=SUMPRODUCT(--(tblHistory[Total Wins]>0),--(tblHistory[Lname]="lane"),tblHistory[Total Wins]) / (SUMPRODUCT(--(tblHistory[Total Games]>0),--(tblHistory[Lname]="lane"),tblHistory[Total Games]))

This gives me the handicap for the entire table.
I would like to calculate handicap for the last 100 games played from tblhistory[Total Games].
Thanks in advance

 

[jasonb75]

Perhaps something like this to find the 100th most recent bases on date (change the [Dates] column name to match your actual table).

=SUMIFS(tblHistory[Total Wins],tblHistory[Lname],"lane",tblHistory[Dates],"<="&LARGE(tblHistory[Dates],100))/SUMIFS(tblHistory[Total Games],tblHistory[Lname],"lane",tblHistory[Dates],"<="&LARGE(tblHistory[Dates],100))

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)