# Rolling Forecast - Trying to create summary

#### DigiDean

##### New Member
[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
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

Replies
5
Views
105
Replies
3
Views
211
Replies
1
Views
153
Replies
13
Views
550
Replies
2
Views
1K

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.

### Which adblocker are you using?

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

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