excel number format - varying decimal digits

Question

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

Answer 1

I have a rather primitive solution by implementing a function in VBA:

Function formatStr(myVal As Double) As String
' the smaller the number, the more digits are to be shown
Dim retStr      As String
Dim absVal      As Double

    absVal = Abs(myVal)

    If absVal >= 100 Then
        retStr = "0"
    ElseIf absVal >= 10 Then retStr = "0.0"
    ElseIf absVal >= 1 Then retStr = "0.00"
    Else ' number is between -1 and 1
        retStr = ".000"
    End If

    formatStr = retStr
End Function

...which can be used then in a VBA statement like this :

   myVal = 0.5555: ActiveSheet.Cells(27, 4).Value = Format(myVal, formatStr(myVal))

if you want to use that in a sheets cell you need another little function

  Function FormatAlignDigits(myVal As Double) As Double
     FormatAlignDigits = Format(myVal, formatStr(myVal))
  End Function

=> you may enter =FormatAlignDigits(B27) in the cell where you want the result. B27 is the cell with the source data in this example.

My test results:

100.3 => 100
100.5 => 101
10.53 => 10.5
10.55 => 10.6
1.553 => 1.55
1.555 => 1.56
0.5553 => 0.555
0.5555 => 0.556
-0.5555 => -0.556
-0.5553 => -0.555
-1.555 => -1.56
-1.553 => -1.55
-10.55 => -10.6
-10.53 => -10.5
-100.5 => -101
-100.3 => -100

I'm sure there can be a more fancy solution, e.g. with a parameter for function 'formatStr' that tells at which power the number of digits is down to 0 (here: power = 2; meaning if the value is >= 100). For me this primitive thing works just fine.

Answer 2

I've recently solved this issue but configuring up to 6 decimals. The request is as follows:

We want to display a number same way as entered by the user but allowing no more than 6 decimals:

• 1 -> 1
• 1,1 -> 1,1
• 1,12 -> 1,12
• ...
• 1,123456 -> 1,123456
• 1,1234567 -> 1,123457 (Round from this point)

This can be solved using conditional formatting and one rule per decimal starting from integers. An integer is a number without decimals, so X MOD 1 = 0. We can apply this logic multiplying the number by 10^N being N the number of decimals we want. Besides, we want to stop applying rules when we have detected the number of decimals in the cell value, so be sure you mark "Stop if True" flag and that the order of the rules is from the most restrictive one (the integer) to the less restrictive (the one that stands for the maximum number of decimals allowed).

Summarizing, you have to configure the following way (in the sample I'm doing for B column):

• INTEGER: =MOD(B2;1)=0
• ONE DECIMAL: =MOD(B2*10;1)=0
• TWO DECIMALS: =MOD(B2*100;1)=0
• ...
• SIX DECIMALS: =MOD(B2*1000000;1)=0

Also set the default format of the cell to be a number with the maximum number of supported decimals:

And finally the working results on Excel:

Answer 3

Under the Home tab in the Ribbon select Conditional Formatting:

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!):

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.