software

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


You can highlight the column and then do Data gt; 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
quot;gadmirequot; wrote:

gt; I am trying to obtain an average of blood pressure over a period of time.
gt; The individual entries are in a single column but are in a combined format
gt; (e.g. 130/82). I need to parse each of the two numbers and arrive at an
gt; average of all entries in the column. I have searched help because I thought
gt; I had seen some reference to the selecting only a portion of a number on a
gt; prior search. Unfortunately, I could not come across it again. Thanks for
gt; any help

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
quot;CLRquot; wrote:

gt;
gt; You can highlight the column and then do Data gt; TextToColumns, and use the
gt; / as the delimiter.........this will separate the diastolic and systolic
gt; numbers each into their own column.........then just average each column
gt; using the =AVERAGE(range) formula.
gt;
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;
gt;
gt; quot;gadmirequot; wrote:
gt;
gt; gt; I am trying to obtain an average of blood pressure over a period of time.
gt; gt; The individual entries are in a single column but are in a combined format
gt; gt; (e.g. 130/82). I need to parse each of the two numbers and arrive at an
gt; gt; average of all entries in the column. I have searched help because I thought
gt; gt; I had seen some reference to the selecting only a portion of a number on a
gt; gt; prior search. Unfortunately, I could not come across it again. Thanks for
gt; gt; any help

If your first number is always three digits and your second two digits,
try...

=AVERAGE(IF(A1:A100lt;gt;quot;quot;,LEFT(A1:A100,3) 0))

and

=AVERAGE(IF(A1:A100lt;gt;quot;quot;,RIGHT(A1:A100,2) 0))

Otherwise, try...

=AVERAGE(IF(A1:A100lt;gt;quot;quot;,MID(A1:A100,1,FIND(quot;/quot;,A1:A100)-1) 0))

and

=AVERAGE(IF(A1:A100lt;gt;quot;quot;,MID(A1:A100,FIND(quot;/quot;,A1:A100) 1,1024) 0))

These formulas need to be confirmed with CONTROL SHIFT ENTER, not just
ENTER.

Hope this helps!

In article gt;,
quot;gadmirequot; gt; wrote:

gt; Chuck:
gt;
gt; My only problem with the below solution is that I have multiple columns next
gt; to the column I want to average. By using the below method, I would need to
gt; move data in many other columns to add the new column (resulting from the
gt; split of the numbers). This would be somewhat impractical for me.
gt;
gt; I seem to recall a formula that conditions either an average or sum that
gt; simply takes a specified number of spaces from an entry and calculates the
gt; result using, say, only the first three digits. If I could find this
gt; formula, it would eliminate the need to create new columns.
gt;
gt; Does the above ring any bells to you?
gt;
gt; Que le vaya bien,
gt;
gt; Gary
gt;
gt;
gt;
gt; quot;CLRquot; wrote:
gt;
gt; gt;
gt; gt; You can highlight the column and then do Data gt; TextToColumns, and use the
gt; gt; / as the delimiter.........this will separate the diastolic and systolic
gt; gt; numbers each into their own column.........then just average each column
gt; gt; using the =AVERAGE(range) formula.
gt; gt;
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;gadmirequot; wrote:
gt; gt;
gt; gt; gt; I am trying to obtain an average of blood pressure over a period of time.
gt; gt; gt;
gt; gt; gt; The individual entries are in a single column but are in a combined
gt; gt; gt; format
gt; gt; gt; (e.g. 130/82). I need to parse each of the two numbers and arrive at an
gt; gt; gt; average of all entries in the column. I have searched help because I
gt; gt; gt; thought
gt; gt; gt; I had seen some reference to the selecting only a portion of a number on
gt; gt; gt; a
gt; gt; gt; prior search. Unfortunately, I could not come across it again. Thanks
gt; gt; gt; for
gt; gt; gt; any help

Reading between the lines

=ROUND(AVERAGE(IF(A1:A100lt;gt;quot;quot;,--LEFT(A1:A100,3))),0)amp;quot;/quot;amp;ROUND(AVERAGE(IF(A1
:A100lt;gt;quot;quot;,--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)

quot;gadmirequot; gt; wrote in message
...
gt; I am trying to obtain an average of blood pressure over a period of time.
gt; The individual entries are in a single column but are in a combined format
gt; (e.g. 130/82). I need to parse each of the two numbers and arrive at an
gt; average of all entries in the column. I have searched help because I
thought
gt; I had seen some reference to the selecting only a portion of a number on a
gt; prior search. Unfortunately, I could not come across it again. Thanks
for
gt; any help
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).

Bobquot;Bob Phillipsquot; wrote:

gt; Reading between the lines
gt;
gt; =ROUND(AVERAGE(IF(A1:A100lt;gt;quot;quot;,--LEFT(A1:A100,3))),0)amp;quot;/quot;amp;ROUND(AVERAGE(IF(A1
gt; :A100lt;gt;quot;quot;,--RIGHT(A1:A100,2))),0)
gt;
gt; which is an array formula, it should be committed with Ctrl-Shift-Enter, not
gt; just Enter.
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;gadmirequot; gt; wrote in message
gt; ...
gt; gt; I am trying to obtain an average of blood pressure over a period of time.
gt; gt; The individual entries are in a single column but are in a combined format
gt; gt; (e.g. 130/82). I need to parse each of the two numbers and arrive at an
gt; gt; average of all entries in the column. I have searched help because I
gt; thought
gt; gt; I had seen some reference to the selecting only a portion of a number on a
gt; gt; prior search. Unfortunately, I could not come across it again. Thanks
gt; for
gt; gt; any help
gt;
gt;
gt;

You could try this to cater for that

=ROUND(AVERAGE(IF((B1:B100lt;gt;quot;quot;)*(ISNUMBER(--LEFT(B1:B100,3))),--LEFT(B1:B100
,3))),0)amp;quot;/quot;amp;
ROUND(AVERAGE(IF((B1:B100lt;gt;quot;quot;)*(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)

quot;gadmirequot; gt; wrote in message
...
gt; Bob:
gt;
gt; Thanks for the recommendation.
gt;
gt; It worked perfectly after I started the formula at B3 ( B1 and B2
contained
gt; text which prevented correct operation of the formula).
gt;
gt; Bob
gt;
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Reading between the lines
gt; gt;
gt; gt;
=ROUND(AVERAGE(IF(A1:A100lt;gt;quot;quot;,--LEFT(A1:A100,3))),0)amp;quot;/quot;amp;ROUND(AVERAGE(IF(A1
gt; gt; :A100lt;gt;quot;quot;,--RIGHT(A1:A100,2))),0)
gt; gt;
gt; gt; which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
gt; gt; just Enter.
gt; gt;
gt; gt; --
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from the email address if mailing direct)
gt; gt;
gt; gt; quot;gadmirequot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I am trying to obtain an average of blood pressure over a period of
time.
gt; gt; gt; The individual entries are in a single column but are in a combined
format
gt; gt; gt; (e.g. 130/82). I need to parse each of the two numbers and arrive at
an
gt; gt; gt; average of all entries in the column. I have searched help because I
gt; gt; thought
gt; gt; gt; I had seen some reference to the selecting only a portion of a number
on a
gt; gt; gt; prior search. Unfortunately, I could not come across it again.
Thanks
gt; gt; for
gt; gt; gt; any help
gt; gt;
gt; gt;
gt; gt;