Hi,

Can anyone offer an elegant Excel formule to identify the following. I
could probably do it with some brute force VBA code examining every
cell, but I expect that an Excel formula would be preferable.

I have a data table of forecast Stock Issue and Return items which
looks like the following (and yes this is an English format date in
case you're wondering).

A B C D
Date Code Issues Returns
12/8/04 ABC 100
13/8/04 ABC 50
14/8/04 DEF 120
..
.. etc.

I have another table of forecast Stock Balance items for all the 30
or so stock items I''m interested in as follows.
(The w/e stock positions are =SUM {} array formualae which reference
the Issues and Returns table.)

A B C D E
Code Current Week Ending Stock
Stock 14/8/04 21/8/04 28/8/04 ....for next 13 weeks
ABC 5000 4000 2000 -1000
DEF 6000 3000 -400 2000
..
.. etc.

I'd like to introduce another column to the Stock Balance Table which
calculates for each stock code item, the date when it first goes
negative. I was initially thinking of a formula which would identify
the week ending date, e.g. w/e 28/8/04 for code ABC and w/e 21/8/04
for code DEF.

Better still would be a formula that identified the actual date a
stock went negative, i.e. a date which may be one of the 6 other days
between two w/e dates. This would clearly need to reference the stock
issues and returns table and the current stock position in column B of
the Stock Balance table.

Any suggestions would be gratefully received - even a VBA macro if
that's felt preferable.

Volumes of data for info.
The max number of w/e stock weeks is 13, the max number of stock
issues/returns is 250 & the number of Stock Items is 30


Usual TIA

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________