find when QTY becomes <= sum of other cells (1 Viewer)

[umair434]

Hi guys,

I am attaching a spreadsheet here - what I want is to list the products in order of when the QTY_ON_HAND is depleted.

so for example:

for product number 0090069, the QTY_ON_HAND = 442

SumofUNITS1 = 125
SumofUNITS2 = 75.3
SumofUNITS3 = 101.1
SumofUNITS4 = 134.5
SumofUNITS5 = 4400.5


total after 4 weeks = SumofUNITS1+SumofUNITS2+SumofUNITS3+SumofUNITS4 = 436.1, which is still < QTY_ON_HAND (442)

so,
total after 5 weeks = 436.1+4400.5, which is > QTY_ON_HAND (442) -

hence this product's inventory will be over by 5 weeks!

I want this information for every product.




hope i'm making some sense

Thanks!!!

 

[NBVC]

Try this formula in AB2:

=INDEX($C$1:$AA$1,MATCH(TRUE,SUBTOTAL(9,(INDIRECT(TRANSPOSE("c" & ROW()&":"&ADDRESS(ROW(C2),ROW(INDIRECT("1:"&COUNT(C2:AA2)))+2)))))>B2,0))

confirmed with CTRL+SHIFT+ENTER not just ENTER so that you get { } brackets appear around the formula and then copy down.

 

[umair434]

=)))))[B][I]>=[/I][/B]B2,0))

Thanks alot NBVC!! just had to include an "=" sign in the code. so if QTY_ON_HAND = SUMOFUNITS1, then it should give SUMOFUNITS1 as the answer!


thanks again!! much appreciated

 

[NBVC]

You are welcome...

Yes, I should have included that...