Search

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

Share

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.

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.

### Want more challenges…

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

### 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.

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

### How to fix SPILL Error in Excel Tables (3 easy solutions)

So you have a SPILL error in your Excel tables? In this quick article, let me show you 3 easy fixes to the problem. Fix

## Related Tips

Excel Howtos

### How to fix SPILL Error in Excel Tables (3 easy solutions)

Financial Modeling

Excel Howtos

Featured

Learn Excel

Excel Howtos

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

1. TheQ47 says:

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.

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.

• José Lôbo says:

My Excel is a portuguese version

• José Lôbo says:

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.

• Milinda Jayasekara says:

2. 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.

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:

• 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!

3. Asel says:

=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"))

4. Jason says:

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

• GraH says:

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?

• GraH says:

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

5. GraH says:

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).

6. Abhay says:

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.

• GraH says:

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.

7. Daniel H says:

=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

• Pavel says:

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“)

• Chandoo says:

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

8. Alexandre says:

=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 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

9. Pavel says:

• Alexandre says:

Hey Pavel,
I really like you solution!

10. Bhavani Seetal says:

This could work.....

11. Bhavani Seetal says:

12. Bhavani Seetal says:

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

Any help on this would be grateful...

13. Anton says:

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

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

14. Michael (Micky) Avidan says:

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

• Gopi Krishna kaivaram says:

15. Patty says:

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!

16. Judie B. says:

That was fun!

17. Gopi Krishna kaivaram says:

18. Michael (Micky) Avidan says:

The following will be more "appropriate".
https://s14.postimg.org/3nqyjai75/NONAME.png

19. Jason Morin says:

20. Jeremy Reed says:

21. giz a job in reading berks says:

The boring way?

22. Cliff Beacham says:

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

23. kishor says:

24. kishor says:

Sorry, Earlier post was mistake. Correct would be.

25. Vipul Patel says:

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

26. ZORRO2005 says:

• Chandoo says:

brilliant...

• EXCELent Bacon says:

• Harry says:

Never seen MMULT before; nice work!

• Stephen says:

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?

• Karthik says:

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

• Curtis says:

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

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

The semicolons indicate separators between
POSITIVE; NEGATIVE; ZERO; TEXT

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

• Martin Cruz says:

Excelente!!!

27. QPRO says:

• QPRO says:

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.

• QPRO says:

Oops! Sorry, I dropped a bracket.

28. sam says:

This works as well

• sam says:

DBP = {0;90;100;110}
SBP = {0;140;160;180}

29. Harry says:

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

30. Sandeep Sharma says:

31. f(x)dx says:

took me 5-6 minutes

32. Kieu Anh says:

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

33. B-art in XL says:

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

With :
\$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

34. kordelskid says:

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!

35. rajinikanth says:

36. rajinikanth says:

37. Guimar says:

38. Samir says:

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.

39. Samir says:

=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.

40. Jason C says:

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:

41. TUSHAR GAUBA says:

WORKED,,, TOOK 10 MINS

• Pablo says:

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

• Michael (Micky) Avidan says:

@Pablo,
The formula U R referring to doesn't work for
DBP = 70
SBP = 120
Pls check and comment.

42. Erick Blandon says:

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

43. Kai says:

This is my solution to the BP challenge.

44. Kai says:

45. Imelda says:

This works:-)
Grade 1 - SBP 140-159 OR DBP 90-99
[DBP = 104; SBP = 144]

FInish Formula in 00:03:09

47. Bill Wisdom says:

48. Dhaval Doshi says:

49. Sew Sewram says:

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

50. Hazel says:

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

51. Bob Calnan says:

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

52. Michael says:

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

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

53. arjun rathod says:

54. Ryan says:

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 "))))

55. Ryan says:

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 "))))

• Hui... says:

@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"

56. SK says:

57. Debra says:

58. Abhinav says:

WORKED,,, TOOK 8 MINS.

60. Moe Benatar says:

Let me try:

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

61. sylar xu says:

=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)))

62. sylar xu says:

correct to this ?……

63. Peter B says:

= MAX( MATCH([@SBP], Threshold[SBP]), MATCH([@DBP], Threshold[DBP]) ) - 1
where Threshold is a table of reference values - could be Named Array constants if preferred. The sneaky bit was that I used a custom number format
to display the result rather than performing an additional lookup.

64. Peter B says:

An alternative is to use a named formula

= MAX(
( ( Readings[@SBP]>=Threshold[SBP]) * Threshold[level] ),
( ( Readings[@DBP]>=Threshold[DBP]) * Threshold[level] ) )

since that will evaluate as an array formula without the strain of having to coordinate multiple digits (CSE).

65. mulraj says:

This seems to work. Please comment if ok

66. Suraj says:

67. John Manegene says:

=IFS(AND([@SBP]<140,[@DBP]=140,[@SBP]=90,
[@DBP]=160,[@SBP]=100,
[@DBP]=180),([@DBP]>=110)),

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.