• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cashflow help - Average

S

Spicegirl

New Member
Hi

Not sure if I can do this without a macro...

I have a cashflow spreadsheet which has 2 tabs, one for previous actuals and one for the forecast to come. I would like the forecast tab to take the average sales from the previous 13 weeks on the actuals tab and report it as forecast sales on the forecast tab. However, obviously the start and stop point of the 13 weeks moves on one column at the end of each week.

Can I (and if so how) use some sort of dynamic formula within the forecast sheet to pick up the previous 13 weeks and have it move automatically (either by changing a reference date or cell somewhere).

Thanks in advance
 
should be able to - depending on the layout - can you provide a sample of how you data is laid out

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.
 
Thanks, I have added a layout with dummy figures for the actuals tab, the forecast tab is exactly the same layout but I want the sales in the forecast tab to take an average of the most recent 13 weeks of data in the sample sheet. At the end of each week the next column in the actuals tab gets populated, so this Friday I will be populating column AB, next week AC and so on, so I need the average to move on one every time I populate a new column. The result will be picked up by the forecast tab and reported as forecast sales for the following 39 weeks
 

Attachments

  • Cashflow average sale.xlsx
    11 KB · Views: 3
Thanks for trying. Unfortunately the range doesn't move on each week (I was after a rolling 13 weeks if you see what I mean), and I was thinking more of an average. I might try a different tack and see if I can get a formula to tell me how many weeks away the values are and then do a bit of a sumif/sumproduct on the 1-13 weeks (if that makes sense). I definitely need to get under the skin of that forecasting tool though as that could be an amazing help
 
You must log in or register to reply here.
Back
Top