1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Buying Google sheets - weights in imperial measurements

Discussion in 'Programming' started by fadetoblack22, Mar 28, 2020.

  1. #1
    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.
    SEMrush
     
    fadetoblack22, Mar 28, 2020 IP
    SEMrush
  2. Saputnik

    Saputnik Active Member

    Messages:
    61
    Likes Received:
    24
    Best Answers:
    0
    Trophy Points:
    70
    As Seller:
    100% - 0
    As Buyer:
    100% - 0
    #2
    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...
     

    Attached Files:

    Saputnik, Mar 28, 2020 IP
  3. fadetoblack22

    fadetoblack22 Well-Known Member

    Messages:
    2,332
    Likes Received:
    52
    Best Answers:
    0
    Trophy Points:
    160
    As Seller:
    100% - 1
    As Buyer:
    100% - 0
    #3
    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?
     
    fadetoblack22, Mar 28, 2020 IP
  4. Saputnik

    Saputnik Active Member

    Messages:
    61
    Likes Received:
    24
    Best Answers:
    0
    Trophy Points:
    70
    As Seller:
    100% - 0
    As Buyer:
    100% - 0
    #4
    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. :)
     

    Attached Files:

    Saputnik, Mar 28, 2020 IP
  5. fadetoblack22

    fadetoblack22 Well-Known Member

    Messages:
    2,332
    Likes Received:
    52
    Best Answers:
    0
    Trophy Points:
    160
    As Seller:
    100% - 1
    As Buyer:
    100% - 0
    #5

    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.
     
    fadetoblack22, Mar 28, 2020 IP
  6. Saputnik

    Saputnik Active Member

    Messages:
    61
    Likes Received:
    24
    Best Answers:
    0
    Trophy Points:
    70
    As Seller:
    100% - 0
    As Buyer:
    100% - 0
    #6
    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
     

    Attached Files:

    Saputnik, Mar 28, 2020 IP
    fadetoblack22 likes this.
  7. fadetoblack22

    fadetoblack22 Well-Known Member

    Messages:
    2,332
    Likes Received:
    52
    Best Answers:
    0
    Trophy Points:
    160
    As Seller:
    100% - 1
    As Buyer:
    100% - 0
    #7
    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.
     
    fadetoblack22, Mar 30, 2020 IP
    Saputnik likes this.