Hi all,
I am having a problem with trying to get this working correctly.
Previously I found a formula which helps to calculate the number of weeks of inventory I have for any week depending on the forecasted demand quantities.
I have included the sample data below:
[TABLE="width: 662"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Forecast[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Inventory[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Weeks cover[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Data range is from A2:H6 and the formula I used is as follows, starting in cell B6:
=IF(B4 < SUM(C2:$H$2),SUMPRODUCT(--(B4>=SUBTOTAL(9,OFFSET(C2:$H$2,,,,COLUMN(C2:$H$2)-COLUMN(C2)+1))))+LOOKUP(0,SUBTOTAL(9,OFFSET(C2:$H$2,,,,COLUMN(C2:$H$2)-COLUMN(C2)+1))-C2:$H$2-B4,(B4-(SUBTOTAL(9,OFFSET(C2:$H$2,,,,COLUMN(C2:$H$2)-COLUMN(C2)+1))-C2:$H$2))/C2:$H$2),IF(B4=SUM(C2:$H$2),COLUMNS(C2:$H$2),"Inventory Exceeds Forecast"))
The formula works well and I am able to get what I want, however, I need to be able to show row 6 in another summary sheet according to part numbers as this sample data is only for one part number. I have about 300 to list.
Also, it has to be repeatable as I get a new set of data every week and I would like to just copy/transfer the data over to another workbook with the summary sheet.
Is there anyway to do that? Does it need a macro or just formulas will do?
Thanks in advance for any assistance!
I am having a problem with trying to get this working correctly.
Previously I found a formula which helps to calculate the number of weeks of inventory I have for any week depending on the forecasted demand quantities.
I have included the sample data below:
[TABLE="width: 662"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Forecast[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Inventory[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Weeks cover[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Data range is from A2:H6 and the formula I used is as follows, starting in cell B6:
=IF(B4 < SUM(C2:$H$2),SUMPRODUCT(--(B4>=SUBTOTAL(9,OFFSET(C2:$H$2,,,,COLUMN(C2:$H$2)-COLUMN(C2)+1))))+LOOKUP(0,SUBTOTAL(9,OFFSET(C2:$H$2,,,,COLUMN(C2:$H$2)-COLUMN(C2)+1))-C2:$H$2-B4,(B4-(SUBTOTAL(9,OFFSET(C2:$H$2,,,,COLUMN(C2:$H$2)-COLUMN(C2)+1))-C2:$H$2))/C2:$H$2),IF(B4=SUM(C2:$H$2),COLUMNS(C2:$H$2),"Inventory Exceeds Forecast"))
The formula works well and I am able to get what I want, however, I need to be able to show row 6 in another summary sheet according to part numbers as this sample data is only for one part number. I have about 300 to list.
Also, it has to be repeatable as I get a new set of data every week and I would like to just copy/transfer the data over to another workbook with the summary sheet.
Is there anyway to do that? Does it need a macro or just formulas will do?
Thanks in advance for any assistance!
Last edited: