Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a formula for inches to feet and inches? | Excel Worksheet Functions | |||
feet and inches | Excel Worksheet Functions | |||
Special format for feet - inches in a cell | Excel Discussion (Misc queries) | |||
is there a macro that will convert from inches to feet and inches | Excel Discussion (Misc queries) | |||
Calculating the Area of objects in Feet and Inches | Excel Discussion (Misc queries) |