Problems with feet an inches
 

I have imported a column of data from another program that is in feet and
inches (1' 7"). There is a space separating the values. I need to average
this column. It is not that important on the final format. It doesn't have to
be in feet and inches. How can I do this without manually converting each
cell?

Problems with feet an inches
 

Ken,

If your lengths are in column A, starting in cell A2, then use this formula in another column:

=VALUE(LEFT(A2,FIND("'",A2)-1))+ VALUE(MID(A2,FIND("'",A2)+1,FIND("""",A2)-FIND("'",A2)-1))/12

and copy down to match your list. This will give you decimal feet, which you can then average.

HTH,
Bernie
MS Excel MVP


"Ken" wrote in message
...
I have imported a column of data from another program that is in feet and
inches (1' 7"). There is a space separating the values. I need to average
this column. It is not that important on the final format. It doesn't have to
be in feet and inches. How can I do this without manually converting each
cell?

Problems with feet an inches
 

Bernie, thanks alot. That worked perfectly. Now I have another problem. In
this same spreadsheet, I have imported 2 other columns of text data. I would
like to use the average formula you gave me with certain conditions. Both
columns contain one of 2 possible results, lets say it is "yes" or "no" for
column 1 and "up" or "down" for column 2. I would like to average the results
of the third column (feet and inches) if the text in column 1 and the text in
column 2 contain "no" and "down" respectively. I have tried using logical
functions but they seem to only work with numbers. Can you help me with this
also?
Ken

Problems with feet an inches
 

Ken,

=SUMPRODUCT((A1:A100="no")*(B1:B100="down")*(C1:C1 00))/SUMPRODUCT((A1:A100="no")*(B1:B100="down"))

Or, use a Pivot table set for average - much easier to get your four combinations of values
averaged.

HTH,
Bernie
MS Excel MVP


"Ken" wrote in message
...
Bernie, thanks alot. That worked perfectly. Now I have another problem. In
this same spreadsheet, I have imported 2 other columns of text data. I would
like to use the average formula you gave me with certain conditions. Both
columns contain one of 2 possible results, lets say it is "yes" or "no" for
column 1 and "up" or "down" for column 2. I would like to average the results
of the third column (feet and inches) if the text in column 1 and the text in
column 2 contain "no" and "down" respectively. I have tried using logical
functions but they seem to only work with numbers. Can you help me with this
also?
Ken

Problems with feet an inches
 

Bernie, I tried the formula:
=SUMPRODUCT((A1:A100="no")*(B1:B100="down")*(C1:C1 00))/SUMPRODUCT((A1:A100="no")*(B1:B100="down")),
but I get an error message (#Div/0!). I don't know enough yet about pivot
tables to try this method, but I am working on it.

Problems with feet an inches
 

Bernie, I finally figured out how to do the pivot table. You were right. It
is fairly simple. Thanks for everything.