Excel height-conversion formula

System
System Posts: 100,534 🤖 Admin 🤖
edited June 8 in AAT student discussion
Good morning all,
I;m hoping someone can give me a hand with this MS Excel query. What I'm trying to do is convert a number in metres to a number in feet and inches. As in a person's height, or the dimensions of a pice of furniture- that order of magnitude.

Lets assume the number I want to convert is in cell A2, and for the sake of arguement is 1.75 (about an average height for an adult male)

I know that I can do:
formula in cell b2 wrote:
=convert(A2,"m","in")
which will give me "68.8976" (to 4 dp), and that's easy enough to round to 69 inches.
But I don't want it to say that, I want "5 ft 9 in".

I managed to get correct feet and inches (in seperate columns) like this:
formula in cell c2 wrote:
=INT(B2/12)
formula in cell d2 wrote:
=ROUND(((B2/12)-INT(B2/12))*12,0)
Which gives me the "5" and "9" respectively I was after, although the last one feels a bit clunky.

I then used the concatenate function:
=CONCATENATE(c2," ft ",d2," in")

Which finally gives me "5 ft 9 in", which is what I was after. If I string them all together, I get:
=CONCATENATE(INT(CONVERT(A1,"m","in")/12)," ft ",ROUND((CONVERT(A1,"m","in")/12-INT(CONVERT(A1,"m","in")/12))*12,0)," in")

which is extremely ugly and awkward (you should see it with forum codes!). On the other hand, it will take a number in metres and translate it directly into feet and inches. Isn't there an easter way?

Graham

Comments

  • System
    System Posts: 100,534 🤖 Admin 🤖
    Excel height-conversion formula
    GJP104 wrote:
    Isn't there an easter way?
    I hope so - it isn't eggactly easy to follow, is it?
    :shock:
  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Excel height-conversion formula

    I usually reckon that when I need a complex function it's easier to to do in VBA. So something along the lines of:
    Function MtoF(metres)
    Dim totalInches, feet, inches As Integer

    totalInches = metres * 39.37
    feet = Int(totalInches / 12)
    inches = Round(((totalInches / 12) - feet) * 12, 0)
    MtoF = feet & " ft" & " " & inches & " in"
    
End Function

    Not much neater but at least you only have to enter =MtoF(cell) in the worksheet when you want to use it
  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Excel height-conversion formula

    Thanks Chris, that makes sense

    I've never used Visual Basic much though- its always seemed a little intimidating.
    Maybe I should just dive in and try it.

    Helen, of course I meant "easier". Sounds like my subconscious mind at work again.
    Damn these Freudian boobies!

    er, Freudian slips :oops:
  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Excel height-conversion formula
    GJP104 wrote:

    Freudian boobies!

    :oops:

    Is that like " Fallen Madonna with the Big Boobies"?
  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Excel height-conversion formula


    I know I can't be the only one wanting to ask...WHY?

    :shock:
  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Excel height-conversion formula
    Baggybooks wrote:
    I know I can't be the only one wanting to ask...WHY?

    :shock:



    It is quite easy to do these conversions in ones head, at least it is for the older members of the community :lol:

Categories

Privacy Policy