Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts

    formula (Excel 2000)

    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

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: formula (Excel 2000)

    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?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formula (Excel 2000)

    ReCalc manually doesn't work either.

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

    --KZ
    Attached Files Attached Files

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: formula (Excel 2000)

    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>
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula (Excel 2000)

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

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    727
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formula (Excel 2000)

    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>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •