• 0

Circular Reference in Excel


Question

Hi All.

 

I'll try to explain this as best I can

   A       B 
1 Add      50 <----User Editable
2 Add      70 <----User Editable
3 Sub      20 <----4% of Total in B7 (Formula)
4 SbTotal 100 <----B1+B2-B3 (Formula)
5 Add     300 <----User Editable
6 Add     100 <----User Editable
7 Total   500 <----B4+B5+B6 (Formula)

The figure calculated in B3 is calculated as set percentage of B7.  Understandably this produces a circular reference whereby I imagine some horrible never ending loop would occur.

 

I wondered if there was a way to loop this until the Total in B7 stopped changing. 

 

These are my thoughts in pseudo-code:

 

LOOP UNTIL VAR1 = VAR2
 
    RUN FORMULA IN CELL B7
    VAR1 = B7
    RUN FORMULA IN CELL B4
    RUN FORMULA IN CELL B7
    VAR2 = B7
 
END LOOP

 
 

They ultimately only need to match to two decimal spaces.

 

 

Many thanks!

Link to comment
https://www.neowin.net/forum/topic/1254538-circular-reference-in-excel/
Share on other sites

3 answers to this question

Recommended Posts

  • 0

I think this is a case of just getting so caught up in doing this in a very spreadsheet-centric way, that you've missed the forest for the trees. This is just algebra.

Let..
A,B,C,D = the 4 cells added
X = the cell subtracted
T = the total

Therefore..
A+B-X+C+D = T
X = 0.04T

Then..
A+B+C+D = T+0.04T
A+B+C+D = 1.04T
(A+B+C+D)/1.04 = T

Or, in other words: Total = (B1+B2+B5+B6)/1.04

Done.

Alternatively, you could replace the one for the amount subtracted.

X = 0.04(A+B-X+C+D)
X = 0.04(A+B+C+D) - 0.04X
1.04X = 0.04(A+B+C+D)
X = 0.04(A+B+C+D)/1.04
X = (A+B+C+D)/26

Subtracted amount = (B1+B2+B5+B6)/26

Replace one or both of those two formulas, and the circular reference is gone.

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • I haven't heard that sound in a while and now I kinda miss it.
    • Do we really expect home users to have access to enterprise licenses?
    • The 2025 Complete Splunk Beginner Bundle is now 25% off by Steven Parker Today's highlighted deal comes via Neowin Deals store, where you can save 75% on The 2025 Complete Splunk Beginner Bundle. Splunk is a powerful data platform used to gather information from multiple sources and index it for efficient access. You can then use collected data to create visualizations, analytics, and a variety of automated and security-related functions. With its web-style interface, Splunk is easy to use and is utilized by many companies worldwide. What's Included: Splunk Fundamentals for Effective Management of SOC and SIEM Oak Academy 38 Lessons (3.5h) Lifetime $20.00 Value Splunk | Splunk Core Certified User Certification Prep Lab Oak Academy, 63 Lessons (6h),Lifetime, $20.00 Value Splunk | Splunk Core Certified Power User SPLK 1002 Prep Oak Academy, 53 Lessons (5.5h), Lifetime, $20.00 Value Splunk| Splunk Enterprise Certified Admin Certification Prep Oak Academy, 68 Lessons (8.5h), Lifetime, $20.00 Value Requirements Basic understanding of IT and networking concepts Familiarity with Linux and Windows operating systems A computer with internet access for hands-on practice Good to Know Length of time users can access this course: lifetime Access options: desktop or mobile Redemption deadline: redeem your code within 30 days of purchase Experience level required: all levels Certificate of Completion ONLY Updates included Closed captioning NOT available NOT downloadable for offline viewing Learn more about our Lifetime deals here! Lifetime access to this 2025 Complete Splunk Beginner Bundle normally costs $80, but this deal can be yours for just $19.99, that's a saving of $60. For full terms, specifications, and info, click the link below. Get the 2025 Complete Splunk Beginner Bundle for just $19.99 (75% off MSRP: $80) Although priced in U.S. dollars, this deal is available for digital purchase worldwide. We post these because we earn commission on each sale so as not to rely solely on advertising, which many of our readers block. It all helps toward paying staff reporters, servers and hosting costs. Other ways to support Neowin Whitelist Neowin by not blocking our ads Create a free member account to see fewer ads Make a donation to support our day to day running costs Subscribe to Neowin - for $14 a year, or $28 a year for an ad-free experience Disclosure: Neowin benefits from revenue of each sale made through our branded deals site powered by StackCommerce.
    • and you got scammed because 2077 launch its one of the worse thing i ever remember and even to this day the game never reached its potential
  • Recent Achievements

    • Apprentice
      Wireless wookie went up a rank
      Apprentice
    • Week One Done
      bukro earned a badge
      Week One Done
    • One Year In
      Wulle earned a badge
      One Year In
    • One Month Later
      Wulle earned a badge
      One Month Later
    • One Month Later
      Simmo3D earned a badge
      One Month Later
  • Popular Contributors

    1. 1
      +primortal
      558
    2. 2
      ATLien_0
      258
    3. 3
      +FloatingFatMan
      182
    4. 4
      Michael Scrip
      125
    5. 5
      Steven P.
      102
  • Tell a friend

    Love Neowin? Tell a friend!