And what about the, Open ID, Close ID, and Gain/Loss columns of data.

I can sort of see where the Gain/Loss numbers come from, but what's
driving the choice of the Open/Close IDs? i.e why 1-3, 1-5, 2-6, 4-8
etc.? What's the rule for deriving those bands?

Rgds


On Mon, 17 Oct 2005 12:06:03 -0700, Sal Young
wrote:

Richard,

The unit price is 285310 (Amount) / 2000 (QTY)


"Richard Buttrey" wrote:

For clarification, can you explain how you derive the Unit Price --
onwards data columns in the goal sheet.

e.g. where does the

Unit Price,OpenID,CloseID,Gain/Loss
142.655,1,3,2320

data in the first record come from?

Rgds

On Mon, 17 Oct 2005 07:54:43 -0700, Sal Young
wrote:

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 that you'll need to import into
two spreadsheets. 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 a running total
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,,,


" wrote:

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




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).

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________