How to identify which cell in a row first becomes negative.

[Richard Buttrey]

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]

Hi

With your dates in cells D22 and your week ending stock values in D44,
this array formula will return the earliest date of a negative stock value.
{=IF(MIN(D44)>0,"N/A",MIN(IF(D44<0,D11)))}

--
XL2002
Regards

William

(e-mail address removed)

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

 

[Richard Buttrey]

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 "D11" bit in the formula, I guess you
meant D22 ).

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,





RB>Hi
RB>
RB>With your dates in cells D22 and your week ending stock values in
D44,
RB>this array formula will return the earliest date of a negative
stock value.
RB>{=IF(MIN(D44)>0,"N/A",MIN(IF(D44<0,D11)))}

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[William]

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

(e-mail address removed)

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 "D11" bit in the formula, I guess you
| meant D22 ).
|
| 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" <[email protected]>
| wrote:
|
| RB>Hi
| RB>
| RB>With your dates in cells D22 and your week ending stock values in
| D44,
| RB>this array formula will return the earliest date of a negative
| stock value.
| RB>{=IF(MIN(D44)>0,"N/A",MIN(IF(D44<0,D11)))}
|
| __
| Richard Buttrey
| Grappenhall, Cheshire, UK
| __________________________

 

[William]

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

(e-mail address removed)

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 "D11" bit in the formula, I guess you
| meant D22 ).
|
| 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" <[email protected]>
| wrote:
|
| RB>Hi
| RB>
| RB>With your dates in cells D22 and your week ending stock values in
| D44,
| RB>this array formula will return the earliest date of a negative
| stock value.
| RB>{=IF(MIN(D44)>0,"N/A",MIN(IF(D44<0,D11)))}
|
| __
| Richard Buttrey
| Grappenhall, Cheshire, UK
| __________________________

 

[Richard Buttrey]

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

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

RB>
RB>Instead, here is a suggestion.....
RB>
RB>I have 5 columns and in cells A1 to E1, I have the following column
RB>headings...
RB>Date
RB>Code
RB>Issues
RB>Returns
RB>Net
RB>
RB>I named the range from A2 downwards "da", the range B2 downwards
"cd", the
RB>range C2 downwards "is", the range D2 downwards "re" and the range
E2
RB>downwards "ne".
RB>
RB>Assuming yout Returns column (columnd D) has negative numbers,
enter the
RB>following formula in cell E2 and drag it down as far as necessary.
RB>=SUMPRODUCT((da<=A2)*(cd=B2)*(is+re))
RB>
RB>In cells H2 downwards, I placed a unique list of all codes.
RB>Enter this "array" formula in cell I2 and then drag down alongside
the code
RB>names 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
__________________________

 

[William]

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

(e-mail address removed)

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" <[email protected]>
| wrote:
|
| RB>Richard
| RB>
| RB>Given that codes will be repeated in your table as you move down
| column B, I
| RB>do not think what you want is what you asked for. I may be wrong
|
| RB>
| RB>Instead, here is a suggestion.....
| RB>
| RB>I have 5 columns and in cells A1 to E1, I have the following column
| RB>headings...
| RB>Date
| RB>Code
| RB>Issues
| RB>Returns
| RB>Net
| RB>
| RB>I named the range from A2 downwards "da", the range B2 downwards
| "cd", the
| RB>range C2 downwards "is", the range D2 downwards "re" and the range
| E2
| RB>downwards "ne".
| RB>
| RB>Assuming yout Returns column (columnd D) has negative numbers,
| enter the
| RB>following formula in cell E2 and drag it down as far as necessary.
| RB>=SUMPRODUCT((da<=A2)*(cd=B2)*(is+re))
| RB>
| RB>In cells H2 downwards, I placed a unique list of all codes.
| RB>Enter this "array" formula in cell I2 and then drag down alongside
| the code
| RB>names 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
| __________________________