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/
Options
Excel Question
JimBobtheMonkey
Registered User regular
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
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.
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.
0
Posts
Once you have all the relevant data on one sheet you can more easily create analyses
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.
PaD id - 346,240,298
Marvel FF - Lil bill12
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 FunctionTo 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
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.
@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
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
1: get the date
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.
What I am thinking though is your main page where you have the yearly average. I would place into B1 . In A2 through A36 I would put in 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 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
PaD id - 346,240,298
Marvel FF - Lil bill12
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).