Forum Discussion
Square Footage Cell(s) Worksheet
OK: I've given you one possible solution (not knowing for sure what you meant for column E to display, I made it the cell that computes the square footage based on width and length) and I've created this using inches for width and length, to give you greater precision. Here's the formula.
=(C7/12)*(D7/12)
I'm attaching the revised spreadsheet. As you'll see, I modified slightly your "Document Size" line. It now looks like this:
This is just play so do not take it too seriously. I have created a mix of standard Excel dynamic array formulas with a sprinkling of RegEx. I am sure a hardcore regular expression expert would not approve but breaking down the cryptic expressions to their constituent parts helps me!
(values in blue are input data)
Worksheet formulae
= AreaCalc(width, length)
"for a single occurrence, or"
= MAP(width, length, AreaCalc)
"for a list of dimensions"where the Lambda function is defined to be
/* "Function Name: AreaCalc"*/
/**"Calculates an area from the width and length expressed in feet and inches"
*/
AreaCalc
= LAMBDA(length1, length2,
LET(
number, "[\d|\/|\.|\s]+",
f, "[\'|f]",
in, "[\""|in]",
PosLkAhd, LAMBDA(t, "(?=" & t & ")"),
feet, number & PosLkAhd(f),
inches, number & PosLkAhd(in),
measure1, IFNA(REGEXEXTRACT(length1, HSTACK(feet, inches)), 0),
measure2, IFNA(REGEXEXTRACT(length2, HSTACK(feet, inches)), 0),
area, (SUM({12, 1} * (measure1)) * SUM({12, 1} * (measure2))) / 144,
ROUND(area, 2)
)
)
