Buying Google sheets - weights in imperial measurements

I work with weight loss and in the UK we use STONE which is 14lbs or 6.35kg

The problem is that because 14 isn't base 10 it's hard to make it display on a graph as stone.

So for example, 10.10 stone is 150lbs
BUT 10.11 stone isn't 11 stone like with base 10.

It goes like this:

10.01
10.02
10.03
10.04
10.05
10.06
10.07
10.08
10.09
10.10
10.11
10.12
10.13
11.00

So 10 and half would be 10.07, not 10.05.


Is there a way to show this on a graph?

I am paying for this service.

 

I'm not exactly sure what do you want to display on the chart, stones vs. lbs?

The way you explained it, Excel formula to convert lbs to stones is:
=INT(A1/14)+MOD(A1,14)/100
where A1 contains weight in lbs.

Formula to convert stones to lbs is:
=INT(A1)*14+(A1-INT(A1))*100

But if you try to chart with that formula, you will get the staircase type of the chart, i.e., for example it will slowly increase from 10.00 stones to 10.13 stones, and then jump to 11.00 stones; therefore, for charting, you need to adjust gap between decimal places by 100/14 for each step:

=INT(A1)+(A1-INT(A1))*100/14

With that delineation, you get proper chart; check attached Excel file, or Google Sheets here: https://drive.google.com/file/d/1VFz4G67Kip-TRYYC1ArcCC4-3Sk_dZ25/view?usp=sharing

Sorry if I missunderstood your question - this is first time I dabble with stones this way.

Note: amazingly, this forum does not accept upload of .xlsx files, that's why I zipped it, and then it goes, it seems...

 

Yes, thanks! That's almost what I want. If you DM me I can pay you for it.

Basically I want to chart stone against time.

Sometimes clients give me measurements in stone, sometimes lbs. I want to convert to it stone and chart it week per week for 12 weeks.

Does that make sense?

 

OK, I see; here is the updated file: https://drive.google.com/file/d/165M5s8mNANzHSo3brqEFHqAn43iVpasB/view?usp=sharing
Zipped Excel file attached, as well.

Now, you have two tables with 12 weeks each.

If you get weight in lbs, you enter it in column"lbs", it gets converted to stones and charted at sheet "chart-lbs".

If you get weight in stones, you enter it in second table, in column "stones", and it gets converted to lbs (to have it for reference) and charted at sheet "chart-stones".

Hope I got what you're looking for?

As for payment, thank you very much, but it's not needed, this is minor task, kind of exercise for me; and in process, I have learnt about stones measurement.

 

Yeah, thank you - almost! When I put in for example, 7.5 stones, it comes out as more pounds than if I put in 9.5. I think there's an error there somewhere...

And thank you. That's very kind of you.

 

You welcome, it was my pleasure. As for error, I have checked, and it is actually not error, but another missunderstanding - I followed what you explained in first post: seven and half stones is 7.07 stones, and 9 and half stones are 9.07, aren't they? I did not expect that you would write 7.50 stones, but 7.07, and not 9.50, but 9.07.

Added is another, third table, for decimal input of stones. If seven and half stones is "7.07", you use second table and chart; if seven and half stones is "7.50", you use third table and chart.

So, 7.50 stones are 105 lbs (7*14+7=105) and 9.50 stones are 133 lbs (9*14+7=133), that sounds correct to me.

I hope this update is suitable? I thought it was more user-friendly to have separate table for each case, than to write over the same table and undo inputs to recover formulas, or to use drop-down menu to select what is the input.

Excel file attached, and Google Sheets here: https://drive.google.com/file/d/1da9TN80LSmPTum6pzvoZ3HGW_1lmastS/view?usp=sharing

 

Thank you! Yes, you're right. It was user error. I am not used to putting in the zeroes before the decimal.

That's very useful so thanks again for your help.