Count Wins/Losses of Last N games

Hi everyone,

The task here is to extract wins losses from the '2014-2' sheet into the 'Current Game 2' beginning at cell AC6:AD21. Ditto for AJ6:AK21 except the results should mirror what is in the other cells for obvious reasons.

The data is located in the '2014-2'. Thus,

AC6:AD6 will contain 11 and 5 respectively. (may not be accurate, used for illustration purposes only)

AJ6:AK6 will contain 5 and 11 respectively. (may not be accurate, used for illustration purposes only)

Keep in mind that the "N" number is the number located in cell AD4 and AK4. Thus if that number is changed to 20 then the last 20 games will be counted for wins/losses and displayed as previously stated.

To make it easier to track progress sheet '2014-2' Line 1contains a formula for the games versus a specific opponent for all cells in range. However, I only want the last 'N' number of games versus ANY opponent. Does not have to be a specific opponent as long as it omits all blank cells in range.
W-L-of-last-N-games.xlsx
Pedrov664Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Missing-linked-file.jpg
0
Pedrov664Author Commented:
fanpages,

That may be happeing because I copied from that file and pasted so that I do not post an excessively large file. The data on that file is not required for this only the data in the '2014-2' sheet is. Please ignore all the '#REF!' errors. I simply want to maintain the cell sequence, that is also why I started the table in cell AB4 because it matches the original file. This allows me to keep track of changes from the file that is posted to the original file.

Only AB:AD and AI:AK are relevant to this question. The others are used as place holders.
0
Ejgil HedegaardCommented:
This array formula in AC6 finds the last N wins.
It is long and complex, so see the file where both win and loss are calculated.
The formula are set to calculate up to 1000 rows on the data sheet.

=SUM(--(INDIRECT("'2014-2'!"&ADDRESS(LARGE(--(LEN(INDEX('2014-2'!$A$5:$CM$1000,,MATCH($AB6,'2014-2'!$A$3:$CM$3,0)))>0)*ROW('2014-2'!$A$5:$A$1000),$AD$4),MATCH($AB6,'2014-2'!$A$3:$CM$3,0))&":"&ADDRESS(LARGE(--(LEN(INDEX('2014-2'!$A$5:$CM$1000,,MATCH($AB6,'2014-2'!$A$3:$CM$3,0)))>0)*ROW('2014-2'!$A$5:$A$1000),1),MATCH($AB6,'2014-2'!$A$3:$CM$3,0)))>INDIRECT("'2014-2'!"&ADDRESS(LARGE(--(LEN(INDEX('2014-2'!$A$5:$CM$1000,,MATCH($AB6,'2014-2'!$A$3:$CM$3,0)+1))>0)*ROW('2014-2'!$A$5:$A$1000),$AD$4),MATCH($AB6,'2014-2'!$A$3:$CM$3,0)+1)&":"&ADDRESS(LARGE(--(LEN(INDEX('2014-2'!$A$5:$CM$1000,,MATCH($AB6,'2014-2'!$A$3:$CM$3,0)+1))>0)*ROW('2014-2'!$A$5:$A$1000),1),MATCH($AB6,'2014-2'!$A$3:$CM$3,0)+1))))
W-L-of-last-N-games.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pedrov664Author Commented:
Ejgil,

I do not know how you keep track of such a complicated formula but it works!

Thanks for all your hard work.

BTW, I only planned to look up say the last 20 games but with your formula I may go as high as 50.
0
Pedrov664Author Commented:
the formula is so good my head is still spinnning!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.