Excel formula "just in time" system

[drakaz]

I'm doing a bit similar thing called as "just in time" system.
I attached a bit of table I'm trying to take date and number off.
I basically want to check when Paddington runs out, that is when D row minus E row becomes negative and take that date and value when it happened in order to "order" more.

D3 is always fixed value, there will never be a value in E3.

For example, if we take D3+D5-E5 it is more than 0 (or value that I would put as a minimum product stock that should be always available), than formula should check further if D3+D5-E5+D6-E6 is <0 and now (by the document attached) it turns to negative value. First thing what I need is that negative value, and later in another cell the date when it became negative (or lower than I want it to be) from A column.

[Marcol]

Try this
In F5

Please Login or Register  to view this content.

Drag/Fill Down

Conditional Format
Formula is:=

Please Login or Register  to view this content.

Applies to:=

Please Login or Register  to view this content.

Is that what you mean?

[drakaz]

...

Well, it's something to that side, but I do not want Balance column, I need to fill only one cell with the negative number in this case -10. But I need to find where it is, as well on what date it appeared on.

With this excel if I would use IF function it would look smth like this:

=IF((SUM(D3,D5)-SUM(E5))<0,(SUM(D3,D5)-SUM(E5))*(-1),IF((SUM(D3,D5:D6)-SUM(E5:E6))<0,(SUM(D3,D5:D6)-SUM(E5:E6))*(-1),IF((SUM(D3,D5:D7)-SUM(E5:E7))<0,(SUM(D3,D5:D7)-SUM(E5:E7))*(-1),IF((SUM(D3,D5:D8)-SUM(E5:E8))<0,(SUM(D3,D5:D8)-SUM(E5:E8))*(-1),IF((SUM(D3,D5:D9)-SUM(E5:E9)),(SUM(D3,D5:D9)-SUM(E5:E9))*(-1),"")))))

I gave excel with two negative values, thus I don't trust my excel knowledge that much so I would stick that only one negative value would appear in the model.
However, IF function is a bit too long and long equals mistakes and a lot of job, here is only 5 cells and I have 20 cell long data.

[Marcol]

Is this any closer to your needs?

Try changing the Dr/Cr values in rows 5 to 9 and/or D3

Hope this helps

[drakaz]

Thank you for your help