Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

calculations based on last 5 values in table

Posted on 2014-09-18
11
Medium Priority
?
112 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
[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
  • 5
  • 3
  • 3
11 Comments
 
LVL 19

Expert Comment

by:Ken Butters
ID: 40330902
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
ID: 40330920
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
ID: 40331304
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40331470
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
ID: 40332812
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
 

Author Comment

by:Pedrov664
ID: 40332858
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 2000 total points
ID: 40333466
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
ID: 40333634
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
ID: 40333722
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
ID: 40333826
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
ID: 40334077
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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.

715 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