average blood pressure readings

[bhmcpfs]

Looking for a formula to average rows of blood pressure readings. Column H contains readings as general format, e.g. 120/80 Have tried numerous formulas using LEFT and RIGHT to isolate #'s to sum/average with no luck at all. Compounding factor is that both #'s can be either 2 or 3 digits. Any guidance would be greatly appreciated.

 

[fairwinds]

Hi,

Use either Data - Text to columns with / as sepearator.

or try these formulas:

Book3
	A	B	C	D
1
2	120/80	120	80
3	10/500	10	500
4
Sheet4

 

[jimboy]

You need to use left or right with find.

If 120/80 is in A1 use these two formulas;

=VALUE(LEFT(A1,FIND("/",A1,1)-1))
=VALUE(RIGHT(A1,LEN(A1)-FIND("/",A1,1)))

Value changes it from text to numeric.

 

[kkknie]

If the data is in C2, use:

=LEFT(C2,FIND("/",C2)-1) to get the first one and

=RIGHT(C2,LEN(C2)-FIND("/",C2)) to get the second.

K

 

[Andrew Poulsom]

Try something like this:

=AVERAGE(VALUE(LEFT(A1:A5,FIND("/",A1:A5)-1)))&"/"&AVERAGE(VALUE(RIGHT(A1:A5,LEN(A1:A5)-FIND("/",A1:A5))))

which is an array formula which must be confirmed with Ctrl+Shift+Enter not just Enter.

 

[bhmcpfs]

I can isolate the #'s in other columns, and then average them and put them back together, and hide my data columns, but I'd prefer to do the average in one formula at the end of the reports (lots of rows of data ~ 20,000 in each report). I think you guys are on the right track though.

 

[bhmcpfs]

Very impressive Andrew! Except for rounding the first # (which I should be able to handle), it looks absolutely beautiful. I tried a whole lot of CSE's (arrays), but didn't get nearly far enough into it. Many many thanks.

 

[IMAKEMISTAKES]

The finishing touches to Andrews fine formula:

=INT(AVERAGE(VALUE(LEFT(H27:H40,FIND("/",H27:H40)-1)))+0.5)&"/"&INT(AVERAGE(VALUE(RIGHT(H27:H40,LEN(H27:H40)-FIND("/",H27:H40))))+0.5)

Needless to say substitute your range where my H27:H40 is - I just pasted directly from my spreadsheet. There can't be any blank values in the range or you will get an error!

 

[kmizser]

Great formulas! What would be the approach if the spreadsheet does contain blank values (and assuming I can't do anything about them). Can the formula be extended to ignore blank cells?