3

I have a custom function provides results based on user input - once of these variables accepts either "Numerical" or "Percentage", which provides the data as standard number, or a percentage of the total.

I've tried looking at conditional formatting to change the number format based on the value of the above variable's cell, but wasn't successful. Is this possible in Google Sheets? Is there some kind of workaround?

2 Answers 2

4

Conditional formatting in Google Sheets does not involve setting number formats. To achieve the desired effect, you can use a script running on edit:

function onEdit(e) {
  if (e.range.getA1Notation() == 'A1') {
    switch (e.value) {
      case 'Whole Number':
        e.range.offset(1,0).setNumberFormat('0'); 
        break;
      case 'Percentage':
        e.range.offset(1,0).setNumberFormat('0%');   // or 0.##% to allow fractional percentages
        break;
      default:
        e.range.offset(1,0).setNumberFormat('0.##'); 
    }
  }
}

This script checks whether cell A1 was edited, and if so, formats the cell below it accordingly. e.range.offset(1,0) means: one row down, zero columns to the right of the edited cell.

1
3

I know this is an old question but it seems that Google has yet to implement number formatting for conditional formatting options. While user79865's answer achieves the desired result it involves people having to authorize the script.

My solution is much simpler.

Create a section with results with the numerical values and another section with results with percentage values. You can hide these after but format them the way you would like and then refer to them in an if statement. IF(A1="Numerical", B2, C2) where A1 is the drop down, B2 is the numerical value and C2 is the percentage value.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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