Malfunction in cell references

[Svilen Pachedjiev]

I have very strange problem in Excel 2002.
Suppose you have a1, a2, a3 and a4;
a4=a3*(1+a1)*(1+a2);
a3 is a number;
a1 and a2 are formulas, for example: a1=b1*b2+0.06;
and now I change 0.06 to 1 and I have new value for a1
but nothing happens with a4!!! even though this changes.

Please help me to find the problem.
I could send the file, if someone is interesting in it.

Thank you.

Svilen Pachedjiev
Bulgaria

 

[Frank Kabel]

Hi Svilen
have you checked if your calculation mode is set to manual. That is
does hitting F9 changes A4?

Frank

 

[Svilen Pachedjiev]

Calculation mode is set to automatic.
By hitting F9 nothing changes in A4 :-((

I could send you my file so you could look on it.
Shall I send it?

Svilen

 

[Frank Kabel]

just send it: frank[dot]kabel[at]freenet[dot]de

Calculation mode is set to automatic.
By hitting F9 nothing changes in A4 :-((

I could send you my file so you could look on it.
Shall I send it?

Svilen

 

[Frank Kabel]

Hi Svilen
after looking at your sheet, there is no malfunction but Excel behaves
as it should . To explain this a little bit more.
You have the following formula in C1:

=A1*(1+C3)*(1+C4) /with C3=C6-F1 (and F1 is the cell which you changed
with no effect)

now I replace C4 with the formula (1+C6)/(1+C3)+1/(1+C3)-1 (as found in
your sheet):

=A1*(1+C3)*(1+((1+C6)/(1+C3))+(1/(1+C3))-1)

now the first simplification:

=A1*(1+C3)*(((1+C6)/(1+C3))+(1/(1+C3)))

second simplification: combining the 1+C3 fractions:

=A1*(1+C3)*((2+C6)/(1+C3))

third simplification:

=A1*(2+C6)

and as you can see your value in cell F1 has no effect on the result!
Only A1 and C6 determine your result

HTH

Frank