Project Runout date based on projection and actual sales

wgwgwgong

New Member
Joined
Nov 18, 2020
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
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
    excel.PNG
    19.7 KB · Views: 0

Some videos you may like

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

Watch MrExcel Video

Forum statistics

Threads
1,119,063
Messages
5,575,892
Members
412,689
Latest member
nhsmedic
Top