Writing Stones and Pounds into Excel

[Jaffabfc]

Hi,

I am currently on a journey of losing weight and trying to keep a record of it but i have to go on a website and convert 15st 11lb (example) to a numberic number to workout the pounds i have lost.

So 15st 11lb i would have to input 15.7857 and then use convert(xxx,"stone","lbm") to show the pounds and then workout my loss.
But i dont want to be going to a website to work it out everytime.

The website i use is coolconversion.com

Can someone help me.
thanks

 

[Fluff]

How about like

Fluff.xlsm
	A	B
1
2	15.11	15.78571
3	15.01	15.07143
4		10
Sheet6

Cell Formulas
Range	Formula
B2:B3	B2	=DOLLARDE(A2,14)
B4	B4	=(B2-B3)*14

 

[Jaffabfc]

that looks like it would work which would be brilliant, how would it work though if i did 15stone 11.2lb for example
.2 of a pound all counts when losing weight lol

 

[Fluff]

Just enter it as 15.112

 

[Teeroy]

While I think that @Fluff's approach is elegant it would make my head explode since the number that you input is not an actual decimal.
Also, if you forget to put in a leading '0' e.g 15.1 for 15 st 1 lb instead of 15.01 you'll get a wildly wrong answer.
I'd personally make it simpler with helper columns, and you could hide column D.

Book1
	A	B	C	D	E	F
1	Date	ST	Lb	ST (total)	Loss per period (lb)	Loss Total (lb)
2	1/10/2024	15	7	15.5	-	-
3	3/10/2024	15	5	15.35714	2	2
4	5/10/2024	15	4	15.28571	1	3
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=+B2+C2/14
E3:E4	E3	=(-D3+D2)*14
F3:F4	F3	=(-D3+$D$2)*14

 

[Jaffabfc]

Just enter it as 15.112

That would make sense, i did try it with some data ive got and it gave me a different response to what i have from the calculatlor online so i would have to either redo my historic data (which makes me look heavier) or carry on as i am doing. Thanks though

 

[Jaffabfc]

While I think that @Fluff's approach is elegant it would make my head explode since the number that you input is not an actual decimal.
Also, if you forget to put in a leading '0' e.g 15.1 for 15 st 1 lb instead of 15.01 you'll get a wildly wrong answer.
I'd personally make it simpler with helper columns, and you could hide column D.

Book1
	A	B	C	D	E	F
1	Date	ST	Lb	ST (total)	Loss per period (lb)	Loss Total (lb)
2	1/10/2024	15	7	15.5	-	-
3	3/10/2024	15	5	15.35714	2	2
4	5/10/2024	15	4	15.28571	1	3
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=+B2+C2/14
E3:E4	E3	=(-D3+D2)*14
F3:F4	F3	=(-D3+$D$2)*14

i did have this originally but i didnt want to be messing about with extra columns, just literally wanted to put in 1 column the weight and it worked out my pounds/kg etc and shows how much ive lost.
i may have to put it in and then use a formula in another cell to work it out to 15 stone 7.2lb for example

 

[Jaffabfc]

i did have this originally but i didnt want to be messing about with extra columns, just literally wanted to put in 1 column the weight and it worked out my pounds/kg etc and shows how much ive lost.
i may have to put it in and then use a formula in another cell to work it out to 15 stone 7.2lb for example

got an update but also another issue.

I have done as you said and put new columns for stone and lbs in D and E.
In column C i have put Weight and i am entering 15st 10.1lbs (example).
In column D i am using "left(c3,2)" to get the weight (doubt me ever getting below 10 stone so this is fine).
In column E i am using "Mid(c3,6,4) which brings back the pounds.
I then have in column F =sum(d3*14)+E3)
The only issue is when i lose weight to single pounds like 9 or 8 it would bring up an error.

I have tried a few formulas but all failing, can someone help

 

[Sergius]

Hello!
Is this what you need?

Book1.xlsm
	G	H	I	J
4	st	lb		kg
5	15	11,5		100,45
6	15	10		99,77
Table2

Cell Formulas
Range	Formula
J5:J6	J5	=(($G5*14)+$H5)/2.205

Here 14 is to convert st to lb, and 2.205 to convert this total to kg

 

[Fluff]

How about

Fluff.xlsm
	C	D
1
2	15st 10.1lbs	220.1
3	15st 1.1lbs	211.1
Data

Cell Formulas
Range	Formula
D2:D3	D2	=LET(w,TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(C2,"st",""),"lbs","")," "),SUM(w*{14,1}))