# Can you solve this blood pressure problem? [IF Formula Homework]

Over on Facebook, Kristin asks, *Help, my blood pressure is going thru the roof. I can’t seem to solve this blood pressure problem. *

Okay, I am kidding. That is not what Kristin said. Here we go again…

Let’s simplify Kristin’s problem.

You have some data in the format shown aside.

**Click here to download the sample file.**

And you want to find out the BP category for each reading, using below rules.

- Normal – SBP < 140 AND DBP < 90
- Grade 1 – SBP 140-159 OR DBP 90-99
- Grade 2 – SBP 160-179 OR DBP 100-109
- Grade 3 – SBP >= 180 OR DBP >= 110

*In case of ties, assume the higher grade.*

Use these references:

- B4 or [@DBP] for DBP
- C4 or [@SBP] for SBP

### You have 3 minutes to write the formula

What’s a BP categorization problem with out some pressure. So time yourself when writing this formula. Try to complete it in 3 minutes and post your answers in the comments section.

**Your time starts now….**

### Want more challenges…

Aah, you are the kind that strive under pressure. Here are a few more challenges to keep you busy.

- Sumerian voter problem – If formula challenge
- How many Mondays between two dates?
- Sum of Top 10 values
- Can you calculate sales commission?
*More home work problems*

### Formulas driving your BP up?

If you find navigating the formula world confusing, murky and difficult, we got you covered. Check out my **Formula Crash Course** or **Excel School program**. Both of these offer excellent instruction on formulas for tremendous value for money. Your move, awesome.

### Introducing our Online Power BI Class:

*Would you like to join me on a date with Power BI?* In this comprehensive online class, learn all about Power BI so you can create beautiful, insightful & interactive reports. Join me and rest of the play mates for our first ever Power BI Play Date.

**Click here to know more and join us.**

### Leave a Reply

How to add a line to column chart? [Charting trick] |
Finding the closest school [formula vs. pivot table approach] |

## 86 Responses to “Can you solve this blood pressure problem? [IF Formula Homework]”

Here's my second attempt. I forgot to time myself, but I'm fairly sure it took me longer than 3 minutes, probably around 5-6 minutes.

=IF(OR([@SBP]>=180,[@DBP]>=110),"Grade 3",IF(OR([@SBP]>=160,[@DBP]>=100),"Grade 2",IF(OR([@SBP]>=140,[@DBP]>=90),"Grade 1","Normal")))

I figured the easiest way to test without adding new data, was in a nested if statement. The first part tests if either the SBP is equal or above 180, OR if the DBP is equal or above 110. If even one of these is true, that's a grade 3. If neither is true, then we test for the Grade 2 levels, and so on down.

When I first wrote this, I had it in reverse, i.e., I tested for "Normal" first, then for "Grade 1", then "Grade 2" and everything else was "Grade 3". This didn't work correctly, because of the line which says: "In case of ties, assume the higher grade."

Therefore, you must test for the higher grades first, and anything which doesn't conform to at least one of those levels will fall into the normal grade.

I hope this makes sense.

=SE(E([@DBP]<90;[@SBP]=110;[@SBP]>=180);"Grade 3";SE(OU([@DBP]>100;[@SBP]>160);"Grade 2";"Grade 1")))

My Excel is a portuguese version

=IF(AND([@DBP]<90;[@SBP]=110;[@SBP]>=180);"Grade 3";IF(OR([@DBP]>100;[@SBP]>160);"Grade 2";"Grade 1")))

Fisrt I tested for "Normal", then for "Grade 3", after, for "Grade 2". The formula do not need to test for "Grade 1", as it is the last that rest.

My formula is unwieldy and it took me a lot longer than three minutes: UNTIL, I got out my pencil and paper and cracked it very quickly. BUT everything was wrong until I realised you had switched the order of the categories: DBP and SBP in the table but SBP and DBP in the boundaries.

=IF(AND(B4<=90,C490,B4140,C499,B5159,C5<=179)),"Grade 2","Grade 3")))

Notice, I used <= ... I hope I didn't miss any!

I just know there is something more elegant waiting for us!

I took this a step further a created a matrix with DBP down the side and SBP across the top. I added together the DBP and SBP scores cell by cell and used Conditional Formatting to tell me which category someone was in and that worked pretty well even though it's a big table!

Having seen ZORRO2005's stunning effort, I came back here and found the formula in my original post had been chopped for some reason. My formula is a baby formula compared to that of ZORRO2005 but here it is anyway:

=IF(AND(B4<=90,C4<=140),"Normal",IF(OR(AND(B4>90,B4<=99),AND(C4>140,C4<=159)),"Grade 1",IF(OR(AND(B5>99,B5<=109),AND(C5>159,C5<=179)),"Grade 2","Grade 3")))

Nope! That didn't work either.

Please note, for my own sanity, what is showing here is NOT my final formula.

Please disregard my post and my replies ... apart from this one!

=IF(OR(C4>=180; B4>=110); "Grade 3"; IF(OR(C4>=160; B4>=100); "Grade 2"; IF(OR(C4>=140; B4>=90); "Garde 1"; "Normal"))

This works, but it's not dynamic as I'd prefer, I look forward to see more creative responses!

=IFERROR(INDEX(Category,MAX(RANK([@DBP],([@DBP],DBP),1),RANK([@SBP],([@SBP],SBP),1)),0),$M$8)

Cell M8 is Grade 3 which I had to link such that where rank = 5 (only 4 categories) the formula reverted to Grade 3 as the DBP or SBP was greater than the maximum in the table.

Jason

Hi Jason, when you say " I look forward to see more creative responses!", you add some pressure then? Though I already think yours is creative.

To be very fast, like instantly.... Look in cell U4 there is a solution hidden: =IF(OR(C4>=180,B4>=110);"Grade 3",IF(OR(C4>=160,B4>=100);"Grade 2",IF(OR(C4>=140,B4>=90);"Grade 1";"Normal"))). A goof up by Chandoo?

It is in cell T4 (& then down) in the original file.

Ok, I made a help table. So what?

Range L12:M16 contains the values for SBS (130; 140 ; 160; 180; 200) + in M I set 1 ; 2 ; 3 ; 4; 5 ("the ranks")

Range N12:O16 contains the values for DBP (60; 90 ; 100; 110; 120)

+ in O I set 1 ; 2 ; 3 ; 4; 5 ("the ranks")

Range P12:P16 has the values Normal, grade 1-2-3

So I lookup DBP value and SBP value rank, I take the max. The index match returns the category.

=INDEX($P$12:$P$16,MATCH(MAX(VLOOKUP([@DBP],$N$12:$O$16,2),VLOOKUP([@SBP],$L$12:$M$16,2)),$O$12:$O$16,0))

Took me about 15 minutes and I did not do a quality check (Lunch time and some-one is waiting for me).

I have not created a formula yet but this would be my strategy:

Step 1: Create a new table with 4 columns

SBP DBP SBPDBP Category

Step 2: Create Vlookup with approximate match in result table

Vlookup(SBP&DBP, new table,2,true)

i guess this should work.

Abhay, this was also my initial approach. I got some N/A's and wrong results. Though I guess it could work if you make all possible (16) combinations.

=MAX(COUNTIF(DBP,"<="&B4),COUNTIF(SBP,"<="&C4))

Two named ranges: DBP = {90,99,109} and SBP = {140,159,179}

Result is 0 (normal),1,2 or 3

I try different usage of array directly in formula. So the first three works fine but the last don’t (Countif). Do you know why?

=SUM({90,99,109})

=COUNT({90,99,109})

=MATCH(95,{90,99,109})

=COUNTIF({90,99,109},“>100“)

COUNTIF, SUMIF, AVERAGEIF and their plural versions all need ranges as inputs. They cant be used with arrays.

=IF(AND([@SBP]<140,[@DBP]<90),$P$5,IF(OR([@SBP]<160,[@DBP]<100),$P$6,IF(OR([@SBP]<180,[@DBP]<110),$P$7,$P$8)))

I have added a column where P5 = Normal, P6 = Grade 1, P7 = Grade 2 and P8 = Grade 3.

I first thought of a VLOOKUP with approximate match but would need more than 3 mins to make it so I went for the nested IFs solution, since we do not have that many possible results.

It would be more professional to use references instead of numbers for the limits, but come on, I was trying to cut seconds.

Took me about 2 mins to write the formula, but overall, took me longer because I wasted 2-3 mins trying to compose a VLOOKUP.

Cheers,

Alex

=CHOOSE(MAX(CHOOSE(SUMPRODUCT(1*(B4>={0,140,160,180})),1,2,3,4),CHOOSE(SUMPRODUCT(1*(C4>={0,90,100,110})),1,2,3,4)),"Normal", "Grade 1", "Grade 2", "Grade 3")

Hey Pavel,

I really like you solution!

=IF(AND([@SBP]<140,[@DBP]=140,[@SBP]=90,[@SBP]=160,[@SBP]=100,[@SBP]=180,[@DBP]>=110),"Grade 3","-Err"))))

This could work.....

=IF(AND([@SBP]<140,[@DBP]=140,[@SBP]=90,[@SBP]=160,[@SBP]=100,[@SBP]=180,[@DBP]>=110),"Grade 3","-Err"))))

I am trying to copy and paste the entire formula. But it is taking a few only....

Any help on this would be grateful...

Including a Helper table with the lower threshold for each grade this would be my solution.

INDEX(Tbl[Grade];MAX(Match([@DBP];Tbl[Low DBP];1);Match([@SBP];Tbl[Low SBP];1)))

It seems to work and including the helper table it took me more or less 3 minutes.

=CHOOSE(MAX(MATCH(C4,{0,140,160,180}),MATCH(B4,{0,90,100,110})),"Normal","Level 1","Level 2","Level3")

IF(AND(C4<140,B4=140,C4=90,B4=160,C4=100,B4180,B4>=110),"Grade 3",""))))

OMGoodness. you are all so formula suave'... pretty sure I need more then a crash course in formulas to remember all the ({[]})," and where to put them, to mention which words to =CHOOSE. you are all AMAZING!

That was fun!

=IF(AND(C4<140,B4=140,C4=90,B4=160,C4=100,B4180,B4>=110),"Grade 3",""))))

The following will be more "appropriate".

https://s14.postimg.org/3nqyjai75/NONAME.png

=SUBSTITUTE("Grade "&MAX(SUM(--([@SBP]>{139,159,179})),SUM(--([@DBP]>{89,99,109}))),"Grade 0","Normal")

=IF(AND([@SBP]<140,[@DBP]<90),"Normal",IF(AND([@SBP]<160,[@DBP]<100),"Grade 1",IF(AND([@SBP]<180,[@DBP]<110),"Grade 2","Grade 3")))

=IF(OR([@DBP]>=110,[@SBP]>=180),"Grade 3",IF(OR([@DBP]>=100,[@SBP]>=160),"Grade 2",IF(OR([@DBP]>=90,[@SBP]>=140),"Grade 1","Normal")))

The boring way?

=IF(AND(B4<90,C4<140),"Normal",

IF(OR(B4<=99,C4<=159),1,

IF(OR(B4<=109,C4<=179),2,

3)))

=IF(OR(bp[[#This Row],[SBP]]>=180,bp[[#This Row],[DBP]]>=110),"Grade 3",IF(OR(bp[[#This Row],[SBP]]<180,bp[[#This Row],[DBP]]<110),"Grade 2",IF(OR(bp[[#This Row],[SBP]]<160,bp[[#This Row],[DBP]]<100),"Grade 2",IF(OR(bp[[#This Row],[SBP]]<160,bp[[#This Row],[DBP]]<100),"Geade 1","Normal"))))

Sorry, Earlier post was mistake. Correct would be.

=IF(OR(bp[[#This Row],[SBP]]>=180,bp[[#This Row],[DBP]]>=110),"Grade 3",IF(OR(bp[[#This Row],[SBP]]>159,bp[[#This Row],[DBP]]>99),"Grade 2",IF(OR(bp[[#This Row],[SBP]]>=140,bp[[#This Row],[DBP]]>=90),"Grade 1","Normal")))

I tried. It is really mental pressure task rather than blood pressure

=IF(OR([@SBP]>=180,[@DBP]>=110),"Grade 3",IF(OR(AND([@SBP]<=160,[@DBP]=100,[@DBP]<=109)),"Grade 2",IF(OR(AND([@SBP]<=140,[@DBP]=90,[@DBP]<=99)),"Grade 1",IF(AND([@SBP]<140,[@DBP]<90),"Normal"))))

=TEXT(MAX(MMULT({1,1,1},--(B4:C4>={90,140;100,160;110,180}))),"""Grade ""0;;""Normal""")

brilliant...

Never seen MMULT before; nice work!

Your answer is fantastic!

I had this kind of problem in our research with several BP readings on different days and different drugs and find out if the readings were taken correctly to warrant enrolment by calculating the yield of several readings by several volunteers. Makes sense?

Grt! but can someone explain how =text(,"""Grade ""0;;""Normal""") works?

Karthik, I had the same question. Here is what I found.

Go here and read the "Understanding Excel number format" section.

https://www.ablebits.com/office-addins-blog/2016/07/07/custom-excel-number-format/#zeros-as-dashes

The semicolons indicate separators between

POSITIVE; NEGATIVE; ZERO; TEXT

Applying these example values to the text portion of ZORRO2005's formula {9,-8,0}:

text(9,"""Grade ""0;;""Normal""") = Grade 9

text(-8,"""Grade ""0;;""Normal""") = [blank]

text(0,"""Grade ""0;;""Normal""") = Normal

Excelente!!!

=CHOOSE(INT(MIN(4,(MAX(1,B4/10-7,C4/20-5))),"Normal","Grade 1", "Grade 2","Grade 3")

Boundaries increase in tens for DBP, and twenties for SBP; so INT(B4/10) and INT(C4/20) separate the grades. Pick an adjustment factor for each to shift the set of results to the range 1 to 4, that is INT(B4/10)-7 and INT(C4/20)-5. Select the maximum which must be at least 1, and limit the result to no more than 4, to choose the labels. The outcome is unchanged if a single INT function wraps round the outside, instead of twice in the middle.

Oops! Sorry, I dropped a bracket.

=CHOOSE(INT(MIN(4,(MAX(1,$B4/10-7,$C4/20-5)))),"Normal","Grade 1","Grade 2","Grade 3")

This works as well

=INDEX(Grade,MATCH(0,([@DBP]<=DBP)*([@SBP]<=SBP)))

Grade = {"N";"L1";"L2";"L3"}

DBP = {0;90;100;110}

SBP = {0;140;160;180}

Personally I convert data sets out of tables to prevent the {} or [] referencing, it drives myself and other staff mad.

=IF(AND([@SBP]<140,[@DBP]=140,[@SBP]=90,[@DBP]=160,[@SBP]=100,[@DBP]<=109)),"Grade 2","Grade 3")))

=IF(OR([@SBP]>=180;[@DBP]>=110);"Grade 3";IF(OR([@SBP]>=160;[@DBP]>=100);"Grade 2";IF(OR([@SBP]>=140;[@DBP]>=90);"Grade 1";IF(AND([@SBP]<140;[@DBP]<90);"Normal"))))

took me 5-6 minutes

=IF(OR([SBP]>=180,[DBP]>=110),"Grade 3",IF(OR([SBP]>=160,[DBP]>=100),"Grade 2", IF(OR([SBP]>=140,[DBP]>=90),"Grade 1","Normal")))

This is my solution. Classify it from the top first to close any other possible outcome and shorten the IF clause.

=INDEX($Q$4:$Q$7;MAX(VLOOKUP([@DBP];$L$4:$M$7;2;TRUE);VLOOKUP([@SBP];$N$4:$O$7;2;TRUE)))

With :

$Q$4:$Q$7 = {"Normal";"Grade1";"Grade2";"Grade3"}

$L$4:$M$7 = {0\1;90\2;100\3;110\4}

$N$4:$O$7 = {0\1;140\2;160\3;180\4}

Took me approximately 6 minutes

=IF(OR(C4>=180,B4>=110),"Grade 3",

IF(OR(AND(C4>159,C499,B4139,C489,B4<99)),"Grade 1",

IF(AND(C4<140,B4<90),"Normal",

"Error"))))

This took me about 15 minutes...I use Excel for fun...not for my job...Thanks for the challenge!

=IF(OR([@SBP]>=180,[@DBP]>=110),"Grade 3",IF(OR(AND([@SBP]159),AND([@DBP]99)),"Grade 2",IF(OR(AND([@SBP]139),AND([@DBP]89)),"Grade 1","Normal")))

IF(OR([@SBP]>=180,[@DBP]>=110),"Grade 3",IF(OR(AND([@SBP]159),AND([@DBP]99)),"Grade 2",IF(OR(AND([@SBP]139),AND([@DBP]89)),"Grade 1","Normal")))

=IF(AND([@SBP]<140;[@DBP]139;[@SBP]89;[@DBP]159;[@SBP]99;[@DBP]<110));"Grade 2";"Grade 3")))

Attaching a file. I am assuming there will be better answer than this, but this was the best possible I could think within 180 seconds.

=IF(OR([@SBP]>=$N$6,[@DBP]>=$M$6),$O$6,IF(OR([@SBP]>=$N$5,[@DBP]>=$M$5),$O$5,IF(OR([@SBP]>=$N$4,[@DBP]>=$M$4),$O$4,$P$4)))

I am assuming there has to be better way than this, which I could not think within 3 minutes. Let me know other better options if available.

I think that the art here is in writing a formula with as few function calls as possible. To that end, ZORRO2005 posted something quite clever by invoking some kind of matrix algebra, though I confess I don't understand how it works. Michael (Micky) Avidan created something clever, too:

"=CHOOSE(MAX(MATCH(C4,{0,140,160,180}),MATCH(B4,{0,90,100,110})),"Normal","Level 1","Level 2","Level3")"

and I think I like his solution better than what I came up with, which is:

="Grade "&MAX(LOOKUP([@SBP],{1,140,160,180},{0,1,2,3}),LOOKUP([@DBP],{1,90,100,110},{0,1,2,3}))

=+IF(MAX(IF(B4<90,0,IF(B4<160,1,IF(B4<180,2,3))),IF(C4<90,0,IF(C4<160,1,IF(C4<180,2,3))))=0,"NORMAL",IF(MAX(IF(B4<90,0,IF(B4<160,1,IF(B4<180,2,3))),IF(C4<90,0,IF(C4<160,1,IF(C4<180,2,3))))=1,"GRADE 1",IF(MAX(IF(B4<90,0,IF(B4<160,1,IF(B4<180,2,3))),IF(C4<90,0,IF(C4<160,1,IF(C4<180,2,3))))=2,"GRADE 2","GRADE 3")))

WORKED,,, TOOK 10 MINS

Dude, you used 24 functions and got it to work. You deserve both an award and a beating!

@Pablo,

The formula U R referring to doesn't work for

DBP = 70

SBP = 120

Pls check and comment.

What happen if DBP and SBP are in differents category? For example DBP- Normal and SBP-Grade2.

This is my solution to the BP challenge.

=IF(AND(C:C<140,B:B=140,C:C=90,B:B=160,C:C=100,B:B<=109)),"Grade2","Grade3")))

This works:-)

Grade 1 - SBP 140-159 OR DBP 90-99

[DBP = 104; SBP = 144]

Category -> Answer = Grade 1

=IF(AND([@SBP]<140,[@DBP]140,[@SBP]90,[@DBP]160,[@SBP]100,[@DBP]180,[@SBP]=180),AND([@DBP]>110,[@DBP]=110)),"Grade 3",""))))

=IF(AND([@SBP]<140,[@DBP]140,[@SBP]90,[@DBP]160,[@SBP]100,[@DBP]<=109)),"Grade2","Grade3")))

FInish Formula in 00:03:09

=IF(AND([@SBP]<140,[@DBP]<90),"Normal", IF(AND([@SBP]<=159,[@DBP]<=99),"Grade 1", IF(AND([@SBP]<=179,[@DBP]<=109),"Grade 2", "Grade 3")))

=IF(OR([@SBP]>=180,[@DBP]>=110)=TRUE,"Grade 3",IF(OR([@SBP]>=160,[@DBP]>=100)=TRUE,"Grade 2",IF(OR([@SBP]>=140,[@DBP]>=90)=TRUE,"Grade 1",IF(AND([@SBP]<140,[@DBP]<90)=TRUE,"Normal"))))

Hi

There is no correlation between SBP and DBP... if SBP 90

How do you calculate for the following reading...

If SBP <140 and DBP =140 and >=90

I'd love to see what the formulas will be....

NB: I'm not an expert....I search and copy and then change to my needs..... You guys are great in sharing your knowledge....Guys like me SALUTE you guys for imparting your expertise....Thanks from us

=IF(AND([@SBP]<140,[@DBP]=140,[@SBP]=90,[@DBP]=160,[@SBP]=100,[@DBP]=180,[@DBP]>=110),"Grade 3","Higher"))))

This worked for me and took approx 2 mins to type out

How'd I do. took a about 3 minutes; didn't time it.

Not as neat as some of the formulae above, but I always feel like Choose doesn't get enough love:

=CHOOSE(MAX(IF([@DBP]<90,1,IF([@DBP]<100,2,IF([@DBP]<110,3,4))),IF([@SBP]<140,1,IF([@SBP]<160,2,IF([@SBP]<180,3,4)))),"Normal","Grade 1","Grade 2","Grade 3")

I think it scans fairly well once you get the sense of what it does.

=IF(AND(B4<90,C4=90,B4=140,C4=100,B4=160,C4=110,C4>=180),"Grade3",0))))))

Hi team,

Been learning for years from Chandoo website. First time trying, hope its good.

=IF(AND([@DBP]<90,[@SBP]=90,[@DBP]=140,[@SBP]=100,[@DBP]=160,[@SBP]=110,[@SBP]>=160),"Grade 3", "Not in Category "))))

weird, not pasting properly..

=IF(AND([@DBP]<90,[@SBP]=90,[@DBP]=140,[@SBP]=100,[@DBP]=160,[@SBP]=110,[@SBP]>=160),"Grade 3", "Not in Category "))))

@Ryan

either use GT for Greater Than, GTE for Greater Than or equal etc

or HTML Codes

use &_gt_; for Greater Than, &_ge_; for Greater Than or equal etc "Without the underlines"

=IF(AND([@SBP]<140,[@DBP]<90),"Normal",IF(OR(140159,9099),"Grade 1",IF(OR(160179,100109),"Grade 2",IF(OR([@SBP]>=180,[@DBP]>=110),"Grade 3","Grade 3"))))

=IF(AND([@DBP]<90,[@SBP]<140),"Normal",IF(AND([@DBP]<=99,[@SBP]<=159),"Grade 1",IF(AND([@SBP]<=109,[@SBP]<=179),"Grade 2","Grade 3")))

=IF(AND(B4<90,C4=140,C4=90,B4<=99)), " Grade 1", IF(AND(C4=160,OR(B4>=100,B4<=109)), " Grade 2", " Grade 3")))

=+IF(MAX(IF(B4<90,0,IF(B4<160,1,IF(B4<180,2,3))),IF(C4<90,0,IF(C4<160,1,IF(C4<180,2,3))))=0,"NORMAL",IF(MAX(IF(B4<90,0,IF(B4<160,1,IF(B4<180,2,3))),IF(C4<90,0,IF(C4<160,1,IF(C4<180,2,3))))=1,"GRADE 1",IF(MAX(IF(B4<90,0,IF(B4<160,1,IF(B4<180,2,3))),IF(C4<90,0,IF(C4<160,1,IF(C4<180,2,3))))=2,"GRADE 2","GRADE 3")))

WORKED,,, TOOK 8 MINS.

Let me try:

=IF(AND([@SBP]=110),"Grade 3","Higher"))))

This worked for me, and I guess took approx three mins to type out.

=IF(OR(bp[[#??],[SBP]]>=180,bp[[#??],[DBP]]>=110),3,IF(OR(bp[[#??],[SBP]]>=160,bp[[#??],[DBP]]>=100),2,IF(OR(bp[[#??],[SBP]]>=140,bp[[#??],[DBP]]>=90),1,0)))

=IF(AND(bp[[#??],[SBP]]<140,bp[[#??],[DBP]]<90),"normal",LOOKUP(MAX(LOOKUP(bp[[#??],[DBP]],{0,90,100,110},{1,1,2,3}),LOOKUP(bp[[#??],[SBP]],{0,140,160,180},{1,1,2,3})),{1,1,2,3},{"grade1","grade1","grade2","grade3"}))

correct to this ?……