Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
gadmire
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
CLR
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
gadmire
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Domenic
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
gadmire
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
formulas to numbers Steve New Users to Excel 2 September 16th 05 12:07 AM
match and count words David Excel Worksheet Functions 5 July 4th 05 02:24 AM
Lookup Table Dilemma Karen Excel Worksheet Functions 2 June 10th 05 08:22 PM
Average of numbers in column between to other numbers Ditandhischeese Excel Discussion (Misc queries) 2 March 31st 05 03:35 AM
I cannot get a column of numbers to sum, why? HHG Excel Discussion (Misc queries) 8 February 8th 05 01:55 PM


All times are GMT +1. The time now is 09:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"