How can I create a custom number format with all of these criteria?

[jno207]

Hi,

I am unable to figure out how to create a customer number format for my spreadsheet that will meet the following criteria:

1) Millions display as thousands. E.g., $5,000,000 would display as 5,000

2) Number has a dollar sign sometimes (I only want a $ in front of my subtotal numbers - for the others no dollar sign)

3) Negative numbers are displayed with a standard accounting format (e.g. -$4 would be displayed as $ (4)

4) Zero values are displayed with a - (I don't want a $ in front of the - , just a - )

Can ANYONE please help with this? I have wasted so much time trying to come up with a custom number format in Excel that will meet all of these conditions and can't figure it out.

Thanks!

 

[mvptomlinson]

To display millions as thousands, you can use the format "0,". To display negative numbers as "$(4)" you can use "$(0,)" assuming the millions-as-thousands rule still applies. If not, remove the comma. To display zero values as "-", just use the format -. I'm not aware of a way to only sometimes use a $ in subtotals, though. If you format the cell as Custom and use:

0,;$(0,);-

you should get 1, 3 and 4 covered. The semi-colons separate the different value types to be formatted, e.g.

Positive_Numbers ; Negative_Numbers ; Zeros ; Text

I left the Text format out since we're just dealing with numbers here.