Remember Me? 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

 CLR Posts: n/a 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

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

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

 Domenic Posts: n/a 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 ,

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

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

 Bob Phillips Posts: n/a Average first 3 numbers in column e.g. 130 in 130/82

=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)

...
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 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:

=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)

...
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

 Bob Phillips Posts: n/a 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)

...
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:

=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)

...
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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Steve New Users to Excel 2 September 16th 05 12:07 AM David Excel Worksheet Functions 5 July 4th 05 02:24 AM Karen Excel Worksheet Functions 2 June 10th 05 08:22 PM Ditandhischeese Excel Discussion (Misc queries) 2 March 31st 05 03:35 AM HHG Excel Discussion (Misc queries) 8 February 8th 05 01:55 PM

All times are GMT +1. The time now is 12:16 PM. Copyright ©2004-2019 ExcelBanter.