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.
''' =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.
