How can you format batting averages?

[Wade M]

In baseball or softball, a batting average is calculated by dividing hits by at-bats. It is displayed as .333 or .400 or .025 or, if perfect, 1.000. However, a standard calculation yields a result such as 0.333 or 0.400, etc. Is there a way in Excel to format the calculation such that it drops the initial 0 in front of the decimal point? If so, is there a way to also allow the 1 if the batting average is 1.000? This one has been driving me nuts for a long time. Thanks for your help.

Wade in San Diego

 

[maxflia10]

In baseball or softball, a batting average is calculated by dividing hits by at-bats. It is displayed as .333 or .400 or .025 or, if perfect, 1.000. However, a standard calculation yields a result such as 0.333 or 0.400, etc. Is there a way in Excel to format the calculation such that it drops the initial 0 in front of the decimal point? If so, is there a way to also allow the 1 if the batting average is 1.000? This one has been driving me nuts for a long time. Thanks for your help.

Wade in San Diego

Custom format to .000

 

[BK8]

How about multiplying by 1000 and then chopping off the decimals?

 

[parry]

Welcome to the board. What you need here is conditional custom number formating. You want to display a number to the left of the decimal (an integer) but only if the number = 1 (1000 ave). Im presuming theres no supermen that can bat better than 1000

To do this, select Cells-Format and in the Custom format option enter this...

[=1]0.000;[<1].000;General

This means if the value is equal to 1 then put the format as 0.000 (ie 1.000 would be the result). If the value is <1 then its formated without the integer (ie 0.5000 would be .500).

regards
Parry

 

[just_jon]

Try --

#.000

where the pound sign is used only as required.

 

[parry]

Try --

#.000

where the pound sign is used only as required.

Doh, missed the obvious!