Show decimals only if the cell has while showing custom text in Excel

Question

I have a row of cells with numeric values. I'd like to produce the following effect:

---------------------------
|Cell value | Cell result |
---------------------------
|2          | 2 kg        |
|1.43       | 1.43 kg     |
|9.5764     | 9.58 kg     |
|3.2        | 3.2 kg      |
---------------------------

Basically,

• I want them to always show "kg" after the value that is entered.
• Any decimal value entered need to be rounded up to 2 decimal places; if there are no decimal places, it's going to show the whole number.

I've formatted cells to 0" kg"so it always show the kilograms.

As for showing only decimals if necessary, I've used the formula =ROUND(X, 2) on each cell to only show two decimals (X being the value I introduce).

Question: However when I type =ROUND(2.5, 2) it displays 3 kg instead of 2.5 kg. Why is it? Do you have other possible solutions?

Edit 1

I've changed the cell formatting to 0.##" kg" and it works just like I want. Now the problem is if I use =ROUND(10, 2) it displays 10. kg with that additional point right after the number.

Answer 1

With the numbers in column A, use the Round() function in column B and then format with Custom format "General k\g". See screenshot

Answer 2

One possible solution based on what I understand your requirement. Use FIXED function to first round it to 2 decimals and check for last three digits, if .00 use only integer portion and concatenate with 'kg' if last digit is zero round to only 1 decimal else round to two decimals.

Assuming your data is in column B, try this formula in column C

=IF(RIGHT(FIXED(B1,2),3)=".00",CONCATENATE(INT(B1)," kg"),IF(RIGHT(FIXED(B1,2),1)="0",CONCATENATE(FIXED(B1,1)," kg"),CONCATENATE(FIXED(B1,2)," kg")))