# Formula to convert kilograms to stones & pounds

1. Sorry, on the file you sent and when i copy it into my spreadsheet, this message is displayed in the cell '#NAME?'  Register To Reply

2. Picked the wrong one to adapt then. If your getting the #NAME? error this is because you need to install and load the Analysis ToolPak add-in.

How?

In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
If necessary, follow the instructions in the setup program

VBA Noob  Register To Reply

3. or try

=IF(C5="","",IF(ROUND(MOD(C5*2.203,14),0)=14,ROUNDDOWN(C5*(2.203/14),0)+1&" st 0 lbs",ROUNDDOWN(C5*(2.203/14),0)&" st "&ROUND(MOD(C5*2.203,14),0)&" lbs"))

VBA Noob  Register To Reply

4. Last thing is the problem I said about earlier, shown below:

The second problem I need help with is as follows: i have two cells showing the difference between a start weight and current weight. One shows the weight in 'kg', it was simple '=SUM(E6-E5)', but the other is the 'st/lbs' cell, is there a formula that can take the current weight 'cell F6' from the start weight 'cell F5' and display it in the format as the conversion formula does '12 st 1 lbs'.

I had this response but now sure what it means exactly.

'for the second question the same, but refer to the new column in the If test and the formula.'

I tried just applying the formula you just did for me to the kg difference cell but that gives strange results.  Register To Reply

5. Originally Posted by britlizard
The second problem I need help with is as follows: i have two cells showing the difference between a start weight and current weight. One shows the weight in 'kg', it was simple '=SUM(E6-E5)', but the other is the 'st/lbs' cell, is there a formula that can take the current weight 'cell F6' from the start weight 'cell F5' and display it in the format as the conversion formula does '12 st 1 lbs'.
You can solve your first problem with a shorter formula (without any weights being shown as 10 st 14 lbs etc.), i.e.

=IF(A1="","",INT(ROUND(A1*2.204623,0)/14)&" st "&MOD(ROUND(A1*2.204623,0),14)&" lbs")

where your weight in kgs, is in A1

for your second question, are E6 and E5 both weights in kg? Will E6 always be greater than E5? If not do you want to show a negative amount? If so then to show a negative amount when E5>E6

=IF(OR(E5="",E6=""),"",IF(E5>E6,"-","")&INT(ROUND(ABS(E6-E5)*2.204623,0)/14)&" st "&MOD(ROUND(ABS(E6-E5)*2.204623,0),14)&" lbs")  Register To Reply

6. Thanks, that worked great! and thanks to everyone else who helped.   Register To Reply

7. Originally Posted by britlizard
Thanks but this formula doesnt work right, if i put in '75.1' i get '11 st 1 lbs', then '75.3' gives '11 st lbs' - no number for pounds, and it keeps showing either a '1' for lbs or nothing until it gets to 75.9 then it jumps to '12 st lbs'.
correct, formulae should not be done in a hurry, correctly it was

=INT(ROUND(A1*2.204623,0)/14)&"st "&TEXT(MOD(ROUND(A1*2.204623,0),14),"0")&"lb"

however, no doubt VBA Noob has already provided a working version.

---  Register To Reply