4

I want to set the number format as below:

If the number have more than 2 decimals, round it to 2 only (e.g. 12.3456 -> 12.35).

If the number is whole number, just let it be (e.g. 12 -> 12).

3

Using the custom number format 0.## achieves most of what you want: the insignificant 0s will not appear after the decimal dot. However, the decimal dot itself will appear, as it's a part of the format. So, you'll have 12.3456 appearing as 12.35 and 12 appearing as 12. (with the dot).

In a way, this trailing dot is a feature because otherwise, 12.001 would be rendered simply as 12 (without dot) which can be misleading in some contexts. The trailing decimal dot is customarily kept to indicate that some rounding may have taken place.

I don't think it's possible to get rid of the trailing dot without a script. (In Excel, conditional formatting can be used for this, but the conditional formatting in Google Sheets does not support applying date/number formats: only colors and such.)

If you really want to get rid of the dot, here is a script for that (put in Tools > Script Editor):

function onEdit(e) {
  if (e.value == Math.floor(e.value)) {
    e.range.setNumberFormat("");
  }
  else {
    e.range.setNumberFormat("0.##");
  }
}

Note this formats everything in the spreadsheet as it's edited. You may want to be more restrictive, e.g.,

function onEdit(e) {
  if (e.range.getSheet().getSheetName() == 'Sheet1' && e.range.getColumn() == 2 && e.range.getRow() >= 2) {
    if (e.value == Math.floor(e.value)) {
      e.range.setNumberFormat("");
    }
    else {
      e.range.setNumberFormat("0.##");
    }
  }
}

This formats only the second column of Sheet1, beginning with the second row.

By the way: 0.## vs #.## is a matter of taste: I prefer 0.75 to .75, not omitting the 0 in front.

1

To build to the other answer, here is a way to set it based on a specific cell changing:

function onEdit(e){
  if(e.range.getA1Notation() == '*cell that's changing*' && 
      e.range.getSheet().getName() == '*sheet the changing cell is on*'
    ) 
    if (e.source.getRange('*range of value to be formatted*') == Math.floor(e.value)) {
      e.source.getRange('*range of value to be formatted*').setNumberFormat("#,##0");
    }
    else {
      e.source.getRange('*range of value to be formatted*').setNumberFormat("#,##0.##");
}
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.