CODEWITHSUNDEEP
excelcircular-reference
Brethlosze
Brethlosze

Reputation: 1618

Using Single Iteration Circular References in Excel

Circular References in Excel are enabled by setting "Enable Iterative Calculation" at "Options/Formulas". A "Maximum Iterations" value of 1 could be an interesting choice, but there are still problems.

At some moments, I am in the urge to replace a wrong value in a specific cell CELL in a COLUMN with the value =1-TOTALS where TOTALS is the cell holding the result of several sheets, like =SUM(COLUMN).

Normally, I have Excel (>2013) worksheets within a structure where all columns sum the same quantity, e.g. 1000 values completing 100% with data from different datasets. Assume you want to stick with Circular Referentes, by not changing the workbook structure, of by calling a macro, or by calling the Solver each time.

I always end typing manually, tediously, repetitively, the proper value at CELL when that happens.

Problem

The Circular Reference adds a constant to the linked cells, and I don't find a clear way to "tie" any of the values to a given constant reference (in this case, easily, the final sum shall always be 1). If the maximum iteration is 1, it calculates correctly, but when calculating a second time, the drift starts.

Question

Is there some way to use a Circular Reference formula in CELL, allowing the Circular Reference to exist, enabling Iterative Calculations with a Maximum Iteration of 1, and most importantly, in some way fixing the 100% result at the end of the column, so the iteration do not drift?.

Upvotes: -1

Views: 1131

Answers (1)

Peter Pesch
Peter Pesch

Reputation: 643

You don't need a circular formula.

I would start by making a Name: LAST_ROW, and fill it with the appropriate (probably 1048576) number for the version of excel in which that worksheet was made.

And then you can simply use a formula. For instance, for cell K10 it would be:

=1 - SUM(OFFSET(K10,1-ROW(),0,ROW()-1,1)) - SUM(OFFSET(K10, 1, 0, LAST_ROW-ROW(), 1))

Explanation:

SUM(OFFSET(<this cell>,1-ROW(),0,ROW()-1,1)) gives the sum of everything above this cell SUM(OFFSET(<this cell>, 1, 0, LAST_ROW-ROW(), 1)) gives the sum of everything below this cell.

Upvotes: 1

Related Questions