As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

Excel Question

JimBobtheMonkeyJimBobtheMonkey Registered User regular
I could use some help with a formula for a workbook I use at work. The workbook has a sheet for every week with a few summary sheets in between for each month.
I have one sheet that shows the average for the whole year that looks like this
=AVERAGE('January 1st - January 5th:January 27th - February 2'!C4,'February 3rd - February 9th :February 24th - March 2rd'!C4,'March 3th - March 9th:March 31st - April 6th'!C4,'April 7th - April 13th:April 28th - May4th'!C4,'May 5th- May 11th:May 26th - June 1st'!C4,'June 2nd - June 8th :June 30th - July 6th'!C4,'July 7th - July 13th:July 28th - August 3th'!C4,'August 4th - August 10th:August 25th - August 31st'!C4,'September 1st - September 7th:September 29th - October 5th'!C4,'October 6th - October 12th:October 27th - November 2nd'!C4,'November 3rd - November 9th:November 24th - November 30th'!C4,'December 1st - December 7th:December 29th - January 4th'!C4)

How would I go about doing a 5 week average for that same cell with the most recent data? I'm thinking it would be something using OFFSET and COUNTA but I'm kind of just lost and throwing together things that come up on google. Also if there is a simpler way do the above average I'm all ears.

Posts

  • CauldCauld Registered User regular
    I would look into using the 'indirect' function to let you easily pull all of the relevant data onto one sheet. Your summary sheet could simply have columns for other sheet names and reference titles (Like "Total Sales") and pull a referenced number from each sheet via a lookup function.

    Once you have all the relevant data on one sheet you can more easily create analyses

    JimBobtheMonkey
  • Kick_04Kick_04 Registered User regular
    A sheet for every week, sounds... terrible.

    I am not all that familiar with it and I might be wrong... What I was thinking you could do was an array average, the refrence inside the array would be todays date & todays date -35 days. If it is updated every week, instead of every day, could do todays date -7 & todays date -42 for a rolling 5 week average.

    PSN id - kickyoass1
    PaD id - 346,240,298
    Marvel FF - Lil bill12
    schussJimBobtheMonkey
  • MrTLiciousMrTLicious Registered User regular
    If you know the pattern (and preferably it doesn't change) of where the last 5 weeks are in the workbook creation order, you can do it without too much finagling.

    First, you'll need a function that returns the name of the sheet by the number. This is pretty straightforward, just put the following code into the VBA section under "Module1"
    Function sheet(n As Integer) As String
         sheet = Sheets(n).Name
    End Function
    
    Function numSheets() As Integer
         numSheets = ThisWorkbook.Sheets.Count
    End Function
    

    To get to the VBA, you'll need to mess with your settings. Let me know if you need help with that and I'll happy to give instructions.

    So what did that do? It basically said, if I tell Excel "=sheet(6)", it will return a string that gives me the name of the 6th workbook (note that this is the 6th one in creation order, not in the display order, so rearranging won't affect the result).

    Similarly, if I put in "=numSheets()" it will return the total number of worksheets. We'll need this because we're going to count from the end.

    So now you want the final average. As Cauld suggested, you can do an indirect. So the average of the last 5 would be something like
    =AVERAGE(INDIRECT(sheet(numSheets())&"!C4"),INDIRECT(sheet(numSheets()-1)&"!C4"),INDIRECT(sheet(numSheets()-2)&"!C4"),INDIRECT(sheet(numSheets()-3)&"!C4"),INDIRECT(sheet(numSheets()-4)&"!C4"))
    

    That will give the average of the item in C4 for the last 5 sheets. If there are sheets inbetween (again, in creation order), then you can change the indexing to whatever it should be. Each indirect is saying "replace this function with the text being evaluated," so it's giving you the sheet name, followed by the relevant location.

    Feel free to let me know if anything is unclear.

    JimBobtheMonkey
  • JimBobtheMonkeyJimBobtheMonkey Registered User regular
    Thanks everyone for the suggestions, I appreciate it.

    @MrTLicious‌ all of the sheets are already created, we update them once a day (there is a section for each day of the week on each sheet). Does that make a difference? I have a vague idea of the creation order but don't know for sure since I wasn't the one who made the workbook.

    Once I have the formula to calculate one cell, I'll have to copy it like 6x for every hour of every day. This

  • MrTLiciousMrTLicious Registered User regular
    I think I'm misunderstanding how it's laid out. I thought every week you would have to make a new sheet to put in the last week's data.

    JimBobtheMonkey
  • useless4useless4 Registered User regular
    I would create a table with a date field as needed and pivot that thing. Sounds like a nightmare the way I imagine it's laid out

    JimBobtheMonkey
  • JimBobtheMonkeyJimBobtheMonkey Registered User regular
    edited July 2014
    No, I'm sorry I wasn't more clear. All of the sheets are already created, we just fill in the data each week. All of the sheets are laid out the same way. The first sheet is named 'January 1st - January 5th' and the last is 'December 29th - January 4th' which you can see in the formula in the op. There is more to it, but for simplicity sake I just need the formula for one cell from each page. The cells are empty until we fill them in that week.

    Full story: this is for a customer service call center. We record the number of calls and orders we take each hour (24/7). The 5 week average is used to help in scheduling so we have the right number of people on for the amount of calls we'll get. Previously we would figure it out manually.

    -edit- it is a mess. You don't want to see the workbook I have to use to create the schedule

    JimBobtheMonkey on
  • MrTLiciousMrTLicious Registered User regular
    Ah okay. You can get the text of the most recent Sunday in this two-step procedure.

    1: get the date
    =TODAY()-WEEKDAY(TODAY(),2)
    

    2: Make it look the way you want it
    =TEXT(A1,"mmmm d")&LOOKUP(DAY(A1),{1,2,3,4,21,22,23,24,31;"st","nd","rd","th","st","nd","rd","th","st"})
    

    Where the text function gives the basic date (e.g. June 29), and the lookup puts on the appropriate st, th, etc.

    If you subtract 6, you'll get the monday before that. So, you can mess with this and the indirect to get the correct sheet name. I'm off to a meeting but I'll put full code in when I get back, just thought I'd drop this off first.

    JimBobtheMonkey
  • Kick_04Kick_04 Registered User regular
    Well I am not sure without seeing the work book....

    What I am thinking though is your main page where you have the yearly average. I would place into B1
    =TODAY()
    
    . In A2 through A36 I would put in
    =HLOOKUP(B1-1,??,**,1)
    
    for each cell going down change it from -1 to -2 to -3 etc.

    ?? = your whole workbook with dates and data
    ** = how many rows down the total number is from the date

    After this you can do
    =AVERAGE(A2:A36)
    
    above it title it as "rolling 5 week average (F9 to refresh date)"
    I would than hide row A.

    If the dates are going all down Row A on each sheet, you will than have to switch to vlookup

    PSN id - kickyoass1
    PaD id - 346,240,298
    Marvel FF - Lil bill12
    JimBobtheMonkey
  • Dis'Dis' Registered User regular
    edited July 2014
    Cauld wrote: »
    I would look into using the 'indirect' function to let you easily pull all of the relevant data onto one sheet. Your summary sheet could simply have columns for other sheet names and reference titles (Like "Total Sales") and pull a referenced number from each sheet via a lookup function.

    Once you have all the relevant data on one sheet you can more easily create analyses

    yeah i'd agree with this - mrTLicious has a good solution but is assuming the sheet names are perfectly formed to the pattern...which isn't often true with 52 user entered sheet names ;).

    To expand on how you could do this with Cauld's suggestion:

    1. Column A row 1 would be the name of a sheet
    2. Column B would have the formula =INDIRECT(ADDRESS(4,3,,,A1)) which tell it to get the value for the 4th row of the third column in the sheet named in A1
    3. Repeat for all sheets so you have 52 rows in columns A and B
    4. For each week in column C the formula =AVERAGE(INDIRECT(ADDRESS(MAX(ROW()-4,1),2,,,)&":"&ADDRESS(ROW(),2,,,))) will get the the average for that row's week and the last four weeks before it (except for the first few weeks of the year).

    Dis' on
    JimBobtheMonkey
  • JimBobtheMonkeyJimBobtheMonkey Registered User regular
    I actually understand some of those I think. Thanks everyone, I think I should be able to make one of those work.

Sign In or Register to comment.