Average first 3 numbers in column e.g. 130 in 130/82

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 > 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
 
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
 
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!
 
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)
 
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
 
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)
 
Back
Top