Excel gurus - formula help please?

[Stan_Lite]

I have a spreadsheet where I can input my weight in kilos and output it in pounds (extremely simple task). I decided I'd like it to do it in stones and pounds instead.

After much searching on t'interweb, I found the following formula

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

This works fine but it rounds it to the nearest pound. I'd like it to round to the nearest half pound but I can't work out for the life of me how to do it

Help please

[Belmit]

Changing the 0's to 1's after the commas in the brackets will round to a further decimal place i.e. currently it's set to 0, so nothing after the decimal point (a whole pound)... if you change it to 1 it will round to 1 decimal place. Not sure how you'd make it round to the nearest half-pound though.

[sara]

Try this... it doesn't give you the stone separately, though.... Just done it now (I like these challenges!)

Stick headings of Kg and Lb in A1 and B1, input your Kg into A2, then stick this formula in B2:

=INT((A2*2.20462262))+IF(((A2*2.20462262)-INT((A2*2.20462262)))<=0.25,0,IF(((A2*2.20462262)-INT((A2*2.20462262)))>0.75,1,0.5))

[Mark]

Try...

=INT(ROUND(A2*2.204623,0)/14)&"st "&TEXT(MOD(ROUND(A2*4.409246,0),28)/2,"0.0")&"lb"

The rounding might be slightly off, but it seems to be within 0.1lb.

I did it by multiplying both of the numbers used in the calculation for lbs by two, and then dividing the result by two.

[Belmit]

Ah, I attacked the wrong 0's! I'm sure there's a clever way of coding it so it rounds to 0.5 but I doubt it would be a built-in function.

[Feek]

I did something like this before, I don't have Excel installed here so I can't check it exactly but I have an alternative so I can open the actual sheet and show me the formula I used although there might be some conversion oddities there.

In the cell which displayed my loss, I have the following:

=ROUND(CONVERT(G4,"kg","lbm")/14,0)&" st_x000D_"&MOD(ROUND(CONVERT(G4,"kg","lbm"),2),14)&" lb"

Which may or may not help?

[Rich_L]

Install the Analysis Toolpak and you can use the MROUND function I think, I'm just trying it now
*edit*

Ok yeah it works I think, bear with me while I try and get the formula right

[Stan_Lite]

Originally Posted by Mark

Try...

=INT(ROUND(A2*2.204623,0)/14)&"st "&TEXT(MOD(ROUND(A2*4.409246,0),28)/2,"0.0")&"lb"

The rounding might be slightly off, but it seems to be within 0.1lb.

I did it by multiplying both of the numbers used in the calculation for lbs by two, and then dividing the result by two.

Thank you, that seems to work fine Mark. I knew it would be something simple like that but I'm an Excel noob so couldn't get my head round it.

Thanks very much for all your help and suggestions folks

[Rich_L]

Ah yeah that's easier Do that, lol

[Stan_Lite]

Originally Posted by Rich_L

Ah yeah that's easier Do that, lol