Search code examples
excelformattingcell

excel number format - varying decimal digits

I'm trying to set a special cell number format with theses rules:

  • display percentage
  • display at max 3 digits (decimal + integer part)

So I can display 100% or 99.3% or 1.27% but not 100.9% or 100.27% or 99.27%.

Of course, I can have negative number (-27.3%) and it does not affect my rules.

I've try with the cell formating option without success:

[<1]0.00%;[<10]0.0%;0%

Because it seemed that excel (2010) does not support more than 2 conditions in cell formating (and so I can't expand it to manage negative number...)

It there anyway to do what I want?

Thanks

Solution

  • Under the Home tab in the Ribbon select Conditional Formatting:

    Conditional tile

    and then select either New Rule or Manage Rules (with the latter you can then select New Rule, but also have an overview of all current rules)

    Then select Use a formula to determine which cells to format and enter the formula with reference to the cell itself (in my case cell A1 was selected, take the $ signs out to allow it to be applied to other cells themselves as well!):

    New Rule

    Now Click Format... and select the required Number format as percentage with the number of decimal places as you want it.

    Repeat this for all the cases you want to distinguish.

    As values can be negative I use ABS() to always test for the rule on the absolute value of the cell.

    Note you can either make all rules apply for a 2 side limited value range (in my example I have the minimum of 0.1 and the maximum of 1 (10% and 100% respectively). Alternatively you can only determine the minimum OR maximum and tick the box for Stof If True at the right end for all your rules involved.