Numbers convert cm to feet

Separating into separate columns for feet and inches simplifies life.

But if you want to continue entering height in the familiar 6' 02" format and are confident that will be entered consistently (no missing ' or "" or spaces) you can do something like this:

You add an "in inches" column (that you can later hide) with this formula in B2 copied down:

=MID(B2,1,1)×12+MID(B2,4,2)

You copy it into the three Footer Row cells as well (C6,C7,C8).

Then in B6 (on the first Footer Row under Ht, you paste this formula, and copy down to the MAX and MIN rows:

=ROUNDDOWN(C6÷12,0)&"' " & RIGHT("0"&ROUND(MOD(C6,12),0),2)& """"

It's tricky to input, so you would probably want to paste, and then change the C6 in two places to refer to the cell where you have your "in inches" data. All it does is converts the total inches it finds in column C back to a 6' 02" format.

SG

Addition: And check out the often forgotten CONVERT() function if you prefer working in other units than inches.

HI Hugh,

Numbers can do the math for you; all you need to provide is the conversion factor, plus the means of converting inches to feet and inches (or feet and inches to inches).

Which units are you starting with? Which do you want to finish with? Do you want weight/mass to be converted as well?

Here are examples for the units shown on your tables. Tables are identified as TL, TR, BL and BR in the formula list below:

Imperial to metric:

TL::C2: =ROUND((LEFT(B,1)*12+MID(B,4,2))/39.37,2)

TL::D2: =ROUND((LEFT(B,1)*12+MID(B,4,2))/0.3937,0)

TR::C2: =ROUND(B/2.2046,1)

Metric to Imperial:

BL::C2: =ROUND(B*0.3937,0)

BL::D2: =INT(ROUND(B*0.3937,0)/12)&"ft. "&MOD(ROUND(B*0.3937,0),12)&"in."

BR::C2: =ROUND(B*2.2046,0)

Fill each formula down its column.

Foot and inch entries are text, from which the two numeric values are extracted. The formulas in TL assume that there will be no heights greater than 9' 11", and that the format (one digit, foot marker, space, 2digits, inch marker) will be consistent.

Error triangles in the bottom rows of TL are due to the multiplication operator expecting a number and finding nothing. These can be eliminated by enclosing the formula in an error trap:

=IFERROR(formula,"")

The results in BL column D are text values and cannot be used in forthr calculations without extracting the numerical values in a manner similar to that in the fromulas in TL.

Regards,

Barry

Hugh,

I think the trick to making this easy is to have separate columns for Feet and Inches. That way you can skip the single and double quote symbols during entry too. With that approach, calculations are easy.

Jerry