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
|