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

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" <FinAnalyst@discussions.microsoft.com> a écrit dans le message
de news:C0B569BE-FB9F-46FA-9F20-B965C1BCD1A5@microsoft.com...
> 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

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" <FinAnalyst@discussions.microsoft.com> a écrit dans le message
> de news:C0B569BE-FB9F-46FA-9F20-B965C1BCD1A5@microsoft.com...
> > 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
>
>
>

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" <FinAnalyst@discussions.microsoft.com> a écrit dans le message
> de news:C0B569BE-FB9F-46FA-9F20-B965C1BCD1A5@microsoft.com...
> > 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
>
>
>

I'm glad I could help!

--
AP

"Fin Analyst" <FinAnalyst@discussions.microsoft.com> a écrit dans le message
de news:8AE9D4F9-4EC0-48CD-9821-69C207776FE6@microsoft.com...
> 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" <FinAnalyst@discussions.microsoft.com> a écrit dans le
message
> > de news:C0B569BE-FB9F-46FA-9F20-B965C1BCD1A5@microsoft.com...
> > > 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
> >
> >
> >