Google Sheets – How to Fix Circular Dependency Error

formulasgoogle sheets

I have a cell (games played) that adds 3 variables (wins, loses, and draws), and doesn't display anything when they have no value.

=IF(D5+D6+D7 = 0, "", D5+D6+D7)

My variable wins (and the others when I add code them) detect when the word Wins appears in the same column as it. It also doesn't display anything when it has no value.

=IF(SUM(COUNTIF(D10:D29, "*Win*")) > 0, SUM(COUNTIF(D10:D29, "*Win*")), IF(D8 > 0, 0, ""))

I want it to display a 0 only when games played is more than 0 (so I don't have a bunch of zeros on the sheet) After making it display a 0 when games played is more than 0, it started giving me Circular Dependency Error. How can I fix this?

Here's a link to my file: Sample sheet

Best Answer

cell D5:

=IF(SUM(COUNTIF(D10:D49, "*Win*"))>0, SUM(COUNTIF(D10:D49, "*Win*")),
 IF(D8<>"", 0,))

cell D11:

=IF(D10>D2,"Win: "&D10&" +"&D10-D2&" SR",
 IF(AND(D10=D2,D2>0),"Draw: "&D10&" "&"+0 SR",
 IF(AND(D10<D2,D10>0),"Lose: "&D10&" "&D10-D2&" SR","")))

Circular Dependency Error was generated, because cell D11 was returning FALSE

Related Solutions

Google Sheets Formulas – Fixing Circular Dependency with IFERROR

The pure spreadsheet answer is to create columns D and E to display your information. An example row:

A3: text
B3: (enter USD if known)
C3: (enter LBP if known)
D3: = if( isblank(B3) ; C3/1500 ; B3 )
E3: = if( isblank(C3) ; B3*1500 ; C3 )

The other way you can solve this is with JavaScript, if you know it, using the onEdit trigger. In that case the JavaScript would detect the edit to B3 and automatically add the formula in C3, or vice versa.

Google Sheets – How to Execute Function Only Once to Prevent Circular Dependency

With formula's it can't be done, as per your other question, but with a little piece of Google Apps Script you can roughly accomplish what you want.

Code

// global
var app = SpreadsheetApp.getUi();

function onOpen() {
  // create menu entry
  app.createMenu("IDs").addItem("Set all IDs", "myIDs").addToUi();
}

function myIDs() {
  // set variables
  var sh = SpreadsheetApp.getActive();  
  var rText = app.prompt("Give range", "Like A1:A10 (single column only)", app.ButtonSet.OK_CANCEL); 
  var r = sh.getRange(rText.getResponseText()), v = r.getValues(), max = 0;

  // retrieve max value
  for(var i = 0, iLen = v.length; i < iLen; i++) {
    var value = v[i][0];
    if(value > max) {
      max = value;
    }
  }

  // prepare output
  var output = [];
  for(var j = 0, jLen = v.length; j < jLen; j++) {
    var val = v[j][0];
    if(val == "") {
      output.push([max + 1]);
      max++;
    } else {
      output.push([val]);
    }
  } 

  // show/return results
  r.setValues(output);  
}

Explained

From the menu, the script is activated. A prompt is shown and a range is requested. The script will retrieve all data from that range and find the highest value. Afterwards it will evaluate the range for empty entries. Once found, it will add a value (max + 1) to that position and re-set the maximum value. At the end of the script all data is added new data to the spreadsheet.

Screenshot

before & after
enter image description here

Example

I've created an example file for you: check if cell itself is empty
Add the script under Tools\Script editor from the menu. Press the bug button to authenticate the script.