Convert room dimensions 13' 8" X 9' 2" to square feet
lai...@canada.com
you put 13' 8" in the same cell ?
Dale
joeu2004
> how do you convert room dimensions 13' 8" X 9' 2" to
> square feet. Can you put 13' 8" in the same cell ?
AFAIK, Excel does not recognize that form. But you might double-check the
"special" and "custom" formats of your version.
Two alternatives come to mind.
The first is a clever (too clever?) use of DOLLARDE and DOLLARFR functions,
which are part of the ATP for XL2003; presumably, they were mainstreamed in
XL2007 and later.
This requires that you enter your feet as integers and inches as decimal
fractions divided by 100. For example, 13.08 and 9.02. You can use the
Custom format 0"' ".00\" to get almost the appearance that you use above.
(But note that we cannot get rid of the decimal point.)
With such values in A1 and A2, the square measurement can be calculated as
follows:
=DOLLARFR(DOLLARDE(A1,12)*DOLLARDE(A2,12),12) formatted with the same Custom
format.
The second approach is to enter feet-inches amounts exact as you do above,
namely: 13' 8" and 9' 2". Note that Excel will treat them as text. You
must then use text functions like MID, LEFT, RIGHT and FIND to manipulate
the text. I suggest that you use helper cells in order to reduce
replication; but you can choose to combine the individual formulas into a
single one-liner.
If A7 contains feet-inches text, then use B7 to convert to a decimal number
as follows:
=LEFT(A7,FIND("'",A7)-1)+LEFT(RIGHT(A7,3),2)/12
or
=LEFT(A7,FIND("'",A7)-1)+MID(LEFT(A7,LEN(A7)-1),FIND("'",A7)+1,99)/12
The first simpler formula is limited to integer inches separated from feet
by at least one space. The second formula is more robust; it permits
non-integer inches (e.g. 8.125), and it does not require the one-space
separator.
If B7 and B8 contain your two feet-inches amounts converted to decimal
numbers, the square feet is simply =B7*B8.
I don't know if it makes sense to present that in the feet-inches form. But
it you want that, you could do the following:
=INT(B7*B8)&"' "&ROUND(12*MOD(B7*B8,1),0)&""""
joeu2004
I wrote:
> The first is a clever (too clever?) use of DOLLARDE and DOLLARFR functions
I meant to attribute that approach to MrExcel.com.
Gord
or convert all to inches then back to sq ft
164 * 110/144 = 125.27 sq ft
Gord Dibben Microsoft Excel MVP
isabelle
this doesn't exist in the native functionality of Excel,
i suggest you a custom function
with provided that data is always enter in that form (0'1") or (10'12")
=SquareFeet(A1,B1)
[code]
Function SquareFeet(rng1 As Range, rng2 As Range) As Double
x = Split(rng1, "'")
inches1 = CDbl(Application.Substitute(x(1), """", ""))
feet1 = CDbl(x(0))
x2 = Split(rng2, "'")
inches2 = CDbl(Application.Substitute(x(1), """", ""))
feet2 = CDbl(x(0))
SquareFeet = ((feet1 + (inches1 / 12)) * (feet2 + (inches2 / 12)))
End Function
[/code]
--
isabelle
