Excel lbs to stones and lbs

  Graham ® 10:25 10 Mar 2005

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 2005

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

  Graham ® 10:54 10 Mar 2005

Thankyou very much.

  Simsy 12:04 10 Mar 2005

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!



  Graham ® 12:13 10 Mar 2005

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 2005

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?



  Simsy 12:45 10 Mar 2005

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,



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

Click bottom right.

  Simsy 13:01 10 Mar 2005

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?



  Graham ® 13:09 10 Mar 2005

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.

Elsewhere on IDG sites

OnePlus 5T review: Hands-on

Illustrator Andrés Lozano on his improv line work, brazen use of colours & hand sketching

iPhone X review

Comment envoyer gratuitement des gros fichiers ?