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
__________________________

Hi

With your dates in cells D2:P2 and your week ending stock values in D4:P4,
this array formula will return the earliest date of a negative stock value.
{=IF(MIN(D4:P4)0,"N/A",MIN(IF(D4:P4<0,D1:P1)))}

--
XL2002
Regards

William



"Richard Buttrey" wrote in
message ...
| 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
| __________________________

William,

Many thanks indeed for this solution, and indeed for the extremely
swift response. This ng must be one of the best on the web. (I think
there was a typo with the "D1:P1" bit in the formula, I guess you
meant D2:P2 ).

If I might be so bold as to pose a supplementary Q., any suggestions
as to how I might refine this to work out the exact date a stock goes
negative, from the stock issues/returns table?
I guess one solutions is to sort that table by date, and have a new
running cumulative balance column and then use a variation of the
formula you've suggested.

Kind regards,





On Wed, 11 Aug 2004 11:24:58 +0100, "William"
wrote:

RBHi
RB
RBWith your dates in cells D2:P2 and your week ending stock values in
D4:P4,
RBthis array formula will return the earliest date of a negative
stock value.
RB{=IF(MIN(D4:P4)0,"N/A",MIN(IF(D4:P4<0,D1:P1)))}

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Richard

Given that codes will be repeated in your table as you move down column B, I
do not think what you want is what you asked for. I may be wrong :-)

Instead, here is a suggestion.....

I have 5 columns and in cells A1 to E1, I have the following column
headings...
Date
Code
Issues
Returns
Net

I named the range from A2 downwards "da", the range B2 downwards "cd", the
range C2 downwards "is", the range D2 downwards "re" and the range E2
downwards "ne".

Assuming yout Returns column (columnd D) has negative numbers, enter the
following formula in cell E2 and drag it down as far as necessary.
=SUMPRODUCT((da<=A2)*(cd=B2)*(is+re))

In cells H2 downwards, I placed a unique list of all codes.
Enter this "array" formula in cell I2 and then drag down alongside the code
names in column H.
{=IF(MIN(IF(cd=H2,IF(ne<0,da)))=0,"",MIN(IF(cd=H2, IF(ne<0,da))))}

--
XL2002
Regards

William



"Richard Buttrey" wrote in
message ...
| William,
|
| Many thanks indeed for this solution, and indeed for the extremely
| swift response. This ng must be one of the best on the web. (I think
| there was a typo with the "D1:P1" bit in the formula, I guess you
| meant D2:P2 ).
|
| If I might be so bold as to pose a supplementary Q., any suggestions
| as to how I might refine this to work out the exact date a stock goes
| negative, from the stock issues/returns table?
| I guess one solutions is to sort that table by date, and have a new
| running cumulative balance column and then use a variation of the
| formula you've suggested.
|
| Kind regards,
|
|
|
|
|
| On Wed, 11 Aug 2004 11:24:58 +0100, "William"
| wrote:
|
| RBHi
| RB
| RBWith your dates in cells D2:P2 and your week ending stock values in
| D4:P4,
| RBthis array formula will return the earliest date of a negative
| stock value.
| RB{=IF(MIN(D4:P4)0,"N/A",MIN(IF(D4:P4<0,D1:P1)))}
|
| __
| Richard Buttrey
| Grappenhall, Cheshire, UK
| __________________________

Richard

Given that codes will be repeated in your table as you move down column B, I
do not think what you want is what you asked for. I may be wrong :-)

Instead, here is a suggestion.....

I have 5 columns and in cells A1 to E1, I have the following column
headings...
Date
Code
Issues
Returns
Net

I named the range from A2 downwards "da", the range B2 downwards "cd", the
range C2 downwards "is", the range D2 downwards "re" and the range E2
downwards "ne".

Assuming yout Returns column (columnd D) has negative numbers, enter the
following formula in cell E2 and drag it down as far as necessary.
=SUMPRODUCT((da<=A2)*(cd=B2)*(is+re))

In cells H2 downwards, I placed a unique list of all codes.
Enter this "array" formula in cell I2 and then drag down alongside the code
names in column H.
{=IF(MIN(IF(cd=H2,IF(ne<0,da)))=0,"",MIN(IF(cd=H2, IF(ne<0,da))))}


--
XL2002
Regards

William



"Richard Buttrey" wrote in
message ...
| William,
|
| Many thanks indeed for this solution, and indeed for the extremely
| swift response. This ng must be one of the best on the web. (I think
| there was a typo with the "D1:P1" bit in the formula, I guess you
| meant D2:P2 ).
|
| If I might be so bold as to pose a supplementary Q., any suggestions
| as to how I might refine this to work out the exact date a stock goes
| negative, from the stock issues/returns table?
| I guess one solutions is to sort that table by date, and have a new
| running cumulative balance column and then use a variation of the
| formula you've suggested.
|
| Kind regards,
|
|
|
|
|
| On Wed, 11 Aug 2004 11:24:58 +0100, "William"
| wrote:
|
| RBHi
| RB
| RBWith your dates in cells D2:P2 and your week ending stock values in
| D4:P4,
| RBthis array formula will return the earliest date of a negative
| stock value.
| RB{=IF(MIN(D4:P4)0,"N/A",MIN(IF(D4:P4<0,D1:P1)))}
|
| __
| Richard Buttrey
| Grappenhall, Cheshire, UK
| __________________________

William,

This is extremely good. I've learned a lot more today. Thanks for all
your help.

Just as a matter of interest, what's the essential difference between
a sumproduct formula, and an =sum {} array formula, which seem to do
much the same sorts of things?

Kind regards,

Richard

On Wed, 11 Aug 2004 13:32:30 +0100, "William"
wrote:

RBRichard
RB
RBGiven that codes will be repeated in your table as you move down
column B, I
RBdo not think what you want is what you asked for. I may be wrong
:-)
RB
RBInstead, here is a suggestion.....
RB
RBI have 5 columns and in cells A1 to E1, I have the following column
RBheadings...
RBDate
RBCode
RBIssues
RBReturns
RBNet
RB
RBI named the range from A2 downwards "da", the range B2 downwards
"cd", the
RBrange C2 downwards "is", the range D2 downwards "re" and the range
E2
RBdownwards "ne".
RB
RBAssuming yout Returns column (columnd D) has negative numbers,
enter the
RBfollowing formula in cell E2 and drag it down as far as necessary.
RB=SUMPRODUCT((da<=A2)*(cd=B2)*(is+re))
RB
RBIn cells H2 downwards, I placed a unique list of all codes.
RBEnter this "array" formula in cell I2 and then drag down alongside
the code
RBnames in column H.
RB{=IF(MIN(IF(cd=H2,IF(ne<0,da)))=0,"",MIN(IF(cd= H2,IF(ne<0,da))))}

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Hi Richard

Glad to have helped.

I may be wrong but AFAIK the SumProduct formula and SUM(Array) formulae are
usually fairly similar. By habit I tend to use the SUM(Array) formula when
there are "Min" or "Max" requirements - I haven't tested whether you can use
the SUMPRODUCT formula in this context but I do not think you can.

Regards from London, UK.

--
XL2002
Regards

William



"Richard Buttrey" wrote in
message ...
| William,
|
| This is extremely good. I've learned a lot more today. Thanks for all
| your help.
|
| Just as a matter of interest, what's the essential difference between
| a sumproduct formula, and an =sum {} array formula, which seem to do
| much the same sorts of things?
|
| Kind regards,
|
| Richard
|
| On Wed, 11 Aug 2004 13:32:30 +0100, "William"
| wrote:
|
| RBRichard
| RB
| RBGiven that codes will be repeated in your table as you move down
| column B, I
| RBdo not think what you want is what you asked for. I may be wrong
| :-)
| RB
| RBInstead, here is a suggestion.....
| RB
| RBI have 5 columns and in cells A1 to E1, I have the following column
| RBheadings...
| RBDate
| RBCode
| RBIssues
| RBReturns
| RBNet
| RB
| RBI named the range from A2 downwards "da", the range B2 downwards
| "cd", the
| RBrange C2 downwards "is", the range D2 downwards "re" and the range
| E2
| RBdownwards "ne".
| RB
| RBAssuming yout Returns column (columnd D) has negative numbers,
| enter the
| RBfollowing formula in cell E2 and drag it down as far as necessary.
| RB=SUMPRODUCT((da<=A2)*(cd=B2)*(is+re))
| RB
| RBIn cells H2 downwards, I placed a unique list of all codes.
| RBEnter this "array" formula in cell I2 and then drag down alongside
| the code
| RBnames in column H.
| RB{=IF(MIN(IF(cd=H2,IF(ne<0,da)))=0,"",MIN(IF(cd= H2,IF(ne<0,da))))}
|
| __
| Richard Buttrey
| Grappenhall, Cheshire, UK
| __________________________