Need a Formula for Multiplication of Feet and Inches

  • Thread starter Thread starter vmohan1978
  • Start date Start date
V

vmohan1978

Need a Formula for the Following


No No L B D Qty
Remarks

2 2 4' 8" 2'4" 0' 9" ------- I
need formula that multiplies( 2*2*4'8"*2'4"*0'9"=32.64')
 
Hi,

First I'm having a problem figuring out just how you come to 32.64', my
calculation product 32.6667?

Second, are the entries always going to be single digits for the first two
and then always going to be feet & inches for the second three?

If so, then assume your numbers are in A1:E1 the following array formula
will give you my result:

=PRODUCT(A1:B1,LEFT(C1:E1,1)+MID(C1:E1,3,1)/12)

(the enter an array formula press Shift+Ctrl+Enter, not Enter.)
 
2*2*4'8"*2'4"*0'9"=32.64'

The correct result should be 32.66666667

This array formula works on your (very limited) sample data:

=PRODUCT(IF(ISNUMBER(FIND("'",A1:E1)),LEFT(A1:E1,FIND("'",A1:E1)-1)+MID(SUBSTITUTE(A1:E1,"""",""),FIND("'",A1:E1)+1,3)/12,A1:E1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
P.S.

I'm assuming (a lot!) that feet inches can be in any cell and the feet can
be any (integer) number and the inches can be any (integer) number up to 11.
 
Assuming the first two numbers are real numbers (not feet/inches) and that
the inches part of the other three numbers will never be more than 2-digits
in length, then this array-entered** formula should do what you want...

=PRODUCT(A2,B2,LEFT(C2:E2,FIND("'",C2:E2&"'")-1)+MID(SUBSTITUTE(C2:E2,"""",""),FIND("'",C2:E2&"'")+1,4)/12)

**Commit formula using Ctrl+Shift+Enter, not just Enter by itself.

This formula can be copied down.
 
Thank you Both Shane Devenshire and Rick Rothstein , it worked perfectly :)

it orked perfectly
 
Back
Top