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

 

[DRJ]

How are the stones and pounds stored in your spreadsheet?

 

[nbrcrunch]

This is just simple math

(NumberOfStone * 14) + Number of Pounds

As an Excel formula

=(Stone*14)+Pounds

But DRJ's question remains relavant because if the amounts are stored as text, you'll need to write an interpreter.

If each record's values (Stone and Pounds) are stored in single cell, that is another consideration.

Finally, if the Stones are in one column and the Pounds in another, then the simple math I gave you will work.

 

[papillon]

Hi DRJ & nbrcrunch,
Thanks for replying, the values are stored in a single cell - eg 13.3 - the value 13 are stones.
Many thanks,
papillon

 

[DRJ]

So the decimal amount is always pounds? If so then if the value is in A1 try this formula.

=Int(A1)*14 + (A1-Int(A1))*10

 

[Red Admiral]

Hi Jacob,
Unfortunately, that formula didn't work, the decimal point is there just to separate the stones and the pounds and not to express the pounds as a percentage
Thanks,
papillon

 

[sqrob]

is a number always enterred with a decimal ?
ie 12.0
or if the number of pounds is zero does the entry just become '12' ?

 

[sqrob]

here is a possible solution for you. it is originally written in french so the second line is a translation and might have to be checked in your help file.

=SI(ESTNUM(TROUVE(",";A4));(STXT(A4;(TROUVE(",";A4)+1);5)+(TRONQUE(A4)*14));(A4)*14)

=if(isnumber(find(".",A4)),(mid(A4,(find(".",A4)+1),5)+(trunc(A4)*14)),(A4)*14)

the above assumes A4 is the cell in which the stones and pounds are in.

also, i believe that the english version of excel uses commas as separators in place of my semi-colons.

pls advise if this helps

 

[papillon]

Hi sqrob,
The number has always been entered with a decimal but if pounds are zero, nothing is entered.
Thanks for the formula but it was way over my head I'm afraid,
Thanks,
papillon

 

[just_jon]

Thanks for the formula but it was way over my head I'm afraid,

Does that mean you're still w/o solution?