Formula does not work-Circular Reference

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can someone help? The following formula will not work. I'm trying to get the
result to appear in C40

=IF(B35-C37-C38-C39-C40-C41=0,B94,IF(AND(B35-C37-C38-C39-C40-C41>0,B35-C37-C38-C39-C40-C41>B94),0,IF(AND(B35-C37-C38-C39-C40-C41>0,B35-C37-C38-C39-C40-C41<B94),B94-B35-C37-C38-C39-C40-C41<B94,0)))
 
Hi
can't work as you're using C40 as parameter in your formula. If you put your
formula in a different cell you may also simplify the formula to
=IF(B35-SUM(C37:C41)=0,B94,IF(B35-SUM(C37:C41)>0,IF(B35-SUM(C37:C41)>B94,0,"not
really defined in your formula")))
 
When you have a formula that refers to itself you get a circular reference.
Allow or correct a circular reference
When a formula refers back to its own cell, either directly or indirectly,
it is called a circular reference. Microsoft Excel cannot automatically
calculate all open workbooks when one of them contains a circular reference.
You can remove a circular reference, or you can have Excel calculate each
cell involved in the circular reference once by using the results of the
previous iteration. Unless you change the default settings for iteration,
Excel stops calculating after 100 iterations or after all values in the
circular reference change by less than 0.001 between iterations, whichever
comes first.

Locate and remove a circular reference

1.. If the Circular Reference toolbar is not displayed, click Customize on
the Tools menu, click the Toolbars tab, and then select the Circular
Reference check box.
2.. On the Circular Reference toolbar, click the first cell in the
Navigate Circular Reference box.
3.. Review the formula in the cell. If you cannot determine whether the
cell is the cause of the circular reference, click the next cell in the
Navigate Circular Reference box.
Note The status bar displays the word "Circular," followed by a
reference to one of the cells contained in the circular reference. If the
word "Circular" appears without a cell reference, the active worksheet does
not contain the circular reference.

4.. Continue to review and correct the circular reference until the status
bar no longer displays the word "Circular."
Tips

a.. When the Circular Reference toolbar appears, tracer arrows appear that
point out the cells that depend on the formula.

You can move between cells in a circular reference by double-clicking the
tracer arrows.

Make a circular reference work by changing the number of times Microsoft
Excel iterates formulas
 
Thanks Don, I need to take more time to figure things out. I appreciate your
response.

Don Guillett said:
When you have a formula that refers to itself you get a circular reference.
Allow or correct a circular reference
When a formula refers back to its own cell, either directly or indirectly,
it is called a circular reference. Microsoft Excel cannot automatically
calculate all open workbooks when one of them contains a circular reference.
You can remove a circular reference, or you can have Excel calculate each
cell involved in the circular reference once by using the results of the
previous iteration. Unless you change the default settings for iteration,
Excel stops calculating after 100 iterations or after all values in the
circular reference change by less than 0.001 between iterations, whichever
comes first.

Locate and remove a circular reference

1.. If the Circular Reference toolbar is not displayed, click Customize on
the Tools menu, click the Toolbars tab, and then select the Circular
Reference check box.
2.. On the Circular Reference toolbar, click the first cell in the
Navigate Circular Reference box.
3.. Review the formula in the cell. If you cannot determine whether the
cell is the cause of the circular reference, click the next cell in the
Navigate Circular Reference box.
Note The status bar displays the word "Circular," followed by a
reference to one of the cells contained in the circular reference. If the
word "Circular" appears without a cell reference, the active worksheet does
not contain the circular reference.

4.. Continue to review and correct the circular reference until the status
bar no longer displays the word "Circular."
Tips

a.. When the Circular Reference toolbar appears, tracer arrows appear that
point out the cells that depend on the formula.

You can move between cells in a circular reference by double-clicking the
tracer arrows.

Make a circular reference work by changing the number of times Microsoft
Excel iterates formulas
Click to expand...
 
Back
Top