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')

Excel won't recognize those as numbers.

You first have to convert to a format Excel can use.

See Chip Pearson's site for more info.

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


Gord Dibben MS Excel MVP

On Sun, 4 Jan 2009 20:04:22 -0800, vmohan1978
wrote:

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.)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"vmohan1978" wrote:

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')

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,F IND("'",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.

--
Biff
Microsoft Excel MVP


"vmohan1978" wrote in message
...
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')

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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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,F IND("'",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.

--
Biff
Microsoft Excel MVP


"vmohan1978" wrote in message
...
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')

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.

--
Rick (MVP - Excel)


"vmohan1978" wrote in message
...
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')

Thank you Both Shane Devenshire and Rick Rothstein , it worked perfectly :)

it orked perfectly

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"vmohan1978" wrote in message
...
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')