Hi all,

Hope you're well!

I'm working on a sheet, but think there might be a better formula to solve my issue.
The formula I'm working with is in cell BF4 - BF7 and same for BI, BL etc.
It is suggesting how much stock to bring in based on the MOQ and multiply of it as well as taking into consideration the product category.
In column K
A – run down to below 2 over 1 weeks and order top up by 5 weeks of stock
B – Run down to below 1 week and order top up by 5 weeks of stock
Nothing - run down to 2 weeks and order top up to 5 weeks of stock
D – Discontinued - always return 0

I'm trying to figure out how to amend the cat's so the proposed orders are
A - run down to 1 week of cover and top up by 3 weeks - so if the forecast for next few weeks is much higher than for the week this is working with the formula should cover the negative in 4 weeks time.
B - run down to 2 weeks of cover and top up by 3 weeks
Empty cell - run down to 3 weeks of cover and top up by 2 weeks

=IF($I4=999999999,0, IF(BE4>IF(OR($K4="D"), 0, IF(OR($K4="B"), IF(ISFORMULA(BG4)=TRUE, BG4, $P4), IF(OR($K4="A"), IF(ISFORMULA(BG4)=TRUE, BG4, $P4)+IF(ISFORMULA(GI4)=TRUE, GI4, $P4), IF(ISFORMULA(BG4)=TRUE, BG4, $P4)+IF(ISFORMULA(GI4)=TRUE, GI4, $P4)+IF(ISFORMULA(GR4)=TRUE, GR4, $P4)))),0, IF(IF(ISFORMULA(BG4)=TRUE, BG4, $P4)+IF(ISFORMULA(GI4)=TRUE, GI4, $P4)+IF(ISFORMULA(GR4)=TRUE, GR4, $P4)-BE4-IF(OR($K4="A"),IF(ISFORMULA(HA4)=TRUE, HA4, $P4),0)-IF(OR($K4="B")=TRUE,IF(ISFORMULA(HA4)=TRUE, HA4, $P4),0)-IF(OR($K4="D"), IF(ISFORMULA(GX4)=TRUE, GX4, $P4)+IF(ISFORMULA(GR4)=TRUE, GR4, $P4)+IF(ISFORMULA(GI4)=TRUE, GI4, $P4)+IF(ISFORMULA(BG4)=TRUE, BG4, $P4)+IF(ISFORMULA(HA4)=TRUE, HA4, $P4)-BE4, 0)<$I4*0.1, 0, IF(IF(ISFORMULA(BG4)=TRUE, BG4, $P4)+IF(ISFORMULA(GI4)=TRUE, GI4, $P4)+IF(ISFORMULA(GR4)=TRUE, GR4, $P4)+IF(ISFORMULA(GU4)=TRUE, GU4, $P4)+IF(ISFORMULA(GX4)=TRUE, GX4, $P4)+IF(ISFORMULA(HA4)=TRUE, HA4, $P4)-BE4-IF(OR($K4="A"),IF(ISFORMULA(HA4)=TRUE, HA4, $P4),0)-IF(OR($K4="B")=TRUE,IF(ISFORMULA(GX4)=TRUE, GX4, $P4)+IF(ISFORMULA(HA4)=TRUE, HA4, $P4),0)-IF(OR($K4="D"), IF(ISFORMULA(GX4)=TRUE, GX4, $P4)+IF(ISFORMULA(GU4)=TRUE, GU4, $P4)+IF(ISFORMULA(GR4)=TRUE, GR4, $P4)+IF(ISFORMULA(GI4)=TRUE, GI4, $P4)+IF(ISFORMULA(BG4)=TRUE, BG4, $P4)+IF(ISFORMULA(HA4)=TRUE, HA4, $P4)-BE4, 0)<$I4, $I4, CEILING(IF(ISFORMULA(BG4)=TRUE, BG4, $P4)+IF(ISFORMULA(GI4)=TRUE, GI4, $P4)+IF(ISFORMULA(GR4)=TRUE, GR4, $P4)+IF(ISFORMULA(GU4)=TRUE, GU4, $P4)+IF(ISFORMULA(GX4)=TRUE, GX4, $P4)+IF(ISFORMULA(HA4)=TRUE, HA4, $P4)-BE4-IF(OR($K4="A"),IF(ISFORMULA(HA4)=TRUE, HA4, $P4),0)-IF(OR($K4="B")=TRUE,IF(ISFORMULA(GX4)=TRUE, GX4, $P4)+IF(ISFORMULA(HA4)=TRUE, HA4, $P4),0)-IF(OR($K4="D"), IF(ISFORMULA(GX4)=TRUE, GX4, $P4)+IF(ISFORMULA(GU4)=TRUE, GU4, $P4)+IF(ISFORMULA(GR4)=TRUE, GR4, $P4)+IF(ISFORMULA(GI4)=TRUE, GI4, $P4)+IF(ISFORMULA(BG4)=TRUE, BG4, $P4)+IF(ISFORMULA(HA4)=TRUE, HA4, $P4), 0), $J4)))))


Screenshot 2024-07-11 103403.png


The file is too big for the forum, so ready for download here:
https://we.tl/t-ZL1ESQTpA0


TIA
Millie