Solved

Count Wins/Losses of Last N games

Posted on 2014-09-23
5
332 Views
Last Modified: 2014-09-23
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
0
Comment
Question by:Pedrov664
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 40339619
Missing-linked-file.jpg
0
 

Author Comment

by:Pedrov664
ID: 40339657
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
 
LVL 22

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 40340255
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
 

Author Comment

by:Pedrov664
ID: 40340279
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
 

Author Closing Comment

by:Pedrov664
ID: 40340285
the formula is so good my head is still spinnning!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question