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

Posted on November 4th, 2016 in Formula Challenges , Learn Excel - 70 comments

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.

bp-category-problemYou 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.

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.

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

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

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

    • José Lôbo says:

      =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

      • José Lôbo says:

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

  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.

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

  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?

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

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

  9. Pavel says:

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

  10. Bhavani Seetal says:

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

    This could work.....

  11. Bhavani Seetal says:

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

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

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

  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:

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

  18. Michael (Micky) Avidan says:

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

  19. Jason Morin says:

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

  20. Jeremy Reed says:

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

  21. giz a job in reading berks says:

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

  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:

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

  24. kishor says:

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

  25. Vipul Patel says:

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

  26. ZORRO2005 says:

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

  27. QPRO says:

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

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

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

  28. sam says:

    This works as well
    =INDEX(Grade,MATCH(0,([@DBP]<=DBP)*([@SBP]<=SBP)))

  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:

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

  31. f(x)dx says:

    =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

  32. Kieu Anh says:

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

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

  34. kordelskid says:

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

  35. rajinikanth says:

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

  36. rajinikanth says:

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

  37. Guimar says:

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

  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:
    ="Grade "&MAX(LOOKUP([@SBP],{1,140,160,180},{0,1,2,3}),LOOKUP([@DBP],{1,90,100,110},{0,1,2,3}))

  41. TUSHAR GAUBA says:

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

  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:

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

  45. Imelda says:

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

  46. MohammadShabbir Taibani says:

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

    FInish Formula in 00:03:09

  47. Bill Wisdom says:

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

  48. Dhaval Doshi says:

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

  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

Leave a Reply