calculations based on last 5 values in table

Hi everyone,

I would like to be able to calculate based on the last 5 values in tables on the attached workbook.

The sheet named "calculations" contains desired values for the last 10 games played for the player named in cells B7 and J7 respectively, using formulas in the green section. The player tables are located in the 'ARI' and 'BAL' worksheets.

The pink and blue section is where I'd like to make the same calculations as above but using the last 5 values only.
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.

Ken ButtersCommented:
I was able to do what you want by creating a user defined function (UDF).

This requires VBA... so step 1 is to save you spreadsheet as *.xlsm instead of *.xlsx

then add a module in the VBA Editor where I can store the VBA code.

The following is the code:
Function addLastRows(table As Range, ColumnName As String, NumberRows As Long) As Long

    Dim columnRange As Range
    Dim tableName As String
    Dim lowerbound, i As Long
    tableName = table.Value
    addLastRows = 0

    Set columnRange = Range(table.Value & "[" & ColumnName & "]")
    lowerbound = columnRange.Count - NumberRows
    If lowerbound < 0 Then: lowerbound = 0
    For i = columnRange.Count To lowerbound Step -1
       addLastRows = addLastRows + columnRange(i)

End Function

Open in new window

To use the code in your worksheet... for example in cell C5 where you currently have the formula
=INDIRECT(B7 & "[[#Totals],[W]]")

The formula would change to :

The 5 is the count of the number of rows you want to add at the bottom of the table.   I made it variable so you could change it to 6 easily if you want to.   The reason being is that the way your table is defined... you have 1 blank row at the bottom, so I wasn't sure if you wanted to count the last blank row as your fifth row or not.  If not... then change it to 6... and that will include in your total the last 5 non-blank rows (and a blank 6th).

I am also going to attach an updated excel document with the code and updated formulas in cells C5/D5 of the Calculations sheet.

One last note... I am currently passing to the function the table as a range for the first parameter, because that is how you used it in your original function.  If you want to change that to a string name instead... that is doable... it would just require a small code change.
Glenn RayExcel VBA DeveloperCommented:
You have several open questions regarding this request:
Count last 10 items of data

need to calculate based on the last 5 items of data

and I addressed it in an earlier question:
need code to pull data from one table and insert into another 

I recommend that you follow up on those questions first - either close them if no satisfactory answer was provided or acknowledge the solutions for the first two examples listed.

Glenn RayExcel VBA DeveloperCommented:
Your example workbook does not have game numbers (ex., 1 through 10) for all your tables.  If you did have them all populated, that would make the solution significantly simpler than if this column is left blank.

First, and most importantly, your tables should have no blank rows in them at all.  I've mentioned this previously and I'm sure it's a carry-over from your earlier tables.  I do see that you added total rows, however, which is very good.  I've removed the blank rows from all the tables to make this work.

If your tables do not show game numbers, the following formulas will return results for the last five rows of data:
H5: =MIN(OFFSET(INDIRECT(CELL("address",INDIRECT($B$7))),ROWS(INDIRECT($B$7))-5,11,5,1))
I5: =MAX(OFFSET(INDIRECT(CELL("address",INDIRECT($B$7))),ROWS(INDIRECT($B$7))-5,11,5,1))

And you can copy these over to K-Q and just change the cell reference from $B$7 to $J$7.

I've attached a modified workbook with these formulas.  I've also taken the liberty to suggest an alternate layout for your pitcher matchup table that might be easier to read and offer expandability in the future (ex., All Games..last 5, last 10)

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Glenn RayExcel VBA DeveloperCommented:
By the way, this also directly answers your question:
need to calculate based on the last 5 items of data

since it refers to identically-constructed tables and inserts formulas to calculate the last five rows (games) of data.  

Pedrov664Author Commented:

The blank row was added by another user who came up with the formula you see in my original post here. It was pointed out that the blank row was needed, otherwise excel would calculate using the total row as well. It seems you have a way around that wich is good.

BTW: What do you mean, "If your tables do not show game numbers, the following formulas will return results for the last five rows of data:"? Does it mean that I should not add numbers 1-10 to the left as you suggested earlier? Believe me, I would have liked to but was made to feel, by other posters in other q's, that the more data I add to the tables the more difficult it would be to work with them.

That said, I will test the formulas you gave me to see ifI get the desired results. If I do then you are correct you may have answered two at the same time.
Pedrov664Author Commented:

1) I am unable to delete the blank row between the 'data' and the 'total' row at the bottom of each table. This is because when I click the blank row the delete button does not work and when I right click the delete is not visible. How can I delete the blank row?

1.a) Without deleting the blank row I am getting an answer that is one less than expected. So it seems that deleting the blank row is necessary for your formula to work.

2) Is the total row necessary for your formula to work? Although I think it may be necessary for top row formula to work.
Glenn RayExcel VBA DeveloperCommented:

1) I already removed the extra blank rows in ALL the tables in the example file I attached above, so you shouldn't have to remove any.  And yes, they had to be removed for the example formulas I provided.  I don't know why you can't delete any rows; just right-click on the last blank row (after the gray border and just above the total row) and select "Delete" and then "Table Rows"

2) If you use Excel Tables and structured referencing, you can differentiate the data from the total row as in this example:
Both of these functions happen to return the same result (2), but if you changed the total formula in the table for the W column, the answer here would be changed accordingly.

As you can see from the first of these two examples, the total row is NOT necessary to make the formulas work, but if you do remove them, the formulas for all Wins, Losses, and overall ERA will have to be changed since they currently refer to the Totals value for those categories. For example:
C5: =SUM(INDIRECT(B7&"[W]"))
D5: =SUM(INDIRECT(B7&"[L]"))


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
Ken ButtersCommented:
Did you try my solution?  It is flexible enough to allow you to account for the blank row or not.
Pedrov664Author Commented:

I have attached an updated file that still has the issue of not letting me delete the blank row. Do not know what I am doing wrong but I need to do this to make your solution work.

For instance, I click on '14' to highlight the entire row. But when I click delete nothing happens and 'delete' does not show up on right click.


Looks like your code just got lost in all the clutter. However, I need code in the table itself that is changeable and copyable. (i.e. I am only showing the first rows of data once complete I have to be able to copy down to populate the entire table with the same code.)
Glenn RayExcel VBA DeveloperCommented:
Normally, to remove a row within an Excel Table, you must right-click on a cell WITHIN THE TABLE.

So, to remove the last row in the DELGADO table, place your cursor anywhere between B14:P14, right-click, then select "Delete", "Table Rows".  You have to do this for each table (which is what I did).

Fastest way to get through this is
1) right-click in the blank row
2) Type [D],[R] on the keyboard
Ken ButtersCommented:
Once you have a user defined function .... It can be copied in your table and you can do a fill down... Just like any built in function.
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.