# Project Runout date based on projection and actual sales

#### wgwgwgong

##### New Member
I have a question about calculating run-out date for inventory based on BOTH projection and actual YTD sales. Currently, I am able to use index,match+offset function to calculate the run-out date based on projection. by using the following equation:

''' =IFERROR(INDEX(\$E\$2:\$K\$2,MATCH(TRUE,SUBTOTAL(9,OFFSET(E3:K34,,,,COLUMN(\$E\$2:\$K\$2)-COLUMN(E3)+1))>+C3,0)),"None") '''

And that is based on projection only. However, I would love to incorporate the actual sales when the time goes by. That is, having a date in C1, when the B3 updated according to C1, and have my run-out date both looking at the YTD sale and the projection after the date in C1.

I have attached the screenshot of what I explained here. Also the link to the sample sheet: EXCEL-HELP

Hope this helps.

#### Attachments

• excel.PNG
19.7 KB · Views: 0

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Replies
2
Views
311
Replies
1
Views
293
Replies
2
Views
191
Replies
2
Views
390
Replies
21
Views
445