Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Average first 3 numbers in column e.g. 130 in 130/82
I am trying to obtain an average of blood pressure over a period of time.
The individual entries are in a single column but are in a combined format (e.g. 130/82). I need to parse each of the two numbers and arrive at an average of all entries in the column. I have searched help because I thought I had seen some reference to the selecting only a portion of a number on a prior search. Unfortunately, I could not come across it again. Thanks for any help |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Average first 3 numbers in column e.g. 130 in 130/82
You can highlight the column and then do Data TextToColumns, and use the / as the delimiter.........this will separate the diastolic and systolic numbers each into their own column.........then just average each column using the =AVERAGE(range) formula. Vaya con Dios, Chuck, CABGx3 "gadmire" wrote: I am trying to obtain an average of blood pressure over a period of time. The individual entries are in a single column but are in a combined format (e.g. 130/82). I need to parse each of the two numbers and arrive at an average of all entries in the column. I have searched help because I thought I had seen some reference to the selecting only a portion of a number on a prior search. Unfortunately, I could not come across it again. Thanks for any help |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Average first 3 numbers in column e.g. 130 in 130/82
Chuck:
My only problem with the below solution is that I have multiple columns next to the column I want to average. By using the below method, I would need to move data in many other columns to add the new column (resulting from the split of the numbers). This would be somewhat impractical for me. I seem to recall a formula that conditions either an average or sum that simply takes a specified number of spaces from an entry and calculates the result using, say, only the first three digits. If I could find this formula, it would eliminate the need to create new columns. Does the above ring any bells to you? Que le vaya bien, Gary "CLR" wrote: You can highlight the column and then do Data TextToColumns, and use the / as the delimiter.........this will separate the diastolic and systolic numbers each into their own column.........then just average each column using the =AVERAGE(range) formula. Vaya con Dios, Chuck, CABGx3 "gadmire" wrote: I am trying to obtain an average of blood pressure over a period of time. The individual entries are in a single column but are in a combined format (e.g. 130/82). I need to parse each of the two numbers and arrive at an average of all entries in the column. I have searched help because I thought I had seen some reference to the selecting only a portion of a number on a prior search. Unfortunately, I could not come across it again. Thanks for any help |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Average first 3 numbers in column e.g. 130 in 130/82
If your first number is always three digits and your second two digits,
try... =AVERAGE(IF(A1:A100<"",LEFT(A1:A100,3)+0)) and =AVERAGE(IF(A1:A100<"",RIGHT(A1:A100,2)+0)) Otherwise, try... =AVERAGE(IF(A1:A100<"",MID(A1:A100,1,FIND("/",A1:A100)-1)+0)) and =AVERAGE(IF(A1:A100<"",MID(A1:A100,FIND("/",A1:A100)+1,1024)+0)) These formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , "gadmire" wrote: Chuck: My only problem with the below solution is that I have multiple columns next to the column I want to average. By using the below method, I would need to move data in many other columns to add the new column (resulting from the split of the numbers). This would be somewhat impractical for me. I seem to recall a formula that conditions either an average or sum that simply takes a specified number of spaces from an entry and calculates the result using, say, only the first three digits. If I could find this formula, it would eliminate the need to create new columns. Does the above ring any bells to you? Que le vaya bien, Gary "CLR" wrote: You can highlight the column and then do Data TextToColumns, and use the / as the delimiter.........this will separate the diastolic and systolic numbers each into their own column.........then just average each column using the =AVERAGE(range) formula. Vaya con Dios, Chuck, CABGx3 "gadmire" wrote: I am trying to obtain an average of blood pressure over a period of time. The individual entries are in a single column but are in a combined format (e.g. 130/82). I need to parse each of the two numbers and arrive at an average of all entries in the column. I have searched help because I thought I had seen some reference to the selecting only a portion of a number on a prior search. Unfortunately, I could not come across it again. Thanks for any help |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Average first 3 numbers in column e.g. 130 in 130/82
Reading between the lines
=ROUND(AVERAGE(IF(A1:A100<"",--LEFT(A1:A100,3))),0)&"/"&ROUND(AVERAGE(IF(A1 :A100<"",--RIGHT(A1:A100,2))),0) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "gadmire" wrote in message ... I am trying to obtain an average of blood pressure over a period of time. The individual entries are in a single column but are in a combined format (e.g. 130/82). I need to parse each of the two numbers and arrive at an average of all entries in the column. I have searched help because I thought I had seen some reference to the selecting only a portion of a number on a prior search. Unfortunately, I could not come across it again. Thanks for any help |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Average first 3 numbers in column e.g. 130 in 130/82
Bob:
Thanks for the recommendation. It worked perfectly after I started the formula at B3 ( B1 and B2 contained text which prevented correct operation of the formula). Bob "Bob Phillips" wrote: Reading between the lines =ROUND(AVERAGE(IF(A1:A100<"",--LEFT(A1:A100,3))),0)&"/"&ROUND(AVERAGE(IF(A1 :A100<"",--RIGHT(A1:A100,2))),0) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "gadmire" wrote in message ... I am trying to obtain an average of blood pressure over a period of time. The individual entries are in a single column but are in a combined format (e.g. 130/82). I need to parse each of the two numbers and arrive at an average of all entries in the column. I have searched help because I thought I had seen some reference to the selecting only a portion of a number on a prior search. Unfortunately, I could not come across it again. Thanks for any help |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Average first 3 numbers in column e.g. 130 in 130/82
You could try this to cater for that
=ROUND(AVERAGE(IF((B1:B100<"")*(ISNUMBER(--LEFT(B1:B100,3))),--LEFT(B1:B100 ,3))),0)&"/"& ROUND(AVERAGE(IF((B1:B100<"")*(ISNUMBER(--LEFT(B1:B100,2))),--RIGHT(B1:B100 ,2))),0) still as an array -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "gadmire" wrote in message ... Bob: Thanks for the recommendation. It worked perfectly after I started the formula at B3 ( B1 and B2 contained text which prevented correct operation of the formula). Bob "Bob Phillips" wrote: Reading between the lines =ROUND(AVERAGE(IF(A1:A100<"",--LEFT(A1:A100,3))),0)&"/"&ROUND(AVERAGE(IF(A1 :A100<"",--RIGHT(A1:A100,2))),0) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "gadmire" wrote in message ... I am trying to obtain an average of blood pressure over a period of time. The individual entries are in a single column but are in a combined format (e.g. 130/82). I need to parse each of the two numbers and arrive at an average of all entries in the column. I have searched help because I thought I had seen some reference to the selecting only a portion of a number on a prior search. Unfortunately, I could not come across it again. Thanks for any help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formulas to numbers | New Users to Excel | |||
match and count words | Excel Worksheet Functions | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
Average of numbers in column between to other numbers | Excel Discussion (Misc queries) | |||
I cannot get a column of numbers to sum, why? | Excel Discussion (Misc queries) |