•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Is it possible to isolate weekly data from rolling 28-day totals if I don't have the starting "anchor"?
Our take
Are you grappling with extracting specific weekly data from rolling 28-day totals? This common challenge arises when initial reports do not include starting values. If you have several overlapping reports, you might wonder if there's a way to isolate true weekly figures. By estimating the oldest week's value from your first report, you can begin calculations, but understanding how long it takes for this assumption to stabilize into accuracy is key.
Hi everyone, I’m looking for some help with a data extraction problem.
I receive a weekly report for a subscription service I manage, but the system only provides Rolling 28-day totals. For example:
Report 1 (March 1st): Shows total revenue for the last 28 days.
Report 2 (March 8th): Shows total revenue for the last 28 days.
Since these two periods overlap by 21 days, I want to work out exactly what happened in that one specific new week (the 7 days between the reports).
The Mathematical Problem: I know the standard formula to extract a new week is: New Week = (Current 28-day Total - Previous 28-day Total) + Oldest Week (the one that just dropped off)
The Catch: I only started tracking this recently. My very first report was already a 28-day rolling total, so I don't know the value of the "Oldest Week" that needs to be added back in.
My Questions:
If I have 5 or 6 of these rolling reports, is there a point where I can eventually work out a real weekly number (not an average), or will every subsequent week be "artificial" because I never knew the value of that very first week?
If I just assume the four weeks in my first report were equal (Total ÷ 4) and use that to start my calculations, how many weeks/reports does it take until that "guess" is flushed out and my weekly data becomes 100% accurate?
Thanks for any insights!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How to consolidate multiple sheets into one YTD summary report?I have 52 sheets in my excel workbook. It is named in a non-traditional sequence: "Jan Wk1, Jan Wk2, Jan Wk3,...... Dec Wk 4, Dec Wk5", because it's easy for me to print a weekly report with a total for each category. https://preview.redd.it/rq7qb3x8ddyg1.png?width=814&format=png&auto=webp&s=9d101ae17b185451e5140ef1c17f86221cd83aa6 Log 2026 is what my spreadsheet looks like. I only included three weeks as an example. Cell H34 in the highlighted area, on the Jan Wk2 sheet, contains a VBT to retrieve data from the previous sheet. Each day, I also need the total from the previous day in C36:G37. I like my current setup because I can easily convert it into a weekly PDF report without any modification. However, I'm not sure how to create a YTD summary report from 52 sheets. A template of the YTD report is included in Log2026. I tried to use 3D formulas, but I have to manually change sheet names and adjust cell references when crossing columns and rows. not very pragmatic and easily make mistakes. Log 2026: https://drive.google.com/file/d/1IEavZiJiDOv4GS-8c6n8dJqHUnRfT-ft/view?usp=drive_link ***************************** Some people suggest combining a year's worth of data into a single spreadsheet, rather than 52 weekly sheets, and then using Power Query to populate the report, but I am having difficulty generating the weekly report using the preferred template. Preferred Pdf report example: https://drive.google.com/file/d/1uQW7z_XZJ_Nn5sAzcaTkXDObAu8XTi0e/view?usp=drive_link Screenshot of combined data Completed data: https://docs.google.com/spreadsheets/d/1xIJkqTOwjucN2QMT0pK6A6wmSp8U4U8A/edit?usp=drive_link&ouid=102145557861966136866&rtpof=true&sd=true highlights: data collected daily, so every day is different. data for the drivers on the bottom of the spreadsheet changes based on their starting date. So my goal is to create a weekly and yearly report with preferred template (boss's request). Any suggestion is appreciated. submitted by /u/Ok_Solid_9312 [link] [comments]
- Monthly tracking workbook I use to track employee sales metrics; Trying to find a way to make the process less labour intensiveTruly having a hard to describing my issue effectively but hoping someone can help. First time posting here and I'm by no means an expert with excel, so please be kind! I have a monthly workbook where I track each employees revenue and other metrics. Every 2 weeks for payroll, I provide a print out of these numbers, and the payroll sheet pulls data from multiple sheets in the workbook. For example, every workbook has a separate sheet for each day of the month, titled "1" through "31". I have pay period sheets, so I'll use one titled "04.02.26 - 04.15.26". Then I'll have the data for each employee pulled from multiple sheets. For example, I use the formula =SUM('2:15'!E3) to pull the sales data from each day of that period for the specific employee. This works quite well. However, when I create a new month's spreadsheet, I have to manually alter this formula for each employee and for each data point (more than just revenue, at least 6 different data points for 7 employees). Is there a way to automate this? For example, a cell or two where I'm able to enter the date range and all of the formulas update to that date range for the corresponding pages? I'm sorry of this post is confusing. Truly it's confusing even typing it! submitted by /u/Ok_Smile9222 [link] [comments]
- Consolidate data from different worksheets with final information coming from a combination of the worksheetsHoping for some help on a problem I can't wrap my head around. I need to consolidate some information from 12 different tabs (one data pull per month) into one worksheet with some of the data needing to be overridden and some needing to stay. With the most recent pull of the data not necessarily being the information I want showing, I'm not sure how to proceed. I'm trying to find a way to create this and pass it along to someone else to just load data and it automatically puts out the result I'm looking for. For instance, I pull information from my system in January for the entire year (broken into monthly buckets) and all of that is perfect. My second data pull of the year in February isn't so clean. I need to see the numbers from January on any of the January data (because it zeros out in February in my system) but everything related to February through December needs to be from the most recent data pull. Third pull of the year in March, I need to see the numbers from January and February for the January and February data and then need the new info for March through December. Basically, by the time December rolls around, I need data from all 12 sheets. Hopefully this makes sense. Any thoughts on how I can accomplish this in Excel? submitted by /u/megd726 [link] [comments]
- Countifs function comparing the number of occurrences of a value within a date range without having to change the date for every cell?https://preview.redd.it/5wv7p6qq6dvg1.png?width=1523&format=png&auto=webp&s=8678f87180d0cf5c410d2f14fdaefc77ca4dc255 I am a self-taught excel newbie and am trying to help automate the calculation of the stats at my job (meaning I will likely be back with multiple more questions). For this problem, I am trying to get the data to automatically calculate as information is entered in another sheet to determine how often certain values (e.g. "Academic") appear within each month. For some reason, when I put the cell (H2) in place of the date, excel does not register it as a cell and will not complete the calculation. The only solution I have found is to have to individually type out each date range rather than referring to the cell that contains the date range. Further, is there a way to not have to include the year? in this job, we have a separate file for every school year meaning there will be no overlap of month causing issues that way. I am trying to find an easier way to do a year to year comparison without having to include helper columns with the beginning and end dates of each month per year. https://preview.redd.it/55l3ixa49dvg1.png?width=1140&format=png&auto=webp&s=3b222fe0266778a2645f72fed52b59efae4ed850 Thank you! submitted by /u/mentallylost14 [link] [comments]