How to connect values of cells through ratio?

Question

I'm looking for the best way to connect cells values through ratio by rows. When I enter new values in cells I want to get new value in other cells accordance with ratio. So Ratio1 between columns E and F, Ratio2 between columns F and G.

function onEdit(e) {
var cells1 = ["E2", "E3", "E4"]; // Cells to connection 
var cells2 = ["F2", "F3", "F4"];
var cells3 = ["G2", "G3", "G4"];
var ratio1 = ["H2", "H3", "H4"]; // Expected ratio between values of cells
var ratio2 = ["I2", "I3", "I4"];
var value = (typeof e.value === 'number' ? "" : e.value);
var sheet = e.range.getSheet();
var cell = e.range.getA1Notation();
k = cells1.indexOf(cell);
if (k != -1) {
sheet.getRange(cells2[k]).setValue(value);
sheet.getRange(cells3[k]).setValue(value);
}
k = cells2.indexOf(cell);
if (k != -1) {
sheet.getRange(cells1[k]).setValue(value);
sheet.getRange(cells3[k]).setValue(value);
}
k = cells3.indexOf(cell);
if (k != -1) {
sheet.getRange(cells1[k]).setValue(value);
sheet.getRange(cells2[k]).setValue(value);
}

Answer 1

If you are fixing the ratio between the three columns, then there is only a value that can be variable assigned. After that all the columns should updated to that parameter that in this case the edited cell value.

Also the way you are retrieving the modified cell is kind of complicated when you can just get the index of the modified cell (1-based array).

So with that you only need to get the column and row of the modified cell and then update the others cells with the ratios of that row.

function onEdit(e) {
  var value = (typeof e.value === 'number' ? "" : e.value);
  var sheet = e.range.getSheet();

  // Get the "Coordinates" of the edit
  var startColumn = e.range.getColumn();
  var startRow = e.range.getRow();

  // Get the ratios at the edited row
  var hRatio = sheet.getRange(startRow, 8).getValue();
  var iRatio = sheet.getRange(startRow, 9).getValue();


  if (startColumn == 5 && startRow > 1 && startRow < 5) {  // Edit on E column
    sheet.getRange(startRow, 6).setValue(value / hRatio); //Update F Cell 
    sheet.getRange(startRow, 7).setValue(value / hRatio / iRatio); // Update G Cell

  }
  if (startColumn == 6 && startRow > 1 && startRow < 5) {  // Edit on F column
    sheet.getRange(startRow, 5).setValue(hRatio * value); // Update on E cell
    sheet.getRange(startRow, 7).setValue(value / iRatio); // Update on G Cell

  }
  if (startColumn == 7 && startRow > 1 && startRow < 5) {  // Edit on G column
    sheet.getRange(startRow, 5).setValue(hRatio * iRatio * value); // Update on E cell
    sheet.getRange(startRow, 6).setValue(iRatio * value); // Update on F cell

  }

}

Hope this works for you