CODEWITHSUNDEEP
excel
Farhana
Farhana

Reputation: 169

Conditional formatting(custom format) with multiple conditions for a number in Excel

I want to format a number as M(million), B(Billion) or K(thousand) dynamically based on the value in the cell. I tried (but this does not work):

[>1000000000]0,,,"B";[>1000000]0,,"M";[>1000]0,"K";0”

If I give any two conditions it works, eg: 

[>1000000000]0,,,"B";[>1000000]0,,"M";0

or 

 [>1000000]0,,"M";[>1000]0,"K";0 )

Refer : https://www.sumproduct.com/thought/multiple-number-formatting.html

Upvotes: 4

Views: 6135

Answers (1)

agold
agold

Reputation: 6276

This article (third example) mentions that only two conditions in the formatting statement are allowed:

Custom number formats allow up to two conditions to be specified. This is because only four sections are allowed for custom number formatting and two are reserved. The fourth section always specifies text formatting and one other section is required to detail how ‘everything else’ (numerically) will be formatted.

And as mentioned in the Excel 2010 help:

A number format can have up to four sections of code, separated by semicolons. These code sections define the format for positive numbers, negative numbers, zero values, and text, in that order.

<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>

As an intermediate solution you could use the following VBA function I have written:

Function FormatNumber(val As Variant) As String
If IsNumeric(val) Then
    Dim NumVal As String
    NumVal = ""

    If val > 1000000000 Then
        NumVal = Str(val / 1000000000#) & "B"
    ElseIf val > 1000000# Then
        NumVal = Str(val / 1000000#) & "M"
    ElseIf val > 1000# Then
        NumVal = Str(val / 1000#) & "K"
    Else
        NumVal = Str(val)
    End If

    FormatNumber = NumVal
Else
    FormatNumber = val
End If
End Function

Which results in:

excel number format

First column is the original number, 2nd with the number formatting you proposed, and third with the VBA function FormatNumber. Note that the results of FormatNumber are Strings, so you cannot use them to calculate.

Upvotes: 2

Related Questions