Is it possible to get the sum of cells using numbers entered in feet? In
other words...I need to calculate a sum using numbers with the ' & "
symbol next to the number indicating feet & inches.

Is there a special way to do this, or do I need to convert the data to
whole numbers?

Thank you in advance. :)


--
calimari


------------------------------------------------------------------------
calimari's Profile: http://www.excelforum.com/member.php...o&userid=24537
View this thread: http://www.excelforum.com/showthread...hreadid=390229

Assuming you don't have mixed feet and inches within the same cell, you
could use the following formula to 'read' the text entry of feet as a
number and sum them together using an array formula:

=SUM(VALUE(LEFT(A1:A100,1)))

confirmed with Control+Shift+Enter, not just Enter.

the values of 1', 3' and 5' will return 9

If your cells contain mixed feet and inches, you can convert to decimal
feet (e.g. 1' 3" = 1.25 feet) and sum the results:
(note: this assumes your data is entered with a space between the ' and
the next character, e.g. 1'_3")

=LEFT(A1,FIND("'",A1)-1)+(MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND("
",A1)-1)/12)

HTH


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=390229

If you have a column of data in the form:

1&2

meaning one foot, two inches, then use Data Text to Columns.. feature to
separate the data into a column for feet and a column for inches.

Once you have summed th columns, you can either convert the inches to feet
or feet to inches and add them up.
--
Gary's Student


"calimari" wrote:


Is it possible to get the sum of cells using numbers entered in feet? In
other words...I need to calculate a sum using numbers with the ' & "
symbol next to the number indicating feet & inches.

Is there a special way to do this, or do I need to convert the data to
whole numbers?

Thank you in advance. :)


--
calimari


------------------------------------------------------------------------
calimari's Profile: http://www.excelforum.com/member.php...o&userid=24537
View this thread: http://www.excelforum.com/showthread...hreadid=390229

my original formula for finding feet only did not take into account for
numbers larger than 9. Use this instead:

=SUM(VALUE(LEFT(F15:F17,FIND("'",F15:F17)-1)))

again, this is an array formula and need to be entered using
CTRL+SHIFT+ENTER.

Good Luck

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=390229

Thank you. I really appreciate the assistance! ;)


--
calimari


------------------------------------------------------------------------
calimari's Profile: http://www.excelforum.com/member.php...o&userid=24537
View this thread: http://www.excelforum.com/showthread...hreadid=390229