Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help wih complex formula or macro or solution
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). |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple problem complex solution? | Excel Worksheet Functions | |||
Trying to find out a solution for a complex formula | Excel Discussion (Misc queries) | |||
Hlookup or Vlookup problem? or wich other solution? Complex Proble | Excel Discussion (Misc queries) | |||
Help with complex formula or macro or solution | Excel Programming | |||
Macro better solution than cell formula with date? | Excel Programming |