Developer Forum

Hi,

THis problem may seem a bit vague ...

Suppose I want to type into column A the following values

+20
+10
-20
-5
10
-10
etc...

What I want to do is create another MATCHED column with "Closed out
values

This column would be updated as i fill my data in Col. A in th
following fashion

First entry (value entered is 20 in A1)
A B
20 20 <-- automatic update in B1

2nd entry (value entered is 10 in A2)
A B
20 20
10 10 <--- new update

3rd entry(-20 in A3)
A B
20 0 <---- note this?
10 10
-20 0 <--- this value is gone, because it has been "matched" above

(B1 is now 0, this is because I'm "matching" the "first one in" i
"MATCHED Column" B with the new update in A)


A B
20 0
10 5 <---- now this is "matched" as much as possible
-20 0
-5 0 <---- and this value is gone

A B
20 0
10 5
-20 0
-5 0
10 10
. no match, so the new value in column remains as same as in column


(this one is important)
A B
20 0
10 0 <--- now this value was fully matched
-20 0
-5 0
10 5 <-- this value was partly matched
-10 0 <--- and this value is 0

And so on..
Any help on how to do this would be greatly appreciated.

thank

Hi
I have got something that I think works but it is not just one column. I
don't guarantee that this is the simplest way, but it works on the figures
in your example.
Put the formulas in column's b through to f and copy them down alongside
your data.

a1 = 20
b1 =
=+IF(A1>0,IF(E1+MIN(F1:F8)>0,MIN(E1+MIN(F1:F8),A1),0),IF(E1+F1>0,0,E1+F1))
c1 = =IF(A1>0,A1,0)
d1 = =IF(A1<0,A1,0)
e1 = =+SUM($C$1:C1)
f1 = =+SUM($D$1:D1)

Thus column C displays positive entries of A
Column D displays Negative entries of A
Column E displays the total positive entries todate
Column F displays the total negative entries to date.
Coulmn B Checks for positive or negative values in A.if(a1>0,
Then checks if cumulative positive values are greater than total negative
values, if (e1+min(f1:f8)>0. If positive values are greater then use
minimum of A1 difference between cumulative positve values and total
negative values.

You should be able to follow the rest of b1 yourself but if you have
problems please let me know.
Let me know if this helps please.

Best of luck

Chris