CODEWITHSUNDEEP
excelconvertersformula
Miaka3
Miaka3

Reputation: 405

MS EXCEL: Convert (Height) Decimal Feet & Inches to Inches Only

Using MS Excel (2010), I would like a formula (Not VBA/Macros) that will convert Decimal Feet, Inches and Fraction of a person's height to Only inches.

Example: John is 6' 1 1/4" tall, I have converted the Total height to decimals = 73.250, and would like to convert the Decimal output to only inches, including fraction of height = 73 1/4"

See Sample for your assistance:

enter image description here

Upvotes: 0

Views: 3080

Answers (4)

Will Poirier
Will Poirier

Reputation: 16

If your decimal in inches is in A1, you can use the following to convert to 'tape measure' values. It's messy but it works:

=IF(INT((A1-INT(A1))*16+0.5)=0,INT(A1),IF(INT((A1-INT(A1))*16+0.5)=16,INT(A1)+1,IF(INT((A1-INT(A1))*16+0.5)/8=INT(INT((A1-INT(A1))*16+0.5)/8),INT(A1)&"  1/2",IF(INT((A1-INT(A1))*16+0.5)/4=INT(INT((A1-INT(A1))*16+0.5)/4),INT(A1)&"  "&INT(INT((A1-INT(A1))*16+0.5))/4&"/4",IF(INT((A1-INT(A1))*16+0.5)/2=INT(INT((A1-INT(A1))*16+0.5)/2),INT(A1)&"  "&INT(INT((A1-INT(A1))*16+0.5))/2&"/8",INT(A1)&"  "&(INT((A1-INT(A1))*16+0.5)&"/16"))))))

Upvotes: -1

Shahriar Hossain
Shahriar Hossain

Reputation: 148

I know that if I enter =3/4 in a cell it will give me .75 but is there is a formula to convert fractions into decimals or is this the best way to do it? I was also wondering if there is some way to convert the decimal amount to a fraction for measurement conversions, I know that=B3*25.4 will convert a decimal to mm and so will the formula =CONVERT(E4," in", "mm") and to convert mm to a decimal a few formulae that work are

=CONVERT(H3,"mm","in")
=MROUND(H4/25.4, 1/8)
=H5/25.4
=INT(H6/25.39999918*8)/8

Upvotes: 0

Robert Mearns
Robert Mearns

Reputation: 11996

A formula that would work is

=INT(A2)&" "&TEXT((A2-INT(A2)),"# #/#")&CHAR(34)

Breaking down the formula:

  • INT(A2) rounds the number down to the lowest integer
  • &" "& joins the results of the two formulas with a space
  • A2-INT(A2) returns the fractional part of the decimal number
  • TEXT(0.125,"# #/#) sets the formatting of the fraction
  • Char(34) adds the quotation mark " to the end

Upvotes: 2

DayLove.01
DayLove.01

Reputation: 80

Taken from: http://www.excelforum.com/l/671155-convert-fractions-to-decimals-and-decimal-to-fraction.html

You can append the following to your formula for dec. conversion using & : 

& IF(MROUND(MOD(A1,1),1/16),TEXT(MROUND(MOD(A1,1),1/16)," ?/#?"),"")&""""

With this formula: A1 = 73.25, B1 = 1/4"

Upvotes: 0

Related Questions