How do I format a number

[lrubins1]

Hi,
I am looking to format a column of numbers - this should be easy -

If the equation = 0 I want to print a blank
If the equation = number I want to have 1 decimal place
I have tried the easy ##.# but it of course prints the decimal point.

Thanks!!!

 

[xlbo]

Try doing it in the formula
=if(Formula_Result = 0, Text(Formula_Result,"0"),Text(Formula_Result,"0.0"))

This will turn values to text however.....but it's the only way of doing it without code - otherwise all numbers will follow formatting conventions

Alternatively - dependant on the formula, you could turn just the 0 to text

=if(Formula_Result = 0, "0", Formula_Result)

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

Hi lrubins1,

Of course "##.#" prints a decimal point but you say you want 1 decimal place - how do you expect it to show?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[asrisk]

Another way is to simply format your cell to display one decimal place, and then go to Tools/Options/View and un-check the "Zero Values" checkbox. That way, any zeroes on your sheet will be displayed as blanks.

HTH,
Andy.

-------
I am not responsible for any "Sponsored Links" which may appear in my messages.

 

[Callan101]

asrisk has the simplest and best answer, but if the cells are calculated remember to round the answer (=round). Otherwise you could end up with a minute fraction, eg 0.00000001234, which is too small to be significant but still makes the result technically non-zero. The display will then show 0.0, in line with the formatting, and you'll wonder why when it's set not to display zeros!

 

[GlennUK]

asrisk has the simplest solution, yes, except if you only want a part of spreadsheet to have blank zeroes, this won't do.

Take note of what Callan101 says, as this is true no matter what solution you choose.

One more thing, another solution is to use a custom number format for these cells.

Create a custom number format of
#,##0.0;-#,##0.0;;
and the zeroes will be shown as blank for the cells that this is applied to.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.