This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(217.60000000000002, 2%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EES%3C/text%3E%3C/svg%3E)
I have a formula in Excel where the value is dependent on the value of the cell above under one condition and the cell below under another condition. Excel sees that there are references which will end up circular with certain values and gives circular ref error, but I know that it would never been an issue because of the way the conditions and values are written.
Is there any way I can tell it to ignore the potential problem and just calculate?
=IFS(B31=100-C$4,C$6*C$5,B31>100-C$4, D30*C$8,TRUE, D32*C$7)
all numbers are positive
C$8 & C$7 are less than 1.
Thanks for any help!
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(32, 95%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
You can try this: https://www.ablebits.com/office-addins-blog/2015/12/15/circular-reference-excel/#iterative-calculations
If it doesnt work I think you need to restructure your table to avoid it.
Also Im not sure that what you shared is enough to resolve the problem I couldnt tell where the circular error is coming from, but maybe thats just me.
Thanks
I ended up rewriting the formula so that instead of having a single formula which could refer to the cell above or below depending on a conditional, there are now two formulas: one refers to the cell below and the other to the cell above. The result of the two are summed. This is logically identical, but keeps Excel happy.
Ethan
Please check if the answer given by Andrew-6911 is helpful to you.
Any updates or questions, please let us know.
If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Have you checked if the answer given by Andrew-6911 is helpful to you?
Any updates or questions, please let us know.