I have a spreadsheet that keeps up with my estimated sales and inbound shipments. It use that data to give me an idea of week ending inventory levels to see if I'm going to have any stock outs.
Right now I have a "run out date" that shows the first date that a product should go out of stock. The formula I use to calculate this is in cell B2.
What I'd like to add is a "Back in stock date." This will return the first date that we have inventory AFTER a stockout. Cells C3:C5 have the expected output based on the dummy data below. In this example, Product A would have no dates for neither Run Out or Back In Stock because we had inventory the whole time.
Any ideas on how (and if) I can calculate this? I can't figure out how to tell Excel to check to see if prior cell was negative and current cell is positive then return the date in the header.
Appreciate the help!
Right now I have a "run out date" that shows the first date that a product should go out of stock. The formula I use to calculate this is in cell B2.
What I'd like to add is a "Back in stock date." This will return the first date that we have inventory AFTER a stockout. Cells C3:C5 have the expected output based on the dummy data below. In this example, Product A would have no dates for neither Run Out or Back In Stock because we had inventory the whole time.
Any ideas on how (and if) I can calculate this? I can't figure out how to tell Excel to check to see if prior cell was negative and current cell is positive then return the date in the header.
Appreciate the help!
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Product | Run Out Date | Back In stock date | 5/10 | 5/17 | 5/24 | 5/31 | 6/7 | 6/14 | 6/21 |
| 2 | A | IFERROR(INDEX($CV$2:$DI$2,MATCH(TRUE,$CV4:$DI4<0,0)),"") | 500 | 450 | 423 | 535 | 244 | 24 | 40 | |
| 3 | B | 5/24 | 6/7 | 300 | 5 | -100 | -256 | 35 | 50 | -20 |
| 4 | C | 5/31 | 6/21 | 100 | 130 | 150 | -49 | -33 | -245 | 53 |
| 5 | D | 6/7 | 6/21 | 10 | 34 | 543 | 35 | -224 | -24 | 75 |
