Solved

calculations based on last 5 values in table

Posted on 2014-09-18
11
103 Views
Last Modified: 2014-09-25
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.
EE-Last-5-Values.xlsx
0
Comment
Question by:Pedrov664
  • 5
  • 3
  • 3
11 Comments
 
LVL 19

Expert Comment

by:Ken Butters
Comment Utility
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)
    Next
 

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 :
=addlastrows(B7,"W",5)

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.
EE-Last-5-Values.xlsm
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
Pedrov664,
You have several open questions regarding this request:
Count last 10 items of data
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28517901.html

need to calculate based on the last 5 items of data
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28516029.html

and I addressed it in an earlier question:
need code to pull data from one table and insert into another
http://www.experts-exchange.com/Programming/Microsoft_Development/Q_28513798.html

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.

Regards,
-Glenn
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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:
C5: =SUM(OFFSET(INDIRECT(CELL("address",INDIRECT($B$7))),ROWS(INDIRECT($B$7))-5,3,5,1))
D5: =SUM(OFFSET(INDIRECT(CELL("address",INDIRECT($B$7))),ROWS(INDIRECT($B$7))-5,4,5,1))
E5: =AVERAGE(OFFSET(INDIRECT(CELL("address",INDIRECT($B$7))),ROWS(INDIRECT($B$7))-5,5,5,1))
F5: =MIN(OFFSET(INDIRECT(CELL("address",INDIRECT($B$7))),ROWS(INDIRECT($B$7))-5,9,5,1))
G5: =MAX(OFFSET(INDIRECT(CELL("address",INDIRECT($B$7))),ROWS(INDIRECT($B$7))-5,9,5,1))
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)

Regards,
-Glenn
EE-Last-5-Values-mod.xlsx
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
By the way, this also directly answers your question:
need to calculate based on the last 5 items of data
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28516029.html

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

Regards,
-Glenn
0
 

Author Comment

by:Pedrov664
Comment Utility
Glen,

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.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Pedrov664
Comment Utility
Glen,

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.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
Comment Utility
Hi,

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:
=SUM(DELGADO[[#Data],[W]])
=DELGADO[[#Totals],[W]]
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]"))
E5: =AVERAGE(INDIRECT(B7&"[ERA]"))

Regards,
-Glenn
0
 
LVL 19

Expert Comment

by:Ken Butters
Comment Utility
Did you try my solution?  It is flexible enough to allow you to account for the blank row or not.
0
 

Author Comment

by:Pedrov664
Comment Utility
Glen,

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.

Ken,

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.)
EE-Last-5-Values-mod.xlsx
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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
0
 
LVL 19

Expert Comment

by:Ken Butters
Comment Utility
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.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

772 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

10 Experts available now in Live!

Get 1:1 Help Now