Custom Number format...

[hbwhitehead]

Would someone please advise me on a simple task?

If we have a list of numbers as such:
1.50
1.25
1.00

What is the format I can use to illustrate that following...
a) If there is .00 after then don't show the decimal or the zeros.
b) if there is .50 after then don't show the zero
c) if there is .25 after then show both numbers.

Thanks for your input.

 

[Joe4]

The General Format will do that.
The General Format drops all trailing zeroes from decimals, by default.

 

[hbwhitehead]

Thanks! When using "General" format, how do you make sure the formatting of has a "," for the 4 digit numbers?

 

[Joe4]

ow do you make sure the formatting of has a "," for the 4 digit numbers[/COLOR]

Ah, a new wrinkle you didn't mention in your original question!

This is much trickier, as if you choose anything other than General, they want you to specify exactly how many decimals you want.
However, we can use Conditional Formatting to do this. We can set up three rules.
This example if for column A (so select column A and add the following Conditional Formatting conditions):

Rule 1, for no decimals
Conditional Formatting formula: =A1=ROUND(A1,0)
Format to choose: Number with comma and no decimals

Rule 2, for no decimals
Conditional Formatting formula: =A1=ROUND(A1,1)
Format to choose: Number with comma and one decimal

Rule 3, for no decimals
Conditional Formatting formula: =A1=ROUND(A1,2)
Format to choose: Number with comma and two decimals

Note that if you enter the conditions in this order, Excel will reverse them (it always put the last condition entered at the top).
So you will need to re-order them (by going to the "Manage Rules" option, and using the arrows to move them up and down).
You also want to check the "Stop If True" check box after each rule.

 

[RoryA]

If you only have two decimals to worry about, you can just use a format like
#,##0.##
but that will still give you the decimal point for integers, so you'd need a CF to apply for that situation.

 

[Joe4]

but that will still give you the decimal point for integers,

Yep, that is why I chose the method I did.

a) If there is .00 after then don't show the decimal or the zeros.

 

[RoryA]

I was really intending to point out that you only need one CF rule, not 3.

 

[Joe4]

I was really intending to point out that you only need one CF rule, not 3.

Ah, gotcha. I see where you are going with that now.
Sorry, rough Monday morning. Still trying to get the caffeine IV drip working...

 

[RoryA]

I can relate - our coffee machine was out of order this morning!!

 

[Joe4]

I can relate - our coffee machine was out of order this morning!!

It must have been chaos!!!