Multiply cell with foot and inch

[Kyle]

How do I format a cell for user to enter in ft and inch
format such as 4'33". Then how do I multiply 2 cell
together to produce square foot. Example A1=4'33" and
B1=3'33" which is equal 14.42 Sq.Ft.

Thanks.

 

[Gord Dibben]

Kyle

Check out Chip Perason's site for working with feet and inches.

http://www.cpearson.com/excel/fractional.htm


Gord Dibben Excel MVP

 

[Jason Morin]

Pre-format the cells (say A1 and B1) as text. Then use:

=(LEFT(A1,FIND("'",A1)-1)+MID(A1,FIND("'",A1)+1,FIND(CHAR
(34),A1)-FIND(CHAR(34),A1)+1)/12)*(LEFT(B1,FIND("'",B1)-1)
+MID(B1,FIND("'",B1)+1,FIND(CHAR(34),B1)-FIND(CHAR(34),B1)
+1)/12)

This requires that inches always be entered, even if they
are 0 (e.g. 4'0"). The formula could be updated to allow
the user to enter feet only, but then the formula would
only get bigger and uglier.

A well-designed spreadsheet would allow the user to key
into 4 cells, with feet in A1 and inches in B1 for the
first measurement, and C1 and D1 for the 2nd. Then you
could use:

=(A1+B1/12)*(C1+D1/12)

and the user could leave the cells for inches blank if
inches = 0.

HTH
Jason
Atlanta, GA