Hi every1!

If u can, plz help me with this excel question (its been bothering me
for ageas already!):

Total Available: 25
Runout date: ???
A B
Date Quantity Required
1/6/2003 6
3/7/2003 14
4/8/2003 9
8/11/2003 6

How do i make it show the date that the food runs out on (4/8/2003 in
this case) and the day before it will run out (3/7/2003 in this case)?

If u can help, then plz reply!!

Thx in advance


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

One possible way:

Assume your data below is in cols A and B, row 2 down

Date................QtyReqd
06-Jan-03.......6
07-Mar-03......14
08-Apr-03.......9
11-Aug-03......6

a. Set-up a cumulative total in col C
for Qty Reqd (col B)

Put in C2: =B2

Put in C3: =B3+C2
Copy C3 down col C

b. Now, suppose you have the "Total Available" in col D
with value in D2: 25

Format E2 and F2 as dd-mmm-yy

Put in F2: =OFFSET($A$1,MATCH(D2,C:C,1)-1,0,1,1)
(F2 returns the date before the run-out date, viz 07-Mar-03)

Put in E2: =OFFSET($A$1,MATCH(F2,A:A,1),0,1,1)
(E2 returns the run-out date, viz. 08-Apr-03)

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email
-------------------------------------------
"handan2002" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Hi every1!
>
> If u can, plz help me with this excel question (its been bothering me
> for ageas already!):
>
> Total Available: 25
> Runout date: ???
> A B
> Date Quantity Required
> 1/6/2003 6
> 3/7/2003 14
> 4/8/2003 9
> 8/11/2003 6
>
> How do i make it show the date that the food runs out on (4/8/2003 in
> this case) and the day before it will run out (3/7/2003 in this case)?
>
> If u can help, then plz reply!!
>
> Thx in advance
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>

On Tue, 11 Nov 2003 03:16:24 -0500, handan2002
<(E-Mail Removed)> wrote:

>
>Hi every1!
>
>If u can, plz help me with this excel question (its been bothering me
>for ageas already!):
>
>Total Available: 25
>Runout date: ???
>A B
>Date Quantity Required
>1/6/2003 6
>3/7/2003 14
>4/8/2003 9
>8/11/2003 6
>
>How do i make it show the date that the food runs out on (4/8/2003 in
>this case) and the day before it will run out (3/7/2003 in this case)?
>
>If u can help, then plz reply!!
>
>Thx in advance
>
>

Assumptions:

Total Available = TotAvail is in C1

Data is in A4:Cn with a title row 4

Date | Quantity | Required

In C5 --> =B5
In C6 --> =C5+B6

Copy/Drag down to give a running total of "Required"

Run out date:

=INDEX(Date,MATCH(TotAvail,Required)+1-NOT(ISNA(MATCH(TotAvail,Required,0))))

Date before run out date:

=INDEX(Date,MATCH(TotAvail,Required)-NOT(ISNA(MATCH(TotAvail,Required,0))))


--ron

On Tue, 11 Nov 2003 18:44:46 +0800, "Max" <(E-Mail Removed)> wrote:


>
>Put in F2: =OFFSET($A$1,MATCH(D2,C:C,1)-1,0,1,1)
>(F2 returns the date before the run-out date, viz 07-Mar-03)
>
>Put in E2: =OFFSET($A$1,MATCH(F2,A:A,1),0,1,1)
>(E2 returns the run-out date, viz. 08-Apr-03)

Depending on what the OP wants when the Required total is exactly equal to the
Total Available, your formula may or may not give the desired answer.


--ron

Good point, Ron. Thanks.

For the situation that Total Available = Cumulative Total
and Run-out date is to be construed as the date this occurs
rather than the following date,

then the formula in F2 should be amended to :
=IF(ISNA(MATCH(D2,C:C,0)),OFFSET($A$1,MATCH(D2,C:C,1)-1,0,1,1),OFFSET($A$1,M
ATCH(D2,C:C,0)-2,0,1,1))

No change for the formula in E2
--
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com for email
--------------------------------------------------
"Ron Rosenfeld" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Tue, 11 Nov 2003 18:44:46 +0800, "Max" <(E-Mail Removed)> wrote:
>
>
> >
> >Put in F2: =OFFSET($A$1,MATCH(D2,C:C,1)-1,0,1,1)
> >(F2 returns the date before the run-out date, viz 07-Mar-03)
> >
> >Put in E2: =OFFSET($A$1,MATCH(F2,A:A,1),0,1,1)
> >(E2 returns the run-out date, viz. 08-Apr-03)
>
> Depending on what the OP wants when the Required total is exactly equal to
the
> Total Available, your formula may or may not give the desired answer.
>
>
> --ron