Help with complex formula or macro or solution

[Sal Young]

I need to write some kind of balance sheet where the balance is calculated
based on how much a quantity of one record covers the quantity of another
record with the opposite sign. Here's an example.

QTY UNITCOST TotCost BalQTY BalAMT
500 200 100,000 500 100,000
-100 210 -21,000 400 79,000
500 205 102,500 900 181,500
-600 210 -126,000 300 55,500


The columns & values I have to generate are the BalQTY and BalAMT. I wan to
point out that a record may close multiple records as demonstrated with the
first record closing record 2 (qty 100) and partially record 4 (qty 400).

 

[twox4s]

Sal:

I'll take a shot at this though I must say that your question
leaves much to guess at. If this "Table" resides in cells(A1:E5) then
your formulas would be as follows for cells(C2:E5)

C2:=A2*B2
C3:=A3*B3
C4:=A4*B4
C5:=A5*B5

D2:=A2
D3:=SUM(D2,A3)
D4:=SUM(D3,A4)
D5:=SUM(D4,A5)

E2:=C2
E3:=SUM(E2,C3)
E4:=SUM(E3,C4)
E5:=SUM(E4,C5)

Once you have the formulas in place, you can just autofill them down
the length of your sheet and they should do what it is I think you are
asking of it. Lemme know if I got it right or missed the point
completely...Mark

 

[Sal Young]

Thanks for your reply.

You're correct, there is a lot to guess from my question because this is a
very complex problem. Let me try to explain a little bit better. I'm
including two comma delimited sets of records. The first set is what the
user will have in their spreadsheet and the second set is what the end result
should look like.
Your recommended solution will not work because it will generate an average
Gain/Loss and what I'm looking for is a first in first out type of operation
that will allow the user to see the Gain/Loss per open/close record.

maybe you can provide me with an email where I can send you the Excel
spreadsheet.

Here's a sample of the original data:
ID,QTY,CODE,Amount,
1,2000,C,285310,
2,1000,C,143630,
3,-1000,V,144975,
4,1000,C,143990,
5,-1000,V,144960,
6,-1000,V,145774,
7,2000,C,285720,
8,-1000,V,145762.4,
9,1000,C,145610,
10,1000,C,145380,
11,1000,C,145880,
12,-1000,V,145598,
13,-1000,V,145454.18,
14,1000,C,143905,
15,-1000,V,145705,
16,2000,C,286540
17,-1000,V,145595
18,-1000,V,145710.64
19,1000,C,145425
20,-1000,V,145702
21,-1000,V,145750
22,1000,C,143390
23,-1000,V,145827.72
24,-1000,V,145903
25,2000,C,285220
26,-1000,V,145702.55
27,-1000,V,145625
28,-1000,V,145738
29,1000,C,145525
30,-1000,V,144975


Here's a sample of the desire goal:
ID,QTY,CODE,Amount,Unit Price,OpenID,CloseID,Gain/Loss
1,2000,C,285310,142.655,1,3,2320
2,1000,C,143630,143.63,1,5,2305
3,-1000,V,144975,-144.975,2,6,2144
4,1000,C,143990,143.99,4,8,1772.4
5,-1000,V,144960,-144.96,7,12,2738
6,-1000,V,145774,-145.774,7,13,2594.18
7,2000,C,285720,142.86,9,15,95
8,-1000,V,145762.4,-145.7624,10,17,215
9,1000,C,145610,145.61,11,18,-169.36
10,1000,C,145380,145.38,14,20,1797
11,1000,C,145880,145.88,16,21,2480
12,-1000,V,145598,-145.598,16,23,2557.72
13,-1000,V,145454.18,-145.45418,19,24,478
14,1000,C,143905,143.905,22,26,2312.55
15,-1000,V,145705,-145.705,25,27,3015
16,2000,C,286540,143.27,25,28,3128
17,-1000,V,145595,-145.595,29,30,-550
18,-1000,V,145710.64,-145.71064,,,
19,1000,C,145425,145.425,,,
20,-1000,V,145702,-145.702,,,
21,-1000,V,145750,-145.75,,,
22,1000,C,143390,143.39,,,
23,-1000,V,145827.72,-145.82772,,,
24,-1000,V,145903,-145.903,,,
25,2000,C,285220,142.61,,,
26,-1000,V,145702.55,-145.70255,,,
27,-1000,V,145625,-145.625,,,
28,-1000,V,145738,-145.738,,,
29,1000,C,145525,145.525,,,
30,-1000,V,144975,-144.975,,,

 

[twox4s]

Please do send me a copy, Email is the posted (e-mail address removed).
I'll play with it when I get it. Looking at it in this format leaves
me dizzy.