Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
ann ann is offline
external usenet poster
 
Posts: 210
Default convert meters to feet/inches

i'm trying to convert from meters to feet and inches (not just feet or not
just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches for
example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: convert meters to feet/inches

To convert meters to feet and inches in Microsoft Excel, use the following formula:

Formula:
=(CONVERT(A1,"m","ft")&" "&ROUND((A1-CONVERT(A1,"m","ft"))*12)&" in"
Here, A1 is the cell containing the value in meters that you want to convert.

Formula breakdown:
  1. CONVERT(A1,"m","ft") converts the value in cell A1 from meters to feet.
  2. ROUND((A1-CONVERT(A1,"m","ft"))*12) calculates the remaining inches after converting to feet. This formula subtracts the feet value from the original value in meters, then multiplies the result by 12 to get the remaining inches. The ROUND function rounds the result to the nearest whole number.
  3. The & symbol joins the two results together with a space in between.
  4. " in" adds the "in" abbreviation for inches to the end of the formula.

For example, if you have 2 meters in cell A1, the formula will return "6 ft 7 in" (rounded up from 6 ft 6.74 in).
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 100
Default convert meters to feet/inches

=ROUND(A1*3.2808399-0.5,0)&" feet
"&ROUND((A1*3.2808399-ROUND(A1*3.2808399-0.5,0))*12,0)&" inches"

Here I'm converting the value in A1 from meters to feet using 3.2808399 feet
per meter. The first part truncates the decimal protion and adds, "feet" and
the second part truncates the integer portion and convert it to inches and
adds "inches"

"Ann" wrote:

i'm trying to convert from meters to feet and inches (not just feet or not
just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches for
example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 897
Default convert meters to feet/inches

One way:

Assume the # of meters is in A1:

To get feet, put this in B1:

=INT(CONVERT(A1,"m","ft"))

To get inches, put this in C1:

=ROUND(MOD(CONVERT(A13,"m","ft"),1)*12,1)


Put it together:

=B1&" feet and "&C1&" inches"


--JP

On Jun 4, 4:09*pm, Ann wrote:
i'm trying to convert from meters to feet and inches (not just feet or not
just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches for
example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,624
Default convert meters to feet/inches

One way:

=INT(CONVERT(A1,"m","ft")) & " feet, " &
TEXT(MOD(CONVERT(A1,"m","in"), 12), "0 ""inches""")


In article ,
Ann wrote:

i'm trying to convert from meters to feet and inches (not just feet or not
just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches for
example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
ann ann is offline
external usenet poster
 
Posts: 210
Default convert meters to feet/inches

and if i was to reverse this formula? from feet and inches back to meters?
if feet is in a1 and inches is in b1
thanks guys

"JE McGimpsey" wrote:

One way:

=INT(CONVERT(A1,"m","ft")) & " feet, " &
TEXT(MOD(CONVERT(A1,"m","in"), 12), "0 ""inches""")


In article ,
Ann wrote:

i'm trying to convert from meters to feet and inches (not just feet or not
just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches for
example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 100
Default convert meters to feet/inches

=CONVERT(VALUE(SUBSTITUTE(A1," feet",""))+VALUE(SUBSTITUTE(B1,"
inches",""))/12,"ft","m")

"Ann" wrote:

and if i was to reverse this formula? from feet and inches back to meters?
if feet is in a1 and inches is in b1
thanks guys

"JE McGimpsey" wrote:

One way:

=INT(CONVERT(A1,"m","ft")) & " feet, " &
TEXT(MOD(CONVERT(A1,"m","in"), 12), "0 ""inches""")


In article ,
Ann wrote:

i'm trying to convert from meters to feet and inches (not just feet or not
just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches for
example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
ann ann is offline
external usenet poster
 
Posts: 210
Default convert meters to feet/inches

thank you!!!

"sb1920alk" wrote:

=CONVERT(VALUE(SUBSTITUTE(A1," feet",""))+VALUE(SUBSTITUTE(B1,"
inches",""))/12,"ft","m")

"Ann" wrote:

and if i was to reverse this formula? from feet and inches back to meters?
if feet is in a1 and inches is in b1
thanks guys

"JE McGimpsey" wrote:

One way:

=INT(CONVERT(A1,"m","ft")) & " feet, " &
TEXT(MOD(CONVERT(A1,"m","in"), 12), "0 ""inches""")


In article ,
Ann wrote:

i'm trying to convert from meters to feet and inches (not just feet or not
just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches for
example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 100
Default convert meters to feet/inches

I guess you don't need the Value function, so =CONVERT(SUBSTITUTE(A1,"
feet","")+SUBSTITUTE(B1," inches","")/12,"ft","m") would work too.

"Ann" wrote:

thank you!!!

"sb1920alk" wrote:

=CONVERT(VALUE(SUBSTITUTE(A1," feet",""))+VALUE(SUBSTITUTE(B1,"
inches",""))/12,"ft","m")

"Ann" wrote:

and if i was to reverse this formula? from feet and inches back to meters?
if feet is in a1 and inches is in b1
thanks guys

"JE McGimpsey" wrote:

One way:

=INT(CONVERT(A1,"m","ft")) & " feet, " &
TEXT(MOD(CONVERT(A1,"m","in"), 12), "0 ""inches""")


In article ,
Ann wrote:

i'm trying to convert from meters to feet and inches (not just feet or not
just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches for
example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default convert meters to feet/inches

Better to divide by 0.3048, rather than to multiply by what isn't exactly
the reciprocal.
Also, why are you using the strange ROUND construct, rather than using TRUNC
(or INT if the number is positive, as your ROUND only works for positive
numbers)?

Isn't =TRUNC(A1/0.3048)&" feet "&ROUND(MOD(A1/0.3048,1)*12,0)&" inches" a
bit easier than
=ROUND(A1*3.2808399-0.5,0)&" feet
"&ROUND((A1*3.2808399-ROUND(A1*3.2808399-0.5,0))*12,0)&" inches" ?
--
David Biddulph

"sb1920alk" wrote in message
...
=ROUND(A1*3.2808399-0.5,0)&" feet
"&ROUND((A1*3.2808399-ROUND(A1*3.2808399-0.5,0))*12,0)&" inches"

Here I'm converting the value in A1 from meters to feet using 3.2808399
feet
per meter. The first part truncates the decimal protion and adds, "feet"
and
the second part truncates the integer portion and convert it to inches and
adds "inches"

"Ann" wrote:

i'm trying to convert from meters to feet and inches (not just feet or
not
just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches for
example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia





  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default convert meters to feet/inches

Checking again in the negative number case, it does need something a bit
more complicated, such as:
=TRUNC(A1/0.3048)&" feet "&ABS(ROUND((A1/0.3048-TRUNC(A1/0.3048))*12,0))&"
inches"
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Better to divide by 0.3048, rather than to multiply by what isn't exactly
the reciprocal.
Also, why are you using the strange ROUND construct, rather than using
TRUNC (or INT if the number is positive, as your ROUND only works for
positive numbers)?

Isn't =TRUNC(A1/0.3048)&" feet "&ROUND(MOD(A1/0.3048,1)*12,0)&" inches" a
bit easier than
=ROUND(A1*3.2808399-0.5,0)&" feet
"&ROUND((A1*3.2808399-ROUND(A1*3.2808399-0.5,0))*12,0)&" inches" ?
--
David Biddulph

"sb1920alk" wrote in message
...
=ROUND(A1*3.2808399-0.5,0)&" feet
"&ROUND((A1*3.2808399-ROUND(A1*3.2808399-0.5,0))*12,0)&" inches"

Here I'm converting the value in A1 from meters to feet using 3.2808399
feet
per meter. The first part truncates the decimal protion and adds, "feet"
and
the second part truncates the integer portion and convert it to inches
and
adds "inches"

"Ann" wrote:

i'm trying to convert from meters to feet and inches (not just feet or
not
just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches for
example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia





  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default convert meters to feet/inches

or (nearer to my earlier suggestion)
=TRUNC(A1/0.3048)&" feet "&ROUND(MOD(ABS(A1)/0.3048,1)*12,0)&" inches"
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Checking again in the negative number case, it does need something a bit
more complicated, such as:
=TRUNC(A1/0.3048)&" feet "&ABS(ROUND((A1/0.3048-TRUNC(A1/0.3048))*12,0))&"
inches"
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Better to divide by 0.3048, rather than to multiply by what isn't exactly
the reciprocal.
Also, why are you using the strange ROUND construct, rather than using
TRUNC (or INT if the number is positive, as your ROUND only works for
positive numbers)?

Isn't =TRUNC(A1/0.3048)&" feet "&ROUND(MOD(A1/0.3048,1)*12,0)&" inches" a
bit easier than
=ROUND(A1*3.2808399-0.5,0)&" feet
"&ROUND((A1*3.2808399-ROUND(A1*3.2808399-0.5,0))*12,0)&" inches" ?
--
David Biddulph

"sb1920alk" wrote in message
...
=ROUND(A1*3.2808399-0.5,0)&" feet
"&ROUND((A1*3.2808399-ROUND(A1*3.2808399-0.5,0))*12,0)&" inches"

Here I'm converting the value in A1 from meters to feet using 3.2808399
feet
per meter. The first part truncates the decimal protion and adds, "feet"
and
the second part truncates the integer portion and convert it to inches
and
adds "inches"

"Ann" wrote:

i'm trying to convert from meters to feet and inches (not just feet or
not
just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches for
example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia







  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 100
Default convert meters to feet/inches

Ok, you've sold me - I like TRUNC better now.

How often to you have a negative distance?

"David Biddulph" wrote:

Checking again in the negative number case, it does need something a bit
more complicated, such as:
=TRUNC(A1/0.3048)&" feet "&ABS(ROUND((A1/0.3048-TRUNC(A1/0.3048))*12,0))&"
inches"
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Better to divide by 0.3048, rather than to multiply by what isn't exactly
the reciprocal.
Also, why are you using the strange ROUND construct, rather than using
TRUNC (or INT if the number is positive, as your ROUND only works for
positive numbers)?

Isn't =TRUNC(A1/0.3048)&" feet "&ROUND(MOD(A1/0.3048,1)*12,0)&" inches" a
bit easier than
=ROUND(A1*3.2808399-0.5,0)&" feet
"&ROUND((A1*3.2808399-ROUND(A1*3.2808399-0.5,0))*12,0)&" inches" ?
--
David Biddulph

"sb1920alk" wrote in message
...
=ROUND(A1*3.2808399-0.5,0)&" feet
"&ROUND((A1*3.2808399-ROUND(A1*3.2808399-0.5,0))*12,0)&" inches"

Here I'm converting the value in A1 from meters to feet using 3.2808399
feet
per meter. The first part truncates the decimal protion and adds, "feet"
and
the second part truncates the integer portion and convert it to inches
and
adds "inches"

"Ann" wrote:

i'm trying to convert from meters to feet and inches (not just feet or
not
just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches for
example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia






  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default convert meters to feet/inches

Why not just this instead?

=TRUNC(A1/0.3048)&" feet "&ROUND(MOD(ABS(A1)/0.3048,1)*12,0)&" inches"

Rick


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Checking again in the negative number case, it does need something a bit
more complicated, such as:
=TRUNC(A1/0.3048)&" feet "&ABS(ROUND((A1/0.3048-TRUNC(A1/0.3048))*12,0))&"
inches"
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Better to divide by 0.3048, rather than to multiply by what isn't exactly
the reciprocal.
Also, why are you using the strange ROUND construct, rather than using
TRUNC (or INT if the number is positive, as your ROUND only works for
positive numbers)?

Isn't =TRUNC(A1/0.3048)&" feet "&ROUND(MOD(A1/0.3048,1)*12,0)&" inches" a
bit easier than
=ROUND(A1*3.2808399-0.5,0)&" feet
"&ROUND((A1*3.2808399-ROUND(A1*3.2808399-0.5,0))*12,0)&" inches" ?
--
David Biddulph

"sb1920alk" wrote in message
...
=ROUND(A1*3.2808399-0.5,0)&" feet
"&ROUND((A1*3.2808399-ROUND(A1*3.2808399-0.5,0))*12,0)&" inches"

Here I'm converting the value in A1 from meters to feet using 3.2808399
feet
per meter. The first part truncates the decimal protion and adds, "feet"
and
the second part truncates the integer portion and convert it to inches
and
adds "inches"

"Ann" wrote:

i'm trying to convert from meters to feet and inches (not just feet or
not
just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches for
example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia






  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default convert meters to feet/inches

I'm trying to put together a chart for track and field. This formula works
great except for one thing ... I'm wondering if you can find a solution.

If a pole vault bar is raised to 3.2 meters it equals 10' 6" like your
formula produces. But if the bar is raised to 3.35 meters the formula
produces 10' 12" ... is there a way to have the formula produce 11' instead?

Thank you. :)

"Rick Rothstein (MVP - VB)" wrote:

Why not just this instead?

=TRUNC(A1/0.3048)&" feet "&ROUND(MOD(ABS(A1)/0.3048,1)*12,0)&" inches"

Rick


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Checking again in the negative number case, it does need something a bit
more complicated, such as:
=TRUNC(A1/0.3048)&" feet "&ABS(ROUND((A1/0.3048-TRUNC(A1/0.3048))*12,0))&"
inches"
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Better to divide by 0.3048, rather than to multiply by what isn't exactly
the reciprocal.
Also, why are you using the strange ROUND construct, rather than using
TRUNC (or INT if the number is positive, as your ROUND only works for
positive numbers)?

Isn't =TRUNC(A1/0.3048)&" feet "&ROUND(MOD(A1/0.3048,1)*12,0)&" inches" a
bit easier than
=ROUND(A1*3.2808399-0.5,0)&" feet
"&ROUND((A1*3.2808399-ROUND(A1*3.2808399-0.5,0))*12,0)&" inches" ?
--
David Biddulph

"sb1920alk" wrote in message
...
=ROUND(A1*3.2808399-0.5,0)&" feet
"&ROUND((A1*3.2808399-ROUND(A1*3.2808399-0.5,0))*12,0)&" inches"

Here I'm converting the value in A1 from meters to feet using 3.2808399
feet
per meter. The first part truncates the decimal protion and adds, "feet"
and
the second part truncates the integer portion and convert it to inches
and
adds "inches"

"Ann" wrote:

i'm trying to convert from meters to feet and inches (not just feet or
not
just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches for
example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia








  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default convert meters to feet/inches

Change ROUND to TRUNC.

TheBlueShadow wrote:
I'm trying to put together a chart for track and field. This formula works
great except for one thing ... I'm wondering if you can find a solution.

If a pole vault bar is raised to 3.2 meters it equals 10' 6" like your
formula produces. But if the bar is raised to 3.35 meters the formula
produces 10' 12" ... is there a way to have the formula produce 11' instead?

Thank you. :)

"Rick Rothstein (MVP - VB)" wrote:

Why not just this instead?

=TRUNC(A1/0.3048)&" feet "&ROUND(MOD(ABS(A1)/0.3048,1)*12,0)&" inches"

Rick


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Checking again in the negative number case, it does need something a bit
more complicated, such as:
=TRUNC(A1/0.3048)&" feet "&ABS(ROUND((A1/0.3048-TRUNC(A1/0.3048))*12,0))&"
inches"
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Better to divide by 0.3048, rather than to multiply by what isn't exactly
the reciprocal.
Also, why are you using the strange ROUND construct, rather than using
TRUNC (or INT if the number is positive, as your ROUND only works for
positive numbers)?

Isn't =TRUNC(A1/0.3048)&" feet "&ROUND(MOD(A1/0.3048,1)*12,0)&" inches" a
bit easier than
=ROUND(A1*3.2808399-0.5,0)&" feet
"&ROUND((A1*3.2808399-ROUND(A1*3.2808399-0.5,0))*12,0)&" inches" ?
--
David Biddulph

"sb1920alk" wrote in message
...
=ROUND(A1*3.2808399-0.5,0)&" feet
"&ROUND((A1*3.2808399-ROUND(A1*3.2808399-0.5,0))*12,0)&" inches"

Here I'm converting the value in A1 from meters to feet using 3.2808399
feet
per meter. The first part truncates the decimal protion and adds, "feet"
and
the second part truncates the integer portion and convert it to inches
and
adds "inches"

"Ann" wrote:

i'm trying to convert from meters to feet and inches (not just feet or
not
just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches for
example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia



  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default convert meters to feet/inches

Although I'm not sure about your rounding rules.

You want 3.2 meters, which converts to 10 feet 5.98 inches to return 10 feet 6
inches.

You also want 3.35 meters, which converts to 10 feet 11.89 inches to return 10
feet 11 inches.

Under "normal" rounding rules, you would want 3.35 meters to result in 11 feet 0
inches. If that is what you really want, try this:

=INT(ROUND(CONVERT(A1,"m","in"),0)/12)&" feet "&
MOD(ROUND(CONVERT(A1,"m","in"),0),12)&" inches"

Glenn wrote:
Change ROUND to TRUNC.

TheBlueShadow wrote:
I'm trying to put together a chart for track and field. This formula
works great except for one thing ... I'm wondering if you can find a
solution.

If a pole vault bar is raised to 3.2 meters it equals 10' 6" like your
formula produces. But if the bar is raised to 3.35 meters the formula
produces 10' 12" ... is there a way to have the formula produce 11'
instead?

Thank you. :)

"Rick Rothstein (MVP - VB)" wrote:

Why not just this instead?

=TRUNC(A1/0.3048)&" feet "&ROUND(MOD(ABS(A1)/0.3048,1)*12,0)&" inches"

Rick


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Checking again in the negative number case, it does need something a
bit more complicated, such as:
=TRUNC(A1/0.3048)&" feet
"&ABS(ROUND((A1/0.3048-TRUNC(A1/0.3048))*12,0))&" inches"
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Better to divide by 0.3048, rather than to multiply by what isn't
exactly the reciprocal.
Also, why are you using the strange ROUND construct, rather than
using TRUNC (or INT if the number is positive, as your ROUND only
works for positive numbers)?

Isn't =TRUNC(A1/0.3048)&" feet "&ROUND(MOD(A1/0.3048,1)*12,0)&"
inches" a bit easier than
=ROUND(A1*3.2808399-0.5,0)&" feet
"&ROUND((A1*3.2808399-ROUND(A1*3.2808399-0.5,0))*12,0)&" inches" ?
--
David Biddulph

"sb1920alk" wrote in message
...
=ROUND(A1*3.2808399-0.5,0)&" feet
"&ROUND((A1*3.2808399-ROUND(A1*3.2808399-0.5,0))*12,0)&" inches"

Here I'm converting the value in A1 from meters to feet using
3.2808399 feet
per meter. The first part truncates the decimal protion and adds,
"feet" and
the second part truncates the integer portion and convert it to
inches and
adds "inches"

"Ann" wrote:

i'm trying to convert from meters to feet and inches (not just
feet or not
just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches
for
example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia



  #18   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default convert meters to feet/inches

To tackle that question, I would change my earlier formula (from more than a
year ago) to
=TRUNC(ROUND(A1/0.0254,0)/12)&" feet
"&ABS(ROUND(A1/0.0254,0)-TRUNC(ROUND(A1/0.0254,0)/12)*12)&" inches"

I will leave someone else to simplify it as Rick did for my previous effort.
--
David Biddulph

"TheBlueShadow" wrote in message
...
I'm trying to put together a chart for track and field. This formula works
great except for one thing ... I'm wondering if you can find a solution.

If a pole vault bar is raised to 3.2 meters it equals 10' 6" like your
formula produces. But if the bar is raised to 3.35 meters the formula
produces 10' 12" ... is there a way to have the formula produce 11'
instead?

Thank you. :)

"Rick Rothstein (MVP - VB)" wrote:

Why not just this instead?

=TRUNC(A1/0.3048)&" feet "&ROUND(MOD(ABS(A1)/0.3048,1)*12,0)&" inches"

Rick


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Checking again in the negative number case, it does need something a
bit
more complicated, such as:
=TRUNC(A1/0.3048)&" feet
"&ABS(ROUND((A1/0.3048-TRUNC(A1/0.3048))*12,0))&"
inches"
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Better to divide by 0.3048, rather than to multiply by what isn't
exactly
the reciprocal.
Also, why are you using the strange ROUND construct, rather than using
TRUNC (or INT if the number is positive, as your ROUND only works for
positive numbers)?

Isn't =TRUNC(A1/0.3048)&" feet "&ROUND(MOD(A1/0.3048,1)*12,0)&"
inches" a
bit easier than
=ROUND(A1*3.2808399-0.5,0)&" feet
"&ROUND((A1*3.2808399-ROUND(A1*3.2808399-0.5,0))*12,0)&" inches" ?
--
David Biddulph

"sb1920alk" wrote in message
...
=ROUND(A1*3.2808399-0.5,0)&" feet
"&ROUND((A1*3.2808399-ROUND(A1*3.2808399-0.5,0))*12,0)&" inches"

Here I'm converting the value in A1 from meters to feet using
3.2808399
feet
per meter. The first part truncates the decimal protion and adds,
"feet"
and
the second part truncates the integer portion and convert it to
inches
and
adds "inches"

"Ann" wrote:

i'm trying to convert from meters to feet and inches (not just feet
or
not
just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches
for
example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia








  #19   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 516
Default convert meters to feet/inches

One problem that I have while doing this is that I'd like the results to be
more precise.

I would like 12.35 meters to come out as 40' 6.25"

I'd even like to have the ' and " instead of feet/inches. Is all that a
possibility?

Thanks for help.

"sb1920alk" wrote:

=CONVERT(VALUE(SUBSTITUTE(A1," feet",""))+VALUE(SUBSTITUTE(B1,"
inches",""))/12,"ft","m")

"Ann" wrote:

and if i was to reverse this formula? from feet and inches back to meters?
if feet is in a1 and inches is in b1
thanks guys

"JE McGimpsey" wrote:

One way:

=INT(CONVERT(A1,"m","ft")) & " feet, " &
TEXT(MOD(CONVERT(A1,"m","in"), 12), "0 ""inches""")


In article ,
Ann wrote:

i'm trying to convert from meters to feet and inches (not just feet or not
just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches for
example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia

  #20   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default convert meters to feet/inches

=INT(A1*1000/(25.4*12))&"' "&ROUND(MOD(A1*1000/25.4,12),2)&""""
--
David Biddulph


matt wrote:
One problem that I have while doing this is that I'd like the results
to be more precise.

I would like 12.35 meters to come out as 40' 6.25"

I'd even like to have the ' and " instead of feet/inches. Is all
that a possibility?

Thanks for help.

"sb1920alk" wrote:

=CONVERT(VALUE(SUBSTITUTE(A1," feet",""))+VALUE(SUBSTITUTE(B1,"
inches",""))/12,"ft","m")

"Ann" wrote:

and if i was to reverse this formula? from feet and inches back to
meters? if feet is in a1 and inches is in b1
thanks guys

"JE McGimpsey" wrote:

One way:

=INT(CONVERT(A1,"m","ft")) & " feet, " &
TEXT(MOD(CONVERT(A1,"m","in"), 12), "0 ""inches""")


In article ,
Ann wrote:

i'm trying to convert from meters to feet and inches (not just
feet or not just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches
for example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia





  #21   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default convert meters to feet/inches

On 2/15/2010 1:41 PM, David Biddulph wrote:
=INT(A1*1000/(25.4*12))&"'"&ROUND(MOD(A1*1000/25.4,12),2)&""""
--
David Biddulph


matt wrote:
One problem that I have while doing this is that I'd like the results
to be more precise.

I would like 12.35 meters to come out as 40' 6.25"

I'd even like to have the ' and " instead of feet/inches. Is all
that a possibility?

Thanks for help.

"sb1920alk" wrote:

=CONVERT(VALUE(SUBSTITUTE(A1," feet",""))+VALUE(SUBSTITUTE(B1,"
inches",""))/12,"ft","m")

"Ann" wrote:

and if i was to reverse this formula? from feet and inches back to
meters? if feet is in a1 and inches is in b1
thanks guys

"JE McGimpsey" wrote:

One way:

=INT(CONVERT(A1,"m","ft"))& " feet, "&
TEXT(MOD(CONVERT(A1,"m","in"), 12), "0 ""inches""")


In ,
wrote:

i'm trying to convert from meters to feet and inches (not just
feet or not just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches
for example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia



David's formula is precise, but it's going to show 6.22 inches rather
than 6.25 inches. 6.22, of course, is the correct answer to two decimal
places.
The convert function only works between two units of measurement, not
among three.

Bill
  #22   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default convert meters to feet/inches

On Mon, 15 Feb 2010 10:06:02 -0800, matt
wrote:

One problem that I have while doing this is that I'd like the results to be
more precise.

I would like 12.35 meters to come out as 40' 6.25"

I'd even like to have the ' and " instead of feet/inches. Is all that a
possibility?

Thanks for help.


=INT(CONVERT(A1,"m","ft"))&"' " &
ROUND(MOD(CONVERT(A1,"m","in"),12)*4,0)/4 & """"

--ron
  #23   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 690
Default convert meters to feet/inches

I would like 12.35 meters to come out as 40' 6.25"

Not ideal, but a slightly different method might be the following.
If you can live with the leading zero on the inches...

40.' 06.22 ''

=TEXT(DOLLARFR(CONVERT(A1,"m", "ft"),12),"0.' 00\.00 ''")

or just use the formula, and make it a custom cell format.

= = = =
Dana DeLouis

On 2/15/2010 1:06 PM, matt wrote:
One problem that I have while doing this is that I'd like the results to be
more precise.

I would like 12.35 meters to come out as 40' 6.25"

I'd even like to have the ' and " instead of feet/inches. Is all that a
possibility?

Thanks for help.

"sb1920alk" wrote:

=CONVERT(VALUE(SUBSTITUTE(A1," feet",""))+VALUE(SUBSTITUTE(B1,"
inches",""))/12,"ft","m")

"Ann" wrote:

and if i was to reverse this formula? from feet and inches back to meters?
if feet is in a1 and inches is in b1
thanks guys

"JE McGimpsey" wrote:

One way:

=INT(CONVERT(A1,"m","ft"))& " feet, "&
TEXT(MOD(CONVERT(A1,"m","in"), 12), "0 ""inches""")


In ,
wrote:

i'm trying to convert from meters to feet and inches (not just feet or not
just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches for
example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia

  #24   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default convert meters to feet/inches

On Thursday, 5 June 2008 01:39:03 UTC+5:30, Ann wrote:
i'm trying to convert from meters to feet and inches (not just feet or not
just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches for
example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia

100*100m= howmanyfeets
  #25   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default convert meters to feet/inches

Hi,

Am Mon, 15 Jun 2015 05:35:25 -0700 (PDT) schrieb :

100*100m= howmanyfeets


what do you exactly mean?
100m x 100m
or
100 x 100m?

100 in cell A1.
=CONVERT(A1,"m","ft")^2


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional


  #26   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default convert meters to feet/inches

On Thursday, 5 June 2008 01:39:03 UTC+5:30, Ann wrote:
i'm trying to convert from meters to feet and inches (not just feet
or not just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches
for example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia

100*100m= howmanyfeets


Perhaps...

=ROUNDDOWN(CONVERT(2,"m","ft"),0)&"ft,
"&MOD(ROUNDUP(CONVERT(2,"m","in"),2),1)*12&"in "

...which returns...

6ft, 9in

...on which Claus (our resident formula wizard) may be able to make more
efficient.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #27   Report Post  
Junior Member
 
Posts: 4
Default

Use the =CONVERT(B5,"m","ft")
and format the cell as
#" feet" 0/12 "inches"

So if text is required =TEXT(CONVERT(11.87,"m","ft"),"#"" feet"" ?/12 ""inches""")
will yield
38 feet 11/12 inches

Regards
Brian
  #28   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default convert meters to feet/inches


If A1 has the value 2, use
CONCATENATE(TEXT(INT(CONVERT(A1,"m","ft")),"####0" ),
" feet,",TEXT(MOD(INT(CONVERT(A1,"m","in")),12),"#0" )," inches")
to give you 6 feet, 6 inches

If A1 has 100, you will get 328 feet, 1 inches

Ignores the fraction of an inch


On Mon, 15 Jun 2015 05:35:25 -0700 (PDT), wrote:

On Thursday, 5 June 2008 01:39:03 UTC+5:30, Ann wrote:
i'm trying to convert from meters to feet and inches (not just feet or not
just inches).
so if i have 2 meters, i want the result to read 6 feet, 2 inches for
example not 6.x feet or 78 inches.
here's the formula i have, which i can't get right.
=CONVERT(2,"m","ft""in").
tia

100*100m= howmanyfeets

  #29   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default convert meters to feet/inches

hello,I tried the formula and it works great, but what if I need to show the fraction also, like (18feet8inches1/8fraction)
  #30   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default convert meters to feet/inches

Hi,

Am Mon, 9 Dec 2019 13:06:52 -0800 (PST) schrieb :

hello,I tried the formula and it works great, but what if I need to show the fraction also, like (18feet8inches1/8fraction)


try:

=INT(CONVERT(A1,"m","ft"))&" feet "&TEXT(MOD(CONVERT(A1,"m","ft"),1)*12,"# ?/?")&" inches"


Regards
Claus B.
--
Windows10
Office 2016


  #31   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default convert meters to feet/inches

wow works great!!! thank you so much, really appreciate it
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I convert decimal to feet and inches Erik Excel Worksheet Functions 5 October 25th 07 11:54 PM
convert to feet and inches sjl Excel Discussion (Misc queries) 3 February 17th 07 01:31 AM
Convert feet/inches to decimal dingy101 Excel Worksheet Functions 16 February 21st 06 11:18 PM
How do I convert Meters to Feet and Inches? Dave S Excel Worksheet Functions 7 December 16th 05 12:33 PM
is there a macro that will convert from inches to feet and inches cable guy Excel Discussion (Misc queries) 1 June 20th 05 07:29 PM


All times are GMT +1. The time now is 06:56 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"