If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Matching" column A's values in column B
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(A10,IF(E1+MIN(F1:F8)0,MIN(E1+MIN(F1:F8),A1) ,0),IF(E1+F10,0,E1+F1)) c1 = =IF(A10,A1,0) d1 = =IF(A10,A1,0) e1 = =+SUM($C$1:C1) f1 = =+SUM($D$11) 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(a10, 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 "buffalo " wrote in message ... 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 the 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" in "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 A (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. thanks --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|