[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
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
