| 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). |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help wih complex formula or macro or solution
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 __________________________ |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help wih complex formula or macro or solution
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 __________________________ |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help wih complex formula or macro or solution
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 __________________________ |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help wih complex formula or macro or solution
Your first record will always set the SIGN of the OpenIDs. In this example,
all positive (QTY) numbers will be OpenID. If the first record in the spreadsheet was a negative number; then, all negative numbers will be OpenIDs. So, all positive QTY are OpenID & all negative QTY are CloseID in this example. Now that you have your first OpenID, you'll match to the first record (1) with the first record that has an opposite SIGN which in this case is record 3. After these two records are matched, record 1 still has a balance QTY of 1000 which needs to be matched to the next record with the opposite SIGN in this case, record 5. This will leave record 1 with a balance QTY of 0 and record 1 matched to 3 & 5. You'll move to the next OpenID which is 2 because it has a positive QTY & does it all over again until you run out of records. "Richard Buttrey" wrote: 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 __________________________ |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help wih complex formula or macro or solution
Sal, a quick update.
I'm just finishing off a potential solution. I'm out of the office at the moment but hope to get a VBA macro to you tomorrow - Thursday. Rgds Richard Buttrey wrote: 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 __________________________ |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help wih complex formula or macro or solution
Sal,
I'm posting below 5 procedures that will produce the summary you're after. The assumption is that the 4 columns of original data have been loaded into A1:D31 using the example data below. (I used the DATA Text to Coumns, after I'd posted your CSV details into column A) The solution will be shown in cols G:N ( Obviously if you want to change the positioning you'll have to adjust the code) You should attach the first procedure (GainLossCalc) to a macro button Rgds Sub GainLossCalc() Call CopyData Call UnitPrice Call OpenCloseID Call GainLoss Call TidyUp End Sub Sub CopyData() [A1].CurrentRegion.Copy Destination:=Range("G1") [F1] = "IDUsed": [K1] = "Unit Price": [L1] = "Open ID" [M1] = "Close ID": [N1] = "Gain / Loss" End Sub Sub UnitPrice() [K2] = "=j2/h2" [K2].Copy Range([J3], [J3].End(xlDown)).Offset(0, 1).PasteSpecial (xlPasteAll) End Sub Sub OpenCloseID() Dim stFirst As String Dim iOpenID As Integer Dim x As Integer, y As Integer Dim iBal As Integer Dim iCurRow As Integer If [B2] 0 Then stFirst = "+" Else stFirst = "-" For x = 1 To [A1].CurrentRegion.Rows.Count If [B2].Cells(x, 1) 0 And stFirst = "+" Then iOpenID = x iBal = [B2].Cells(x, 1) End If If [B2].Cells(x, 1) < 0 And stFirst = "-" Then iOpenID = x iBal = [B2].Cells(x, 1) End If y = 1 Do While iBal < 0 If (stFirst = "+" And [B2].Cells(x + y) < 0 _ Or stFirst = "-" And [B2].Cells(x + y) 0) _ And [B2].Cells(x + y).Offset(0, 4) < "X" Then iBal = iBal + [B2].Cells(x + y) [B2].Offset(x + y - 1, 4) = "X" [L65536].End(xlUp).Offset(1, 0) = iOpenID [M65536].End(xlUp).Offset(1, 0) = x + y End If y = y + 1 Loop Next End Sub Sub GainLoss() [N2] = "=-VLOOKUP(L2,A:D,4)/VLOOKUP(L2,A:D,2)*1000-VLOOKUP(M2,A:D,4)" _ & "/VLOOKUP(M2,A:D,2)*1000" [N2].Copy Range([M3], [M3].End(xlDown)).Offset(0, 1).PasteSpecial (xlPasteAll) End Sub Sub TidyUp() [F:F].ClearContents [G1].CurrentRegion.Copy: [G1].PasteSpecial (xlPasteValues) Application.CutCopyMode = False End Sub On Mon, 17 Oct 2005 12:06:03 -0700, Sal Young wrote: 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 __________________________ |
| Reply |
| 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 | |||
Linear Mode
