MAX Function Help

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

Guest

I have a column that displays blood pressure readings like this: 124/98. I need to extract the highest reading within that column however using the MAX function only gives me a "0". The cells are set to general. I have tried setting to numbers and utilizing the custom setting with out any luck. Any suggestions? oh I also tried entering the reading like this 124-98 without luck. It does work without the forward slash and hyphen but even using an input mask in the results cell did not go either

Thanks in Advanc

Al
 
Hi
I would suggest you split this information into two cells. Currently
you don't have numbers in your cells. try putting the higher and the
lower pressure value in two adjacent cells. e.g.
A1: 124
B1: 98

Now you can use the MAX function without any problems

--
Regards
Frank Kabel
Frankfurt, Germany

Alvin R. Williams said:
I have a column that displays blood pressure readings like this:
Click to expand...
124/98. I need to extract the highest reading within that column
however using the MAX function only gives me a "0". The cells are set
to general. I have tried setting to numbers and utilizing the custom
setting with out any luck. Any suggestions? oh I also tried entering
the reading like this 124-98 without luck. It does work without the
forward slash and hyphen but even using an input mask in the results
cell did not go either.
 
Al

Here's one way assuming readings in A2:A10
(blanks allowed)

=MAX(IF(A2:A10<>"",VALUE(LEFT(A2:A10,FIND("/",A2:A10)-1))))

This is an array formula, and it must be entered
with <Shift><Ctrl><Enter>, also if edited later.
If done correctly, Excel will display the formula
in the formula bar enclosed in curly brackets { }
Don't enter these brackets yourself.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Alvin R. Williams said:
I have a column that displays blood pressure readings like this: 124/98.
Click to expand...
I need to extract the highest reading within that column however using the
MAX function only gives me a "0". The cells are set to general. I have
tried setting to numbers and utilizing the custom setting with out any luck.
Any suggestions? oh I also tried entering the reading like this 124-98
without luck. It does work without the forward slash and hyphen but even
using an input mask in the results cell did not go either.
 
There has to be a better way than this but it works for range(i8:i11). ARRAY
entered by using control+shift+enter. Someone is bound to shorten it.

=LEFT(INDIRECT("I"&MAX(IF(LEFT(I8:I11,3)<>"",ROW(I8:I11),0))),3)
--
Don Guillett
SalesAid Software
(e-mail address removed)
Alvin R. Williams said:
I have a column that displays blood pressure readings like this: 124/98.
Click to expand...
I need to extract the highest reading within that column however using the
MAX function only gives me a "0". The cells are set to general. I have
tried setting to numbers and utilizing the custom setting with out any luck.
Any suggestions? oh I also tried entering the reading like this 124-98
without luck. It does work without the forward slash and hyphen but even
using an input mask in the results cell did not go either.
 
Better than mine and I found it did NOT have to be array entered. And, since
it would be rare to have a bp less than 100, this also works but MUST be
array entered. Why, I don't know.

=MAX(IF(I8:I11<>"",VALUE(LEFT(I8:I11,3))))
 
Thank you for your quick response. All the suggestions worked find but only gave me the "systolic" reading of the Blood pressure. What is needed is the whole reading.
BP
124/8
128/9
170/11
164/6

Highest reading is 170/11

Again thanks in advanc

al
 
Try this adaptation of Leo's formula

=INDEX(A2:A10,MATCH(MAX(IF(A2:A10<>"",--LEFT(A2:A10,FIND("/",A2:A10)-1))),IF
(A2:A10<>"",--LEFT(A2:A10,FIND("/",A2:A10)-1)),0))

entered with ctrl + shift & enter

--

Regards,

Peo Sjoblom

Alvin R. Williams said:
Thank you for your quick response. All the suggestions worked find but
Click to expand...
only gave me the "systolic" reading of the Blood pressure. What is needed
is the whole reading.
 
It has to be array entered, most likely you had the largest value in the
first cell
since it will return that value if entered normally
 
yes that works!! Now is it possible to show the entire reading showing the highest diastolic (/number) numbe

B
124/8
136/9
177/11

resul
177/11

Thanks for being patien

al
 
Or even:

=INDEX(A2:A10,MATCH(MAX(IF(A2:A10<>"",--LEFT(A2:A10,FIND("/",A2:A10)-1))),
--LEFT(A2:A10,FIND("/",A2:A10)-1),0))

entered with ctrl + shift & enter

--
Best Regards
Leo Heuser

Followup to newsgroup only please.
 
Are you trying to determine the max per component or per reading? Given your
sample below, (a) the max of before slash (systolic component I believe), we
get 177; (b) The max of after slash (dystolic component) is 112; And, (c)
the max of readings (keeping components together) is 177/112. If the
interest is in the latter type of max, what would be the max value
regarding:

124/87
176/115
177/112

Alvin R. Williams said:
yes that works!! Now is it possible to show the entire reading showing the
Click to expand...
highest diastolic (/number) number
 
I discovered that it was picking up the last cell which, by coincidence, was
the largest....
 
One way:

=INDEX(A1:A10,MATCH(MAX(IF(A1:A10<>"",MID(A1:A10,
FIND("/",A1:A10)+1,3)+0)),MID(A1:A10,FIND("/",A1:A10)+1,3)+0,0))

Be aware, that the formulae don't consider duplicates!
They find the *first* occurrence in the list.

E.g.

123/89
140/92
140/100
143/100

If you are looking for the highest systolic reading, the matching formula
will return 140/92.
Likewise, if you are looking for the highest diastolic number, the above
formula will return 140/100.

For a more systematic approach, try this setup:

Sys. Dia. Systolic
Max, Max A
Max, Min B
Min, Max C
Min, Min D

Sys. Dia. Diastolic
Max, Max E
Min, Max F
Max, Min G
Min, Min H

Formula A:
=INT(MAX((IF(IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3))+0=
MAX(IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3)+0)),LEFT(A2:A11,
FIND("/",A2:A11)-1)*1000+IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3)))))/
1000)&"/"&RIGHT(MAX(IF(IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3))+0=
MAX(IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3)+0)),LEFT(A2:A11,
FIND("/",A2:A11)-1)*1000+IF(A2:A11<>"",MID(A2:A11,
FIND("/",A2:A11)+1,3)))),3)+0

Formula B:
=INT(MIN((IF(IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3))+0=
MAX(IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3)+0)),LEFT(A2:A11,
FIND("/",A2:A11)-1)*1000+IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3)))))/
1000)&"/"&RIGHT(MAX(IF(IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3))+0=
MAX(IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3)+0)),LEFT(A2:A11,
FIND("/",A2:A11)-1)*1000+IF(A2:A11<>"",MID(A2:A11,
FIND("/",A2:A11)+1,3)))),3)+0

Formula C:
=INT(MAX((IF(IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3))+0=
MIN(IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3)+0)),LEFT(A2:A11,
FIND("/",A2:A11)-1)*1000+IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3)))))/
1000)&"/"&RIGHT(MIN(IF(IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3))+0=
MIN(IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3)+0)),LEFT(A2:A11,
FIND("/",A2:A11)-1)*1000+IF(A2:A11<>"",MID(A2:A11,
FIND("/",A2:A11)+1,3)))),3)+0

Formula D:
=INT(MIN((IF(IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3))+0=
MIN(IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3)+0)),LEFT(A2:A11,
FIND("/",A2:A11)-1)*1000+IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3)))))/
1000)&"/"&RIGHT(MIN(IF(IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3))+0=
MIN(IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3)+0)),LEFT(A2:A11,
FIND("/",A2:A11)-1)*1000+IF(A2:A11<>"",MID(A2:A11,
FIND("/",A2:A11)+1,3)))),3)+0

Formula E:
=INT(MAX((IF(IF(A2:A11<>"",LEFT(A2:A11,FIND("/",A2:A11)-1))+0=
MAX(IF(A2:A11<>"",LEFT(A2:A11,FIND("/",A2:A11)-1)+0)),LEFT(A2:A11,
FIND("/",A2:A11)-1)*1000+IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3)))))/
1000)&"/"&RIGHT(MAX(IF(IF(A2:A11<>"",LEFT(A2:A11,FIND("/",A2:A11)-1))+0=
MAX(IF(A2:A11<>"",LEFT(A2:A11,FIND("/",A2:A11)-1)+0)),LEFT(A2:A11,
FIND("/",A2:A11)-1)*1000+IF(A2:A11<>"",MID(A2:A11,
FIND("/",A2:A11)+1,3)))),3)+0

Formula F:
=INT(MAX((IF(IF(A2:A11<>"",LEFT(A2:A11,FIND("/",A2:A11)-1))+0=
MAX(IF(A2:A11<>"",LEFT(A2:A11,FIND("/",A2:A11)-1)+0)),LEFT(A2:A11,
FIND("/",A2:A11)-1)*1000+IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3)))))/
1000)&"/"&RIGHT(MIN(IF(IF(A2:A11<>"",LEFT(A2:A11,FIND("/",A2:A11)-1))+0=
MAX(IF(A2:A11<>"",LEFT(A2:A11,FIND("/",A2:A11)-1)+0)),LEFT(A2:A11,
FIND("/",A2:A11)-1)*1000+IF(A2:A11<>"",MID(A2:A11,
FIND("/",A2:A11)+1,3)))),3)+0

Formula G:
=INT(MIN((IF(IF(A2:A11<>"",LEFT(A2:A11,FIND("/",A2:A11)-1))+0=
MIN(IF(A2:A11<>"",LEFT(A2:A11,FIND("/",A2:A11)-1)+0)),LEFT(A2:A11,
FIND("/",A2:A11)-1)*1000+IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3)))))/
1000)&"/"&RIGHT(MAX(IF(IF(A2:A11<>"",LEFT(A2:A11,FIND("/",A2:A11)-1))+0=
MIN(IF(A2:A11<>"",LEFT(A2:A11,FIND("/",A2:A11)-1)+0)),LEFT(A2:A11,
FIND("/",A2:A11)-1)*1000+IF(A2:A11<>"",MID(A2:A11,
FIND("/",A2:A11)+1,3)))),3)+0

Formula H:
=INT(MIN((IF(IF(A2:A11<>"",LEFT(A2:A11,FIND("/",A2:A11)-1))+0=
MIN(IF(A2:A11<>"",LEFT(A2:A11,FIND("/",A2:A11)-1)+0)),LEFT(A2:A11,
FIND("/",A2:A11)-1)*1000+IF(A2:A11<>"",MID(A2:A11,FIND("/",A2:A11)+1,3)))))/
1000)&"/"&RIGHT(MIN(IF(IF(A2:A11<>"",LEFT(A2:A11,FIND("/",A2:A11)-1))+0=
MIN(IF(A2:A11<>"",LEFT(A2:A11,FIND("/",A2:A11)-1)+0)),LEFT(A2:A11,
FIND("/",A2:A11)-1)*1000+IF(A2:A11<>"",MID(A2:A11,
FIND("/",A2:A11)+1,3)))),3)+0

Each formula is an array formula and must be entered with
<Shift><Ctrl><Enter>, also if edited later.
Each formula is entered as one line.

With this setup in A2:A11

125/78
123/100
158/120
160/88
160/120
123/78
127/98
Blank
126/98
160/90


this is the returned table:

Sys. Dia. Systolic
Max, Max 160/120
Max, Min 160/88
Min, Max 123/100
Min, Min 123/78

Sys. Dia. Diastolic
Max, Max 160/120
Min, Max 158/120
Max, Min 125/78
Min, Min 123/78


I hope, you can use it, not because I have been patient,
but because I'm beginning to feel like one too :-)

Thanks for an interesting problem :-)

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Alvin R. Williams said:
yes that works!! Now is it possible to show the entire reading showing the
Click to expand...
highest diastolic (/number) number
 
[Snip]
Each formula is an array formula and must be entered with
<Shift><Ctrl><Enter>, also if edited later.
Each formula is entered as one line.

With this setup in A2:A11

125/78
123/100
158/120
160/88
160/120
123/78
127/98
Blank
126/98
160/90


this is the returned table:

Sys. Dia. Systolic
Max, Max 160/120
Max, Min 160/88
Min, Max 123/100
Min, Min 123/78

Sys. Dia. Diastolic
Max, Max 160/120
Min, Max 158/120
Max, Min 125/78
Min, Min 123/78


I hope, you can use it, not because I have been patient,
but because I'm beginning to feel like one too :-)

Thanks for an interesting problem :-)
Click to expand...


Me too by looking at those formulas <vbg>
 
Thanks to Frank, Leo, Don, Peo and Aladin for all your help in this solution. Leo hit it with the following

{=INDEX(C2:C50,MATCH(MAX(IF(C2:C50<>"",MID(C2:C50,FIND("/",C2:C50)+1,3)+0)),MID(C2:C50,FIND("/",C2:C50)+1,3)+0,0))

And with the additional formula variablesthat Leo typed in

As you are aware your circulatory system is a closed system and your blood pressure is a measurement on how your heart functions during pressure contractions (systolic) and pressure rest (diastolic). It is crucial that your heart rests at a certain
pressure ( Ideally the norm is about 120/80). The term high blood pressure or hypertension is the abnormally high systolic and diastolic pressure measurement. The diastolic pressure (/number) must be maintained below 100. Anything above that will cause undo stress on the heart muscle and circulatory arteries

This formula will help me accurately document the blood pressure and heart rate of one of my family members. This information will also assist the Doc's in treatment

Again Thank

Al
 
You're welcome, Al, and thanks for
the feedback. It's appreciated.

Thanks for the info. I feel a lot better now :-)

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Alvin R. Williams said:
Thanks to Frank, Leo, Don, Peo and Aladin for all your help in this
Click to expand...
solution. Leo hit it with the following:
{=INDEX(C2:C50 said:
And with the additional formula variablesthat Leo typed in.

As you are aware your circulatory system is a closed system and your
Click to expand...
blood pressure is a measurement on how your heart functions during pressure
contractions (systolic) and pressure rest (diastolic). It is crucial that
your heart rests at a certain
pressure ( Ideally the norm is about 120/80). The term high blood
Click to expand...
pressure or hypertension is the abnormally high systolic and diastolic
pressure measurement. The diastolic pressure (/number) must be maintained
below 100. Anything above that will cause undo stress on the heart muscle
and circulatory arteries.
This formula will help me accurately document the blood pressure and
Click to expand...
heart rate of one of my family members. This information will also assist
the Doc's in treatment.
 
Did you get the BP monitoring program with chart, etc that I sent?

--
Don Guillett
SalesAid Software
(e-mail address removed)
Alvin R. Williams said:
Thanks to Frank, Leo, Don, Peo and Aladin for all your help in this
Click to expand...
solution. Leo hit it with the following:
{=INDEX(C2:C50 said:
And with the additional formula variablesthat Leo typed in.

As you are aware your circulatory system is a closed system and your
Click to expand...
blood pressure is a measurement on how your heart functions during pressure
contractions (systolic) and pressure rest (diastolic). It is crucial that
your heart rests at a certain
pressure ( Ideally the norm is about 120/80). The term high blood
Click to expand...
pressure or hypertension is the abnormally high systolic and diastolic
pressure measurement. The diastolic pressure (/number) must be maintained
below 100. Anything above that will cause undo stress on the heart muscle
and circulatory arteries.
This formula will help me accurately document the blood pressure and
Click to expand...
heart rate of one of my family members. This information will also assist
the Doc's in treatment.
 
Back
Top