If x.0 do not show decimal or zero; if x.5 show on worksheet

[janljan]

Hello,

I have a summary worksheet with calculation from another worksheet. There will be one to several digits to the left of decimal. There will either be a zero or 5 after the decimal.
If xx.0, I do not want to see the decimal or zero. Is this possible?

TIA.
Regards,
Jan

[MrShorty]

A number format code of 0.# will not show the 0, but will show the decimal point (13.0 will display as 13.). In order to not show both the zero nor the decimal point (to display 13.0 as 13) you will need to use conditional formatting. Your conditional formatting will test for "integer" (probably something like =INT(A1)=ROUND(A1,1)) If your test for integer is TRUE, then format code is 0. If your test for integer is FALSE, then format as 0.0.

https://support.office.com/en-us/art...B-F1951FF89D7F

[janljan]

Thank you. For now I guess I will use number formatting as 0.#. I tried the integer in conditional formatting and if true to format cell as white. It formatted the whole cell as white. Yes, I did change cell reference to my specific cell on worksheet.

Kind regards,
Jan

[Alf]

You could try a formula like this in cell B1 if the value you wish to change is in cell A1

=IF(A1-INT(A1)=0.5,A1,INT(A1))

Alf

[janljan]

Thanks for the tip, but I don't want to add any more columns. I would then have to hide the other column that shows 0.0. This is going to be used every one my manager and I can't have them trying to hide columns, etc.

Regards,
Jan

[janljan]

Per attached, I entered the formula =INT(A1)=ROUND(A1,1)) noted above under conditional formatting, but it gives me an error message.

[MrShorty]

It does not give me an error message. What is the error message?

[janljan]

Hi Mr Shorty,

See attached error message. Please note I changed column/cell from a1 to d4, etc to match my actual date.

Kind regards,
Jan

[MrShorty]

I don't see anything wrong with the formula, unless your Excel installation/language setting specify to use semicolon ; instead of comma , to delimit formula arguments [ROUND(D4;1)]. Does that formula work in a spreadsheet cell?

[janljan]

Hi Shorty,

The language is not the problem because when I use the semi-colon, I get an error.
I put =Round(d4,1) and have attached the result. I still get 11.0.
Please noted that all these cells are calculating data from another worksheet(same workbook). Could that be the problem?

Regards,
Jan

[vlady]

you have extra Close parenthesis in the round (referring to post #6)

[ChemistB]

Hello,

I have a summary worksheet with calculation from another worksheet. There will be one to several digits to the left of decimal. There will either be a zero or 5 after the decimal.
If xx.0, I do not want to see the decimal or zero. Is this possible?

I'm confused. If you format the cells to GENERAL, then values like 24.5 will appear as 24.5 and 24.0 will appear as 24 (no decimal point). Is that what you are looking for?

[janljan]

Hello,

In regards...."I'm confused. If you format the cells to GENERAL, then values like 24.5 will appear as 24.5 and 24.0 will appear as 24 (no decimal point). Is that what you are looking for? "
Yes, that is what I am looking for but If I use general, the cells that have a value of 0 show as 0. If the calculation result is 0, I don't want to see the zero on worksheet.

TIA.

Regards,
Jan

[ChemistB]

Try this custom format
Select the cells in your range>Format cells> Number>Custom
General;-General;
zeros will not appear. decimal points will only appear as necessary

[janljan]

Fantastic! That was perfect.

TIA
Jan