Google-sheets – How to Auto Calculate the Missing Column

google sheets

Let's say you have two columns that are related to each other by a simple formula. Perhaps Column A has monthly expenses and Column B has the expenses annualized. Now, for some rows you may want to enter the annual expense and have the monthly expense calculated, and other rows you may want to enter monthly expenses and have the spreadsheet calculate the annual expenses. One solution I have is as follows:

Column A Formula

=IF(NOT(ISBLANK(B3)),B3/12,)

Column B Formula

=IF(NOT(ISBLANK(A3)),A3*12,)

Ignore for the moment that I should probably be using ARRAYFORMULA which I only just discovered in searching for an answer to this question. The above two formulas will form a circular dependency.

Best Answer

=IF(NOT(ISBLANK(B3)),B3/12,) and =IF(NOT(ISBLANK(A3)),A3*12,) will not work the in the manner you are thinking because, initially both cells, A3 and B3 are blank, therefore both forumlas will try to calculate a value which will make them non-blank; which means the formulas should not calculate a value, which means, …

Instead of ISBLANK() use ISFORMULA(). This will test the content of the target cell for a formula, not simply whether the displayed content is blank. Entering a literal numeric value:

=IF(NOT(ISFORMULA(B3)),B3/12,)

and

=IF(NOT(ISFORMULA(A3)),A3*12,)

Note that your manually entered values must not be entered as a forumla. That is, enter your expense values as their simple numeric values

2.54

and do not prefix them with and equal (=) character—an equal prefix signifies a formula.

=2.54

This will cause the NOT(ISFORMULA(…) check to fail and no value will be calculated.

Related Solutions

Google-sheets – Extraction of integer value from dropdown list inside the same cell

This can be done with Google Script. An example is here.

function onEdit(e) {

var ss = e.source;
var s = ss.getSheetByName("Testing");
var r = e.range;

  // to let you modify where the action and move columns are in the form responses sheet
var actionCol = 1;

  // Get the row and column of the active cell.
var rowIndex = r.getRowIndex();
var colIndex = r.getColumnIndex();
  // Get the number of columns in the active sheet.
  // -1 to drop our action/status column
  //var colNumber = s.getLastColumn()-1; //not needed because we are limiting the edit to a single row and cell

var curCell = s.getRange(rowIndex,1, 1, 1); //the same cell that has dropdown but can be a different one

if (e.value !== "" && colIndex == actionCol) {
var curCellValue =  curCell.getValue();
var curCellLeft = curCellValue.split(" ");
var val = curCellLeft[0];
//is it a number?
var matches = val.match(/\d+/g);
if (matches != null) {
 curCell.setValue(val);
}
}
}

Google-sheets – How to calculate a percentage with a circular reference

Google Sheets now support calculations based on circular references, if the users allows them via the menu "Settings > Calculation". These are resolved by an iterative computation, which is limited by the number of steps and desired precision.

Source: New iterative calculation settings and more in Google Sheets

Best Answer

Related Solutions

Google-sheets – Extraction of integer value from dropdown list inside the same cell

Google-sheets – How to calculate a percentage with a circular reference

Related Topic