Excel lbs to stones and lbs

  Graham ® 10:25 10 Mar 05
Locked

VoG™ kindly gave me the formula some time ago, lost in the mists of time.

I have lbs in column B, and wish to display in stones and lbs in column C.

  VoG II 10:32 10 Mar 05

=INT(B2/14) &"st "&B2-14*INT(B2/14)&"lb"

  Graham ® 10:54 10 Mar 05

Thankyou very much.

  Simsy 12:04 10 Mar 05

with the formula shown, as it obviously works correctly, another version, slightly tidyier I feel is;

=INT(B2/14) & " St " & MOD(B2,14) & " lb(s)"


It uses the Mod function, which is a shorter way of getting the lbs than VoG™ has done.


Very obviously no slight intended!

Regards,

Simsy

  Graham ® 12:13 10 Mar 05

Thanks to you, also. Both of these work, but only in cell C1. If I drag C1 down, I get 0St 0lb(s).

Another problem is the lbs are to 14 decimal places.

  Simsy 12:39 10 Mar 05

are you getting the 0St 0lb(s) in C2 when there is a number of lbs in B2, or is B2 empty?

If it's empty then the result you're getting is what I'd expect. We can get rid of it with an "if" function if needed.

With regard to the decimal places I'm a little stumped. Both the INT and MOD functions work with whole numbers, (by definition), and I don't see it as a formatting issue as the "St" and "lb(s)" will effectively force the cell to not be a number.


Can you clarify, exactly, where the decimal is showing?

Regards,

Simsy

  Simsy 12:45 10 Mar 05

I think I've realised what you mean...

Do you mean that when you enter the lbs in column B that is shows in Col B to 14 dec places?

e.g. you type in 95 and it shows as 95.00000000000000

If so, then this is a formatting issue.

1)Select the cell(s) in question, or the "B" at the top of Col B, if you want this to apply to the whole column.

2)On the menu bar select Format>Cells

3)Make sure the "Number" tab is selcted.

4)Choose "number" and select the number of Dec places you want.

I hope this helps,

Regards,

Simsy

  Graham ® 12:49 10 Mar 05
  Graham ® 12:51 10 Mar 05

Click bottom right.

  Simsy 13:01 10 Mar 05

that we've both done the same thing. as a result, mine isn't that much tidier, but it should be this;

=INT(B2/14) & " St " & INT(MOD(B2,14)) & " lb(s)"

If forces the lbs to be an integer.

What formula do you have in cell C2?

It should be;

=INT(B3/14) & " St " & INT(MOD(B3,14)) & " lb(s)"


nb this will give the number of lbs as an integer. is this OK?


Regards,

Simsy

  Graham ® 13:09 10 Mar 05

OK, thats sorted the decimal places.

Now how do I get the formula to apply to the whole of column C? I remember grabbing the corner of C1 and dragging down, but that doesn't work at the moment.

This thread is now locked and can not be replied to.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

The Pantone Colour of the Year 2017 is Green

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…