Excel formulas
 

I want to find the X most recent results for a given tennis player in a table like the one in this link: https://www.flickr.com/photos/134075.../shares/SM921b

So the desired result would be e.g. 'of Andy Murray's 10 most recent games, 6 were wins and 4 were losses'.

I've tried using the large function with the date field in different ways but nothing seems to work. E.g.

=COUNTIFS($A$2:$A$30,"andy murray",$B$2:$B$30,LARGE($B$2:$B$30,{1,2,3,4,5}),$ C$2:$C$30,"W")

=AND($A$2:$A$30="andy murray",$B$2:$B$30=LARGE($B$2:$B$30,1),$C$2:$C$30= "W")

Repeating this last one when k=2, k=3 etc.

Everything I've tried has returned either error messages or incorrect results. Any help would be greatly appreciated.

Excel formulas
 

hi,

the formula is an array formula to insert with CTRL+Shift+Enter
=SUMPRODUCT(($A$2:$A$30="andy
murray")*($C$2:$C$30="W")*($B$2:$B$30=LARGE(IF(A2 :A30="andy murray",B2:B30),10)))

isabelle

Le 2015-06-17 23:16, echounlucky a écrit :
I want to find the X most recent results for a given tennis player in a table like the one in this link:
https://www.flickr.com/photos/134075.../shares/SM921b

So the desired result would be e.g. 'of Andy Murray's 10 most recent games, 6 were wins and 4 were losses'.

I've tried using the large function with the date field in different ways but nothing seems to work. E.g.

=COUNTIFS($A$2:$A$30,"andy murray",$B$2:$B$30,LARGE($B$2:$B$30,{1,2,3,4,5}),$ C$2:$C$30,"W")

=AND($A$2:$A$30="andy murray",$B$2:$B$30=LARGE($B$2:$B$30,1),$C$2:$C$30= "W")

Repeating this last one when k=2, k=3 etc.

Everything I've tried has returned either error messages or incorrect results. Any help would be greatly appreciated.

Excel formulas
 

in cases where there would be less than 10 results

=SUMPRODUCT(($A$2:$A$30="andy
murray")*($C$2:$C$30="W")*($B$2:$B$30=LARGE(IF(A2 :A30="andy
murray",B2:B30),MIN(COUNTIF(A2:A20,"andy murray"),10))))

isabelle

Le 2015-06-18 09:26, isabelle a écrit :
hi,

the formula is an array formula to insert with CTRL+Shift+Enter
=SUMPRODUCT(($A$2:$A$30="andy
murray")*($C$2:$C$30="W")*($B$2:$B$30=LARGE(IF(A2 :A30="andy murray",B2:B30),10)))

isabelle

Le 2015-06-17 23:16, echounlucky a écrit :
I want to find the X most recent results for a given tennis player in a table
like the one in this link:
https://www.flickr.com/photos/134075.../shares/SM921b

So the desired result would be e.g. 'of Andy Murray's 10 most recent games, 6
were wins and 4 were losses'.

I've tried using the large function with the date field in different ways but
nothing seems to work. E.g.

=COUNTIFS($A$2:$A$30,"andy
murray",$B$2:$B$30,LARGE($B$2:$B$30,{1,2,3,4,5}),$ C$2:$C$30,"W")

=AND($A$2:$A$30="andy murray",$B$2:$B$30=LARGE($B$2:$B$30,1),$C$2:$C$30= "W")

Repeating this last one when k=2, k=3 etc.

Everything I've tried has returned either error messages or incorrect results.
Any help would be greatly appreciated.

Excel formulas
 

That seems to work, thank you! :)

Excel formulas
 

thank you for your reply, i'm glad i could help
isabelle

Le 2015-06-18 19:39, echounlucky a écrit :
That seems to work, thank you! :)