Data Processing

  • Thread starter Thread starter handan2002
  • Start date Start date
H

handan2002

Hello PPLz,

I have an excel question (unsurprisingly) that hopefully someone can
answer.

This is the example:
Row 1: Total Quantity Available- 9
Column 1 Column 2
_Date_ _Quatity_(QTY)_
1/7 4
8/7 2
2/9 2
13/9 5

How do I write a formula it so that
(a) when the total available quantity runs out, then display the date
on which it runs out (i.e. 2/9 in this case)
(b) it displays when the total available will be in shortage even if
there are leftovers for that day (i.e. 13/9).

I have tried lots of codes and formulae and I think I do need help
now.

Thx in advance! :) :) :)
 
HanDan,

With the number 9 in cell B1, your dates in A2:A5, and quantities in
B2:B5, put this formula into cell C2, and copy it down to C5.

=IF(AND(SUM($B$2:B2)<=$B$1,SUM($B$2:B3)>$B$1),"Runs out " &
EXT( A2,"m/d"),IF(C1<>"","Shortage " & TEXT( A2,"m/d"),""))

HTH,
Bernie
MS Excel MVP
 
Back
Top