Solved

Count last 10 items of data

Posted on 2014-09-13
12
234 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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
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
 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

773 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