0

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.

  • try =FIXED(10,2) – strobelight Sep 26 '16 at 1:17
  • Just changing that doesn't work. After applying it, it shows 10.00 kg. – alejnavab Sep 26 '16 at 1:53
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

enter image description here

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")))

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.