Calculations in Excel

[handan2002]

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

 

[Max]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Max]

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
--------------------------------------------------