Solved

Count last 10 items of data

Posted on 2014-09-13
12
223 Views
Last Modified: 2014-09-23
Hi everyone,

I am looking for a way to count the last 10 items that are greather than and less than in a particular set of cells.

I am using the following formula for the less than and greather count.

=SUMPRODUCT(--(C13:C22>D13:D22)) = formula in cell C2
=SUMPRODUCT(--(C13:C22<D13:D22)) = formula in cell D2

This gets the job done with some issues.

First, I have to manually change the cell ranges to match after data is added.

Second, any blanks in between may be omitted so that I do not get the full 10 count range I want.

Does anyone know of a way to do this without manually changing the cell ranges each time data is added.

P.S. I tried using INDIRECT("C" & B2) to use the value in cell B2 as the last cell number but I get an error when inserted in place of the static cell range used in the formula.
EE-CountLast10.xlsx
0
Comment
Question by:Pedrov664
  • 6
  • 4
  • 2
12 Comments
 
LVL 22

Assisted Solution

by:Flyster
Flyster earned 250 total points
ID: 40321361
Refer to Sheet2 of the attached. In L2 this formula is used to find the lower end of your range:

=(COUNTA(C4:C1000))-5

It counts all non-blank cells from C4 to C1000. 5 is subtracted to account for the fact your data starts in row 4  and to be 1 less then max. This formula is used in L3:

=MATCH(9.9E+307,C:C,1)

It is just one of the many out there to find the last non-blank cell in a column. These two are used for the variable's for your formula's in row 2. C2 uses your original formula with indirect used to handle the variables:

=SUMPRODUCT(--(INDIRECT("C"&$L2&":C"&$L3)>INDIRECT("D"&$L2&":D"&$L3)))

If this works for you, just hide column L!

Flyster
EE-CountLast10.xlsx
0
 
LVL 22

Expert Comment

by:Flyster
ID: 40323570
This copy has all cells in row 2 formatted and column L hidden.
EE-CountLast10.xlsx
0
 

Author Comment

by:Pedrov664
ID: 40330604
Flyster,

Don't know what I am doing wrong.

I am trying to adapt the formulas you gave in the worksheet. (See Attached file sheet 3).

And I am not getting the desired results.

First I changed the formulas in 'L' to 'A' since I have other data in 'L'. Then formula in C1 gives me 37 which is higher than the total for last 10 win/losses.

I hope you can help me make this right.
EE-CountLast10W-SHEET3.xlsx
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40331705
I tried to create an array function that would calculate the row where the last ten games recorded starts, but was not able to combine OFFSET/COUNT functions that generate dynamic ranges (I'll probably post my own question on that).

But in the meantime, I was able to resolve the issue by adding a helper column (in F) that calculates that starting row needed to then calculate the last 10 games.  The result is then incorporated into two SUMPRODUCT functions for that subrange.

1) In column F, cell F5, insert this function and copy down:
=(COUNT(OFFSET($C$5,ROW()-5,0,20,1))=10)*ROW()
2) In cell A1, insert this function
=MAX(F1:F300)
This determines the top row for the last 10 games
3) In cell C1, insert
=SUMPRODUCT(--(INDIRECT("C"&A1&":C"&A3)>(INDIRECT("D"&A1&":D"&A3))))
4) In cell D1, insert
=SUMPRODUCT(--(INDIRECT("C"&A1&":C"&A3)<(INDIRECT("D"&A1&":D"&A3))))

The sum of C1 and D1 should always total to 10.  I added the most-recent game results for Baltimore and the tally appears correct.

Modified file attached.

-Glenn
EE-CountLast10W-SHEET3b.xlsx
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 250 total points
ID: 40332025
I got a very good solution to the "last N data" problem from krishnakrkc in my repackaged question and am applying that here.  These steps supercede what I posted above.

1) In cell A1, insert this array function ([Ctrl]+[Shift]+[Enter]):
=ROW(INDEX($C$5:$C$229,LARGE(IF($C$5:$C$229<>"",ROW($C$5:$C$229)),10)-4))
This determines the top row for the last 10 games
2) In cell C1, insert
=SUMPRODUCT(--(INDIRECT("C"&A1&":C"&A3)>(INDIRECT("D"&A1&":D"&A3))))
3) In cell D1, insert
=SUMPRODUCT(--(INDIRECT("C"&A1&":C"&A3)<(INDIRECT("D"&A1&":D"&A3))))

Modified file attached.
EE-CountLast10W-SHEET3b.xlsx
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40338117
Pedro,
Did you have any questions about this solution?  If so, please let me know.

Regards,
-Glenn
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Pedrov664
ID: 40339338
Flyster, Glenn:

I am going to accept both of your solutions. It appears that it is too complicated for me. I am trying to apply it to other teams and am not able to do so without erros.

That said, I am using another solution to get around this problem. Both are getting egual points here for all your hard work.

Thank you,

P.S. It is not that your solution does not work it is just that I just do not know enough to apply it the way I need to.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40339557
Thanks for recognizing our contributions.  

I will say again - as I noted in my first experience with your work - that you should try to get all your data into a single data table as it will make retrieving and manipulating the data much, much easier. :-)

-Glenn
0
 

Author Comment

by:Pedrov664
ID: 40339637
Glenn,

I am assuming you mean that I need to eliminate the lines between tables. I am not too familiar with what you speak of. How would I be able to differentiate between the different player stats? Would the formulas currently used still work?

Perhaps if you send me an attachement with the tables as you say they work best I would understand better.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40339676
Sure.  Here is an example file that I created when I first started working with your data.

This was in in your question "need code to pull data from one table and insert into another".  I proposed that you combine all the pitcher records into one Excel Table so that you could have a singular source for query and also have the advantage of using PivotTables, if desired.  

I believe because you are copying data from the MLB.com website, you find it cumbersome to combine all the data in this manner.  However, I believe the reporting capability you gain will outweigh the data collection cost.

Regards,
-Glenn
EE-DataCalculations.xlsx
0
 

Author Comment

by:Pedrov664
ID: 40340291
Glenn,

Looks like it will mean using different formulas to get to the results, am I correct?
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40340305
It would, but in most cases the formulas would be simpler.  I noted that some of the formulas in that example file aren't correct or complete (my doing), but I think they could be updated in a short timeframe.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now