formula (Excel 2000)

**kzkz** · 2002-09-11 19:49

I am trying to take two cells (that are both totals from other tabs in the same spreadsheet) and dividing one total by the other total. It keeps telling me that it's a circular formula, but it's not.

How can I get around this?

--KZ

**JohnBF** · 2002-09-11 21:15

Is the file on automatic or manula recalc? If on manual, recalc it to see if the circular ref goes away. In my experience Excel isn't usually wrong about circular formulas, except for one complex file I saved before recalc was complete. Can you post a sample?

**kzkz** · 2002-09-11 21:48

ReCalc manually doesn't work either.

I have posted the ss here. Look at the tab called PayCalc and the cells in blue.

--KZ

**JohnBF** · 2002-09-11 22:23

There is at least one circular reference as follows starting from Cell PayCalc!B10:

PayCalc!B10 uses PayCalc!D17 which uses 'One-Time'!AG57 which sums cells 'One-Time'!AG3:AG56 each of which is a multiple of 'One-Time'!Yn * 'One-Time'!In (n = row number, & that's capital 'i') and lo, cells 'One-Time'!I3:I5 all use the value of Cell PayCalc!B10.

Don't feel bad, I've done worse. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

**Michael Evans** · 2002-09-11 22:54

The Auditing option from the Tools menu is useful in locating problems like this.

**kzkz** · 2002-09-11 23:29

Wow, can't believe I overlooked that. Phew.
Thanks...guess it's been a long day.

<img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Thread: formula (Excel 2000)

Thread Tools