Question on Custom Number Formats - Displaying Financials in Thousands

[jonnybruin]

Hello,

I have question on displaying numbers in thousands for presenting financial information. I prepare the spreadsheets using source data without rounding, and would then like to prepare the results in thousands on select pages. I understand the basics of how to display figures in thousands, and I created a macro to display numbers with the following custom format code:

_(* #,###,_);_(* (#,###,);_(* "-"_);_(@_)

Original Table
981 - (981)
94,087 - (94,087)
(328,188) - 328,188
10 - (10)
15,316 - (15,316)

Displayed in Thousands with Custom Format
1 - (1)
94 - (94)
(328) - 328
- ()
15 - (15)

The issue I am is that items less than zero are now either showing up as blank for positive figures rounded to less than one thousand, and "()" for negative figures rounded to less than negative one thousand. I would like to retain the parentheses for all negative numbers, but is there a way to change how figures rounded to less than +/- 1 thousand are displayed? Ideally, I would like these figures to display as a "-", since it would be cleanest and consistent with formatting elsewhere.

I've searched around online and asked around the office, but cannot find a solution here. Any help you have here would be greatly appreciated.

Thank you.

 

[pgc01]

Hi jonny
Welcome to the board

If you have excel 2007+ then you can use conditional formatting and add that format for numbers between -1000 and +1000.

 

[jonnybruin]

Hi PGC,

But if I do that and have numbers like 4,000 and 440 - wouldn't conditional formatting be applied to only numbers greater than 1 thousand change the display to 4 & 440? Thanks for the advice.

 

[pgc01]

Hi

No.

You leave your number format as you have it.

You add a conditional format for the numbers -1000 to 1000.


For ex.
I have in cell the format

0

Now I add a conditional format for number bigger or equal to 1 million:

0.000,,"M"

For the numbers

12
12345
1234567

the display is

12
12345
1.235"M"

In the first 2 cases it uses the cell default number format. Only in the third case, since the number is bigger than 1 million, does he use the conditional formatting number format.

Remark:
In your case, for the numbers between -1000 and 1000 you could use (if I understood correctly):

"-";"(-)"

 

[jonnybruin]

Okay, thank you.

I have a macro to change the number format to round to thousands, and I then can apply conditional formatting so that anything for example '=AND(A1<500,A1<-500) changes to a custom number format "-";"-" as you stated. Is there anyway that I can add this layer of conditional formatting to selected cells so this is a one step process? Thank you so much for all of your help.

The macro I currently have is very simple:

Selection.NumberFormat = "_(* #,###,_);_(* (#,###,);_(* ""-""_);_(@_)"
End Sub

When I try to record the conditional formatting as the subsequent step I keep getting a debugging error on the line starting with 'ExecuteExcel4Macro...':
Sub ThousandsConditional()
'
' ThousandsConditional Macro
'

'
Selection.NumberFormat = "_(* #,###,_);_(* (#,###,);_(* ""-""_);_(@_)"
Selection.NumberFormat = """- "";""- """
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(N12<500,N12>-500)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
ExecuteExcel4Macro "(2,1,""""- "";""- """")"
Selection.FormatConditions(1).StopIfTrue = False
End Sub

If this is an easy fix could you please help me out? Thank you so much, I really appreciate it.

 

[pgc01]

Hi

This works for me:

Sub ThousandsConditional()
Dim r As Range, sR_1_1 As String

' example: set format to range C4:J8
Set r = Range("C4:J8")
sR_1_1 = r.Item(1, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)

r.NumberFormat = "_(* #,###,_);_(* (#,###,);_(* ""-""_);_(@_)"
    
With r.FormatConditions
    .Delete
    .Add _
        Type:=xlExpression, _
        Formula1:="=(" & sR_1_1 & ">-1000)*(" & sR_1_1 & "<1000)"
    .Item(1).NumberFormat = """-"";""(-)"""
End With

End Sub

 

[jonnybruin]

Thank you so much for your help with this!

 

[pgc01]

You're welcome. Thanks for the feedback.