• 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

    • Wouldn’t it be better to disable global c states to improve latency at the cost of marginally higher heat and power usage?
    • I recently bought a brand new Intel 200 series laptop with 16GB, 512GB NVME (now 2TB) with 14 inch OLED screen for $450. Not the fastest laptop in the world, but the screen is great and laptop is small, just what I needed.
    • They forgot to include this slide: "If I get a Mac I won't be able to play games, therefore I will have more time for my studies."
    • It’s not desperation, it’s marketing. Companies do that…
    • Some AMD Ryzen users can get free Windows performance boost with this simple system tweak by Sayan Sen AMD understands that there is a lot of demand for its X3D processors and for good reason too, since they offer some of the best gaming experiences. As such, the company plans to launch a new 6-core Ryzen 5 9600X3D for those who may not want to spend top dollar on a 9800X3D. What makes X3D special is the densely packed last level cache (LLC) wherein the L3 (level 3) cache is 3D die-stacked such that there is a whole lot of it that the cores can access on demand all within the smallest footprint. This is said to help with latency especially, and games happen to be quite sensitive to it since they are a mixed workload and so there is a lot of to-and-fro. However, despite that fact, users have noticed micro-stuttering and freezes on Ryzen X3D CPUs. Although there is no official fix, some of the affected users have managed to resolve the issues by tweaking a motherboard setting. The tweak is related to a setting called "GLOBAL C-STATE CONTROL" (it may be called something else by your motherboard vendor) and changing it to 'Enabled' from 'Auto' could fix stuttering and lag-related issues in games. If you are not familiar with them, Processor Power Management is done through Advanced Configuration and Power Interface (ACPI) P-states or C-states. While P-states or performance states handle CPU voltage-frequency scaling, C-states deal with CPU sleep states so that some of the CPU functions, which are not necessary at that moment, are disabled. The P-states and C-states work together to make the processor run more efficiently. It helps the OS and apps determine which cores can be parked. The Global C-state control setting helps users manage not only the DF and CPU core C-states but also the I/O C-states too. For those wondering, DF here refers to Data Fabric or AMD's high bandwidth Infinity Fabric interconnect between CPUs, GPUs, and more, on AMD systems. By default, this is set to "Auto" which also means that it is "Enabled" by default. However, in the case of X3D parts, Auto may set this setting to "Disabled" and thus manually toggling it to "Enabled" may be necessary. X3D processors, the dual CCD (core complex die) ones especially, have their V-cache on a single CCD. If the CPPC (Collaborative Processor Performance Control), which lets an OS like Windows control the "preferred core" and clock speed boost, isn't working optimally to assign the correct gaming CCD, then this fix could well work. Global C-State Auto: Global C-State Enabled: We ran a benchmark on our Ryzen 9 9950X3D to see if toggling the settings would make a difference, and well, it didn't in the case of AIDA64. However, since this is a synthetic test that measures cache and memory exclusively, we can't definitively conclude that the fix will also not make a difference in the case of games. Another remedy for stuttering is to disable the monitoring of the "Power percent" metric on MSI Afterburner if you have it on. This has been a long-known issue and in fact can help you even if you are not using an X3D CPU. Source: Reddit (link1, link2) via YouTube
  • Recent Achievements

    • One Month Later
      DecaffKnight94 earned a badge
      One Month Later
    • Dedicated
      S.P earned a badge
      Dedicated
    • One Month Later
      adxnksd42031 earned a badge
      One Month Later
    • Rising Star
      aphanic went up a rank
      Rising Star
    • Contributor
      GravityDead went up a rank
      Contributor
  • Popular Contributors

    1. 1
      +primortal
      663
    2. 2
      ATLien_0
      260
    3. 3
      Michael Scrip
      234
    4. 4
      Steven P.
      157
    5. 5
      +FloatingFatMan
      151
  • Tell a friend

    Love Neowin? Tell a friend!