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

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

1. Sort column B but keep value in column A with value column B

2. Matching values from two columns and making a third column with the results - possible?

3. Match 2 Columns and Add the Values of Third Column

4. column A matches f places the value from g in column b what funct

5. Match attribute values (columns) to column names

6. Return text in Column A if Column B and Column K match

7. Parent Columns and Child Columns don't have type-matching columns

8. how to match matched ID and name columns to organized ID column

9. Need to match 2 columns, if a match found add info from 2nd column

10. match row, then match column, then get header for that column?

11. trigger to update column B with column A when column B value is -1

12. Match formula to match values in multiple columns

13. If two criteria match then sum matching values in another column

14. how can i get only columns (along with column name) with values in column

15. Match formula to match values in multiple columns

2 post • Page:**1** of **1**