Number Formatting Question

[PaulOPTC]

Good Afternoon,

I am looking to format some of the number in my sheet and I cant seem to get the correct combination of formatting, maybe there is something that I am missing.

I want them to have a $ in front.
I want them to have a comma for the 1,000 and above
and I want them to up to three decimal places, but only if it needs it, if it is a whole number there is no need to add the extra 0s.
And I would like any 0s in the data to be empty.

For Example:

One dollar - $1
one dollar and fifty cents - $1.5
one thousand dollars and fifty cents - $1,000.5
one thousand dollars and fifty five and a half cents - $1,000.555

Here is my current custom Format, it is very close to what I would like but it does not add the comma

$General;-$General;;@

this format is also very close, it just adds an extra . at the end of any whole number ( like $123 would be $123. )

$####.##;-$####.##;;@


Thank you for your help!

 

[johnny51981]

You'll actually want to do this as 2 conditional formatting rules, each being in the "Use a formula to determine which cells to format".

Note: Cell Reference A1 is the piece you will want to adapt to fit the column you are wanting to format.
The first one will be =MOD(A1,1)=0 then Change the Format to Currency, Show Dollar Sign, 0 decimal places.
The second will be =MOD(A!,1)<>0 then Change the Format to Custom, $#,###.###

Then copy paste format to the cells/columns that you would like to follow these rules.

 

[PaulOPTC]

You'll actually want to do this as 2 conditional formatting rules, each being in the "Use a formula to determine which cells to format".

Note: Cell Reference A1 is the piece you will want to adapt to fit the column you are wanting to format.
The first one will be =MOD(A1,1)=0 then Change the Format to Currency, Show Dollar Sign, 0 decimal places.
The second will be =MOD(A!,1)<>0 then Change the Format to Custom, $#,###.###

Then copy paste format to the cells/columns that you would like to follow these rules.

Maybe I did it wrong, its really really close but not perfect.
One thousand = $1,000 which is perfect
One thousand and fifty five cents = $1000.55 (No comma)

If this is as close as we can get it, I will live without the comma for the fringe times where it will be over 1000, with cents

So I appreciate your help and I will apply this to the rest of my sheet!

 

[PaulOPTC]

Maybe I did it wrong, its really really close but not perfect.
One thousand = $1,000 which is perfect
One thousand and fifty five cents = $1000.55 (No comma)

If this is as close as we can get it, I will live without the comma for the fringe times where it will be over 1000, with cents

EDIT: No clue what happened but I saved and opened the sheet back up and now it is working as intended!

I made a slight modification on it, instead of

"The first one will be =MOD(A1,1)=0 then Change the Format to Currency, Show Dollar Sign, 0 decimal places."

I made it a custom: $#,##0_);($#,##0);;
so It wouldnt show any 0s for me as $0

So I appreciate your help and I will apply this to the rest of my sheet!

 

[johnny51981]

Awesome, I'm glad you got it to work out the way you needed!

 

[PaulOPTC]

Awesome, I'm glad you got it to work out the way you needed!

For sure, thank you! The MOD formula is something I would have never thought of!