Rolling Forecast - Trying to create summary

DigiDean

New Member
Joined
Oct 11, 2006
Messages
1
[face=Courier New]Can anyone help me solve a problem with a forecasting tool that I'm trying to create? I'll do my best to describe the problem, and hopefully someone can give me some advice on how to solve this.

I receive weekly forecasts from customers on a product, calling for weekly shipments. I'm trying to create a worksheet where I could cut/paste each received forecast over time, and then summarize the final forecast across the bottom of the data. The purpose is have all of the weekly dorecast data reside on one sheet, and be able to easily show forecasting accuracies, last shipment requests, and trends over time.

Along the columns of the sheet, the columns would contain the forecasts received for each weeks shipment. Down the rows, would be each set of forecasts that I receive from the customer. It data table would look something like this.

Product A
Forecasted purchases for weeks
Week1 Week2 Week3 Week4
Fcst1 50 48 73 80
Fcst2 0 48 65 77
Fcst3 0 0 50 50
Fcst4 0 0 0 58

Actual Fcst 50 48 50 58
* Assume that week4 is current date. 0's would also be blank cells.

My question, does Excel have a function that would allow it to calculate only the last data received for a specific week? It would be the last data entered as you move down the rows.

All help is appreciated!!! Thanks much!

Dean
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Dean

Assuming that your Actual Fcst is in row 10, with headings in Row 1 and rows 2:9 able to contain the various estimates, with the week 1 data in column B then in B10 enter the formula

=OFFSET(B1,COUNT(B2:B9),0)

and copy across as required.

HTH

Tony
 

Forum statistics

Threads
1,136,991
Messages
5,678,993
Members
419,797
Latest member
ikethegenius

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top