Formula to change 'Stones - Pounds' to Pounds?

[papillon]

Hi All,
Does anyone know of a formula to change imperial stones and pounds to just pounds eg 13 stones 3 lbs = 185lbs ? (14 pounds to a stone)
Thanks,
papillon

 

[papillon]

Hi just_jon,
Yes, I'm afraid it does, but it's not the end of the world,
thanks,
papillon

 

[just_jon]

Both of the proposed solutions work for me --

Book2
	A	B	C	D
1	Stones	Pounds	Pounds
2	9	126	126
3	12.4	172	172
4	123	1722	1722
5	11.4	158	158
Sheet1

Have you tried them?

 

[sqrob]

just_jon,

looks like i took the long way to get to the same solution as you did...only because i didn't know how to translate the int() function into french.

nice work.

papillon,

care to send me the spreadsheet? i might be able to plug in the formula for you, if you show me which cells you want it in.

pls make sure no confidential info is included in the spreadsheet

sqrob

 

[simsy]

Just_Jon's solution above works well, exactly as you want I think, but the complication is caued by the fact that you are using the decimal point for entry purposes when it isn't really a decimal...

If you could enter the lbs in a different row the calculation is easy to understand...

weight 2004.xls
	A	B	C	D	E	F
2	Date	Stones	lbs	StonesasDec.	Totallbs	Kilos
3	Thu1Apr04	16	1	16.07	225	102.06
Apr

Regards,

Simsy

 

[eliW]

Hi Just_Jon,

The problem with your first solution is when trying to deal with 2 decimal digits like 8.12 for example.

Your first solution will give 113.2 instead of =8*14+12=124

Second solution is OK

Eli

 

[just_jon]

Actually I hadn't written either one =and obviously, did not thoroughly test either one ] -- this seems to fix the one, though --

=INT(A2)*14 + IF(INT(A2)<>A2,(A2-INT(A2))*10^(LEN(A2)-FIND(".",A2)))

 

[papillon]

just_jon,eliW,simsy & sqrob,
Thank you so much for all the effort you have put into solving my little problem.
That last formula from just_jon works a treat! You guys are just amazing!
Thanks again,
papillon

 

[nbrcrunch]

As an example, this start with the number 621.12 in cell A1

=A1-INT(A1) = .12

=CHOOSE(LEN(A1)-LEN(INT(A1))-1,10,100,1000) = 100
The above finds the length of the decimal portion in order to turn it into a whole number.


Multiply the two above to arrive at 12

Now all you need to do is multiply the integer of A1 to 14 to get (631*14) = 8834

Finally, put it all together to get:

=CHOOSE(LEN(A1)-LEN(INT(A1))-1,10,100,1000)*(A1-INT(A1))+(INT(A1)*14)

for a total of 8846

One problem, if any particular data cell does not contain a decimal value, this will fail.

 

[rrdonutz]

Late to the fray, but perhaps simpler:

MrE4014.xls
	A	B	C	D
1	Stones	Pounds
2	9	126
3	12.4	172
4	123	1722
5	11.4	158
6	621.12	8706
7	-12.4	-172
8	-3.11	-53
9	631.12	8846
10	17.15	253
11	0.11	11
Sheet7

Formulas:

B2: =TRUNC(A2)*14+REPLACE(A2&".0",1,FIND(".",A2&".0"),"")
B7: =TRUNC(A7)*14+SIGN(A7)*REPLACE(A7&".0",1,FIND(".",A7&".0"),"")

This latter formula will handle negative (& positive) conversions . . .

 

[Red Admiral]

Thanks rrdonutz, works great too, don't know how you do it.
papillon