change a formula dynamically

Z

Zuohong

Hi all,

I'm experiencing a tough MS excel function problem. Please give me
some advices.

-------------------------------------
a b c d
1 3 9 50
2 10 8 40
3 7 30
4 6 20
5 5 10
.. . .
.. . .
.. . .
-------------------------------------

I need to put my results in column D. Currently, D1 should euqal to
"if(C1-SUM(B1:B3)>C3,C1-B1,C1-B1+$A$2)". That means I need to make a
justification that whether C1 is bigger than the summary of B1 to B3.
If it's smaller than the sum, I need to add a constant($A$2) to D1
while reducing B1. Then D2=if(C2-SUM(B2:B4)>C4,C2-B2,C2-B2+$A$2), and
so on.
But, the problem is that the $A$1=3 is NOT a constant number!!! For
different products, this A1 is different. Perhaps it's 6 sometimes, so
the D1 changes to "if(C1-SUM(B1:B6)>C6,C1-B1,C1-B1+$A$2)". Since
there're hundreds of cases to deal with, certainly I don't want to
change the formulas again and again.
Would you please help me to resolve the trouble? Thank you very much.

zh.y
 
M

Myrna Larson

I don't follow you. I don't see any reference to cell $A$1 in the formula. Did
you mean $A$2?

If the constant depends on a product number (in which cell?) then you need to
replace the reference to $A$2 with a formula like VLOOKUP that uses the
product number to fetch the correct amount to be added.

But I see another problem: you refer to "reducing B1". Your formula can't do
that. It can only return a value to the cell in column D. In fact a formula in
B1 that refers to B1 will create a circular reference. Maybe you will need to
set up a whole new set of columns for the amended value in column B.
 
Z

Zuohong

It is $A$1, this number refers to a period of time (in weeks)
actually.
To be explicit, I need to change the formulas in column D, according
to the value of $A$1. If $A$1=3,
D1="if(C1-SUM(B1:B3)>C3,C1-B1,C1-B1+$A$2)",
D2="if(C2-SUM(B2:B4)>C4,C2-B2,C2-B2+$A$2)"...
When $A$1=4, D1="if(C1-SUM(B1:B4)>C4,C1-B1,C1-B1+$A$2)",
D2="if(C2-SUM(B2:B5)>C5,C2-B2,C2-B2+$A$2)"...
 
Z

Zuohong

To be more simplified, I need to firstly get the value of $A$1, for
example $A$1=6, then I cite this value to be a mark for another cell,
for instance, $B$6. But it's NOT always 6, it's changing according to
different $A$1. However, I tried to adopt something like $B$($A$1),
but it's not possible.
 
Top