# Change values to the right of a decimal point

• Hi All.

I have a value in cell A1 which is for example 140.25

This value represents a weight in lbs and oz

However, however, to make it easier for the user, I would like to see 140.4
This being a conversion of .25 of a lb to 4 ounces

The user would easier understand 140lb 4oz

Is this possible?

Thank you

• Re: Change values to the right of a decimal point

You could do something like this:

=INT(A2)&"lb "&INT(MOD(A2,1)*16)&"oz"

Best Regards,
Luke M
=======
"A little knowledge is a dangerous thing."

• Re: Change values to the right of a decimal point

Hi Luke

Appreciate the response.

Your suggestion works fine to the point of converting a known weight and a decimal oz, however this falls down if a user enters 140.13, where the .13 equates to 13oz rather than a decimal round of 2oz

I guess I need to decide how the input is to be entered and work from there first.

Is there a suggestion similar to your previous, where the user enters 140.13 as a true lb/oz weight where the output would be 140lb 13oz

I can then decide which way to proceeed?

Thank you

• Re: Change values to the right of a decimal point

You'd need a second piece of info, as either way you need someone of knowing if the input was just lbs, or lbs + oz. Let's say there was some indication in B2, you could do:

=INT(A2)&"lb "&INT(MOD(A2,1)*IF(A2="Mixed", 100, 16))&"oz"

So, if user inputs a mixed value (lbs + oz), then we change 140.15 into 140lbs 15oz. If user selects "single" (or whatever else you pick), formula outputs 140lbs 2oz (if you want 2.4 oz, remove the INT function on latter part)

Edit: For clarity, I'd probably setup your data input to have two cells clearly marked, one for "Pounds" and the other for "Ounces", and then user can choose one or the other, or both. Then your unit conversions will be a lot easier.

Best Regards,
Luke M
=======
"A little knowledge is a dangerous thing."

• Re: Change values to the right of a decimal point

Nice work and Im almost there

I have used some of your thoughts and a few of my own and trying different methods to acheive the correct outcome.

One last thing, is there a way to add a 2 cells which are round numbers and add the second cell after a decimal point?

I.E.
A1 = 100
A2 = 15

A3 = 100.15

Limitations...
The number in A2 cannot be entered as .15 for this is derived from another calculation
The number cannot be concatenated as this value is used in a chart and offset functions

Thank you

• Re: Change values to the right of a decimal point

Hello,

HTH

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Re: Change values to the right of a decimal point

Not really, the number in A2 needs to be a shown as per A2
If the number is 6 then your suggestion makes this .06 which is incorrect for my requirement

Appreciate the help

• Re: Change values to the right of a decimal point

OK ...then

To be tested in cell A3

=A1+(A2/(1&REPT(0,LEN(A2))))

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Re: Change values to the right of a decimal point

Awesome

Thats works great

Much appreciated

• Re: Change values to the right of a decimal point

Quote from jl2509;791326

Awesome

Thats works great

Much appreciated