Malfunction in cell references

  • Thread starter Thread starter Svilen Pachedjiev
  • Start date Start date
S

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
 
Hi Svilen
have you checked if your calculation mode is set to manual. That is
does hitting F9 changes A4?

Frank
 
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
 
just send it: frank[dot]kabel[at]freenet[dot]de
Svilen said:
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
Click to expand...
 
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
 
Back
Top