Remember Me? June 4th 08, 10:50 PM posted to microsoft.public.excel.worksheet.functions
 David Biddulph[_2_] external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 8,651 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

"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 June 4th 08, 10:51 PM posted to microsoft.public.excel.worksheet.functions
 sb1920alk external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 100 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

"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 June 4th 08, 10:57 PM posted to microsoft.public.excel.worksheet.functions
 Rick Rothstein \(MVP - VB\)[_614_] external usenet poster First recorded activity by ExcelBanter: Jun 2008 Posts: 1 convert meters to feet/inches

=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

"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 June 9th 09, 09:42 PM posted to microsoft.public.excel.worksheet.functions
 TheBlueShadow external usenet poster First recorded activity by ExcelBanter: Jun 2009 Posts: 1 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:

=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

"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 June 10th 09, 02:00 PM posted to microsoft.public.excel.worksheet.functions
 Glenn external usenet poster First recorded activity by ExcelBanter: Jun 2008 Posts: 1,240 convert meters to feet/inches

Change ROUND to TRUNC.

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:

=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

"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 June 10th 09, 03:06 PM posted to microsoft.public.excel.worksheet.functions
 Glenn external usenet poster First recorded activity by ExcelBanter: Jun 2008 Posts: 1,240 convert meters to feet/inches

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.

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'

Thank you. "Rick Rothstein (MVP - VB)" wrote:

=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

"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 June 10th 09, 04:46 PM posted to microsoft.public.excel.worksheet.functions
 David Biddulph[_2_] external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 8,651 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

...
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'

Thank you. "Rick Rothstein (MVP - VB)" wrote:

=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

"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 February 15th 10, 06:06 PM posted to microsoft.public.excel.worksheet.functions
 Matt external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 516 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 February 15th 10, 09:41 PM posted to microsoft.public.excel.worksheet.functions
 David Biddulph[_2_] external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 8,651 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 February 16th 10, 07:56 PM posted to microsoft.public.excel.worksheet.functions
 Bill Sharpe[_3_] external usenet poster First recorded activity by ExcelBanter: Feb 2010 Posts: 3 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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Erik Excel Worksheet Functions 5 October 25th 07 11:54 PM sjl Excel Discussion (Misc queries) 3 February 17th 07 12:31 AM dingy101 Excel Worksheet Functions 16 February 21st 06 10:18 PM Dave S Excel Worksheet Functions 7 December 16th 05 11:33 AM cable guy Excel Discussion (Misc queries) 1 June 20th 05 07:29 PM

All times are GMT +1. The time now is 07:59 PM. Copyright ©2004-2020 ExcelBanter.