Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
|
|||
|
|||
Answer: convert meters to feet/inches
To convert meters to feet and inches in Microsoft Excel, use the following formula:
Formula:
Formula breakdown:
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
convert meters to feet/inches
wow works great!!! thank you so much, really appreciate it
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert decimal to feet and inches | Excel Worksheet Functions | |||
convert to feet and inches | Excel Discussion (Misc queries) | |||
Convert feet/inches to decimal | Excel Worksheet Functions | |||
How do I convert Meters to Feet and Inches? | Excel Worksheet Functions | |||
is there a macro that will convert from inches to feet and inches | Excel Discussion (Misc queries) |