Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose? Offset? Variable starting cell
Good afternoon~
I have scoured the discussion boards and my mind, but can't seem to figure this one out. Any help would be much appreciated. OK, here is my problem. I have a chart listing %s (C20:C34). These %s represent how much cost should be allocted to month 1 through (at most, but could be less) month 15 of a production period. The problem is the starting month for the cost is variable, in cell D75. When the month number in D75 matches the month number in the array H4:DW4 the costs should start based on the %s input in C20:C34, ie. if the 1st month of production % (C20) is 10% and the costs don't start until month 10 (in this array would be Q4) then I need in Q75 to show the result of 10%*G75 (total cost). In R75 the resulting formula would be C21*G75 and so on until you reach the end of the production month period. I'm thinking some sort of combination of OFFSET and TRANSPOSE would be in order, but I can't exactly figure out how to word the formula. Maybe it would be helpful to first transpose the chart %s first and go from there? Please help if you have any ideas. Hopefully I am explaining my dilemna appropriately so others can understand my question. If you have any questions about my problem please post so that I may answer. Let me help you help me. ;) Thanks in advance for your help. Jaclyn |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose? Offset? Variable starting cell
I'm not quite sure I understood your problem
Enter in H75 following formula, then copy right to DW75. =IF(OR(COLUMN()-COLUMN($G75)<$D75,COLUMN()-COLUMN($G75)=$D75+ROWS($C$20:$C$ 34)),"",$G75*OFFSET($C$20,COLUMN()-COLUMN($G75)-$D75,0)) HTH -- AP "Fin Analyst" a écrit dans le message de ... Good afternoon~ I have scoured the discussion boards and my mind, but can't seem to figure this one out. Any help would be much appreciated. OK, here is my problem. I have a chart listing %s (C20:C34). These %s represent how much cost should be allocted to month 1 through (at most, but could be less) month 15 of a production period. The problem is the starting month for the cost is variable, in cell D75. When the month number in D75 matches the month number in the array H4:DW4 the costs should start based on the %s input in C20:C34, ie. if the 1st month of production % (C20) is 10% and the costs don't start until month 10 (in this array would be Q4) then I need in Q75 to show the result of 10%*G75 (total cost). In R75 the resulting formula would be C21*G75 and so on until you reach the end of the production month period. I'm thinking some sort of combination of OFFSET and TRANSPOSE would be in order, but I can't exactly figure out how to word the formula. Maybe it would be helpful to first transpose the chart %s first and go from there? Please help if you have any ideas. Hopefully I am explaining my dilemna appropriately so others can understand my question. If you have any questions about my problem please post so that I may answer. Let me help you help me. ;) Thanks in advance for your help. Jaclyn |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose? Offset? Variable starting cell
For not quite understanding the problem that formula looks like it did the
trick. Thank you so much! =) (I keep forgetting about those row and column formulas.) Jaclyn "Ardus Petus" wrote: I'm not quite sure I understood your problem Enter in H75 following formula, then copy right to DW75. =IF(OR(COLUMN()-COLUMN($G75)<$D75,COLUMN()-COLUMN($G75)=$D75+ROWS($C$20:$C$ 34)),"",$G75*OFFSET($C$20,COLUMN()-COLUMN($G75)-$D75,0)) HTH -- AP "Fin Analyst" a écrit dans le message de ... Good afternoon~ I have scoured the discussion boards and my mind, but can't seem to figure this one out. Any help would be much appreciated. OK, here is my problem. I have a chart listing %s (C20:C34). These %s represent how much cost should be allocted to month 1 through (at most, but could be less) month 15 of a production period. The problem is the starting month for the cost is variable, in cell D75. When the month number in D75 matches the month number in the array H4:DW4 the costs should start based on the %s input in C20:C34, ie. if the 1st month of production % (C20) is 10% and the costs don't start until month 10 (in this array would be Q4) then I need in Q75 to show the result of 10%*G75 (total cost). In R75 the resulting formula would be C21*G75 and so on until you reach the end of the production month period. I'm thinking some sort of combination of OFFSET and TRANSPOSE would be in order, but I can't exactly figure out how to word the formula. Maybe it would be helpful to first transpose the chart %s first and go from there? Please help if you have any ideas. Hopefully I am explaining my dilemna appropriately so others can understand my question. If you have any questions about my problem please post so that I may answer. Let me help you help me. ;) Thanks in advance for your help. Jaclyn |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose? Offset? Variable starting cell
I should also mention that I added an if/ then statement in case the
resulting production cost for the month is zero, ie if the production period was less than the 15 months possible. Below is my final formula: =IF(OR(COLUMN()-COLUMN($G60)<$D60,COLUMN()-COLUMN($G60)=$D60+ROWS($C$20:$C$34)),"",IF($G60*O FFSET($C$20,COLUMN()-COLUMN($G60)-$D60,0)=0,"",$G60*OFFSET($C$20,COLUMN()-COLUMN($G60)-$D60,0))) Thanks again for your help! Because of this formula I was able to delete over 1,000 hidden rows from our previous template. The previous person set up the calculation the long way. Jaclyn "Ardus Petus" wrote: I'm not quite sure I understood your problem Enter in H75 following formula, then copy right to DW75. =IF(OR(COLUMN()-COLUMN($G75)<$D75,COLUMN()-COLUMN($G75)=$D75+ROWS($C$20:$C$ 34)),"",$G75*OFFSET($C$20,COLUMN()-COLUMN($G75)-$D75,0)) HTH -- AP "Fin Analyst" a écrit dans le message de ... Good afternoon~ I have scoured the discussion boards and my mind, but can't seem to figure this one out. Any help would be much appreciated. OK, here is my problem. I have a chart listing %s (C20:C34). These %s represent how much cost should be allocted to month 1 through (at most, but could be less) month 15 of a production period. The problem is the starting month for the cost is variable, in cell D75. When the month number in D75 matches the month number in the array H4:DW4 the costs should start based on the %s input in C20:C34, ie. if the 1st month of production % (C20) is 10% and the costs don't start until month 10 (in this array would be Q4) then I need in Q75 to show the result of 10%*G75 (total cost). In R75 the resulting formula would be C21*G75 and so on until you reach the end of the production month period. I'm thinking some sort of combination of OFFSET and TRANSPOSE would be in order, but I can't exactly figure out how to word the formula. Maybe it would be helpful to first transpose the chart %s first and go from there? Please help if you have any ideas. Hopefully I am explaining my dilemna appropriately so others can understand my question. If you have any questions about my problem please post so that I may answer. Let me help you help me. ;) Thanks in advance for your help. Jaclyn |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose? Offset? Variable starting cell
I'm glad I could help!
-- AP "Fin Analyst" a écrit dans le message de ... I should also mention that I added an if/ then statement in case the resulting production cost for the month is zero, ie if the production period was less than the 15 months possible. Below is my final formula: =IF(OR(COLUMN()-COLUMN($G60)<$D60,COLUMN()-COLUMN($G60)=$D60+ROWS($C$20:$C$ 34)),"",IF($G60*OFFSET($C$20,COLUMN()-COLUMN($G60)-$D60,0)=0,"",$G60*OFFSET( $C$20,COLUMN()-COLUMN($G60)-$D60,0))) Thanks again for your help! Because of this formula I was able to delete over 1,000 hidden rows from our previous template. The previous person set up the calculation the long way. Jaclyn "Ardus Petus" wrote: I'm not quite sure I understood your problem Enter in H75 following formula, then copy right to DW75. =IF(OR(COLUMN()-COLUMN($G75)<$D75,COLUMN()-COLUMN($G75)=$D75+ROWS($C$20:$C$ 34)),"",$G75*OFFSET($C$20,COLUMN()-COLUMN($G75)-$D75,0)) HTH -- AP "Fin Analyst" a écrit dans le message de ... Good afternoon~ I have scoured the discussion boards and my mind, but can't seem to figure this one out. Any help would be much appreciated. OK, here is my problem. I have a chart listing %s (C20:C34). These %s represent how much cost should be allocted to month 1 through (at most, but could be less) month 15 of a production period. The problem is the starting month for the cost is variable, in cell D75. When the month number in D75 matches the month number in the array H4:DW4 the costs should start based on the %s input in C20:C34, ie. if the 1st month of production % (C20) is 10% and the costs don't start until month 10 (in this array would be Q4) then I need in Q75 to show the result of 10%*G75 (total cost). In R75 the resulting formula would be C21*G75 and so on until you reach the end of the production month period. I'm thinking some sort of combination of OFFSET and TRANSPOSE would be in order, but I can't exactly figure out how to word the formula. Maybe it would be helpful to first transpose the chart %s first and go from there? Please help if you have any ideas. Hopefully I am explaining my dilemna appropriately so others can understand my question. If you have any questions about my problem please post so that I may answer. Let me help you help me. ;) Thanks in advance for your help. Jaclyn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
automatic offset cell location when there is a value in a selected cell. | Excel Worksheet Functions | |||
Can the column index in a cell address be made variable? | Excel Discussion (Misc queries) | |||
Linking a cell to another workbook cell based on a variable name | Excel Discussion (Misc queries) | |||
DDE linking with variable from named cell! | Links and Linking in Excel |