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

Question

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.

Answer 1

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

[condition] Replaces the default positive, negative, or zero comparison of the section with another conditional expression. For example, [<100]”Low”;[>1000]”High”;000 will render the word “Low” for values below 100, “High” for values above 1000 and a three digit number (with leading 0s) for anything in between. Conditions can only be applied to the first two sub-formats and if a number matches more than one, it will use the first one it matches. If there is a third format, it will be used for "everything else", otherwise if a number doesn’t match either format, it will be rendered as all "#"s filling up the cell width. The fourth format is always used for text, if it exists.

Solution:

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