Measurement, in feet and inches, in Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I insert measurements in feet and inches and still be able to use
"sum" and "sort" options? I can insert them so that I know what they mean,
but "sort" thinks that 11" is smaller than 4".
 
To sort correctly, all entries in a sort field must have the same number of
digits, so your "4" would have to appear as "04". You can do this with the
Format-Cells Number tab. Select the "Custom" function and in the right-hand
box enter 00, 000, or whatever will be the maximum number of feet in your
sort. This will place 04 before 11.

Sorting by feet and inches is more difficult. Try the Convert function.
 
Try using hidden columns for formulas and use these to sort the data

If the measurement is in A3 in column B
=VALUE(LEFT(A3,FIND("'",A3)-1)) to find the feet
in column C type the formula
VALUE(RIGHT(A3,LEN(A3)-FIND("'",A3,1))) to find the inches

To sum feet in B2 type =SUM(B3:B9)+(INT(SUM(C3:C9)/12))
To sum Inches in C2 type =MOD(SUM(C3:C10),12)

To show the final total (as A string) in A2 type
=B2&" "&C2&"'"

You can sort on Columns B the C before hiding the columns, save your
worksheet and excel will remember the sort for next time.

Peter
 
Back
Top