Home 
Search 
Today's Posts 
#1




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




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




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




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




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 CtrlShiftEnter, 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




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 CtrlShiftEnter, 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




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 CtrlShiftEnter, 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) 