How to Change Number Format of a Cell Based on Its Value-CodePudding

I have values in column B that cahnge based on the ticker present in column A

1 |VIX   |     24.14
2 |NQ    |    12,141

this can become this

1 |SPX   |  2,000.50
2 |US10Y |         0

Every time I change to a ticker with different precision, I have to manually modify the Number Format to accomodate the best visualisation. For instance with the change above the new Number Format should become:

1 |SPX   |     2,000
2 |US10Y |     0.005

How can I make a custom formula or script that changes the Number Format based on how big the number is? In a googlesheet pseudo script language it should be something like:

changesNumberFormat(cell) {
  let value = cell.value
  if (value > 100) {
    cell.numberFormat("#,##0")
  } elseif (value < 0.1) {  
    cell.numberFormat("0.###")
  } else {cell.numberFormat("0.#")}
}

I told it would have been something straight forward but I can't evenunderstand how to take the value of the cell where the formula is applied to.

CodePudding user response：

Script doesn't seem to be necessary. You can set the number format for the entire data range with meta instructions:

[>100]#,##0;[<0.1]0.###;0.#

CodePudding user response：

If you change the values manually you can format them with onEdit() trigger this way:

function onEdit(e) {
  if (e.range.columnStart != 1) return; // if it's not column A do nothing

  var value = e.value;
  var cell = e.range;
  if (value > 100)      cell.setNumberFormat("#,##0");
  else if (value < 0.1) cell.setNumberFormat("0.###");
  else                  cell.setNumberFormat("0.#");
}

It changes formatting in column A. Perhaps it makes sense to limit the script to work on just one sheet only.

It doesn't work if the values are changing not manually.

Update

Here is the example how to set formatting for a range:

function changesNumberFormat_for_range() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange('A1:B10');
  var values = range.getValues();

  for (let row in values) for (let col in values[row]) {
    var value = values[row][col];
    var cell = sheet.getRange(1   row,1   col);
    if (value > 100)      cell.setNumberFormat("#,##0");
    else if (value < 0.1) cell.setNumberFormat("0.###");
    else                  cell.setNumberFormat("0.#");
  }
}

But it can work very slow for big ranges since it calls getRange() for every single cell in the range.