Solved

# Excel: Formula to Return an Answer Closest to a Target

Posted on 2013-02-02
805 Views
Hi. Please see the attached example spreadsheet. Just looking for a formula (no arrays, please) that returns a blood pressure reading from a list that is closest to the ideal blood pressure of 120/80.

Thanks!
EE1.xlsx
0
Question by:Cactus1994
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 7
• 4
• 4
• +2

LVL 38

Expert Comment

ID: 38846847
So, in the sample, you're looking for the values of Tuesday?
0

Author Comment

ID: 38846862
gerwinjansen:

Though the Systolic value for Wednesday is exactly 120, the Diastolic value of 91 is quite a way off from the ideal 80 reading.

Collectively, Tuesday's reading is closer to the ideal 120/80 target I'm looking for (1 away from the ideal 120 Systolic reading, and 2 away from the ideal 80 Diastolic reading.)

Thanks!
0

LVL 38

Expert Comment

ID: 38846870
See attached sample (yes: I meant Tuesday)
ee-find-lowest.xlsx
0

LVL 40

Expert Comment

ID: 38846888
Same idea as used in gerwinjansen example, but according your request. You should check logic in criteria column. Maybe you should add weights
EE1.xlsx
0

Author Comment

ID: 38846904
als315:
Thanks -- the formula and return value is what I need, and in the format, too.

Any way I can do this without having to add columns D & E?

The actual spreadsheet this is needed for is comprehensive and as a result, cramped for columns. I know I could add columns and hide them, but recently there is a lot of formatting and re-formatting going on, and columns constantly reappear and become unhidden and confuse things going forward.

Thanks again.
0

LVL 92

Expert Comment

ID: 38846910
Cactus1994,

Please state in a sentence or two the rule(s) to follow to determine the "best" match.

For example, if you were trying to match 120 / 80 to the following readings, which one wins, and why?

115 / 80
125 / 80
120 / 75
120 / 85

Patrick
0

LVL 38

Expert Comment

ID: 38846915
So you are not allowing arrays and no extra columns?
0

LVL 92

Expert Comment

ID: 38846919
Cactus1994,

You mentioned above that you like als315's approach.

This is not meant as a criticism, but using that approach, which reading would you consider "best":

121 / 90
126 / 85

als315's approach rates them the same.  Now, which would you pick, and why?

Patrick
0

LVL 38

Expert Comment

ID: 38846928
@matthewspatrick - some sort of weight factor must be included, for example: negative deviation of Diastolic 0.1, positive deviation of Diastolic 0.15 - same for deviations of Systolic.
0

LVL 92

Expert Comment

ID: 38846932
gerwinjansen,

I agree.  But it should be up to the Asker to define just what that weighting scheme should be :)

One thing I considered was to take the absolute differences in systolic and diastolic, as als315 did, but to multiply them instead of add them.

There are any number of possible rules that could be applied.  We need guidance from the Asker as to what the rule(s) should be, and so far there has been little to no guidance...

Patrick
0

Author Comment

ID: 38846974
Thanks, guys.

Absent of weightings, in regard to matthewspatrick's question, he nailed it (and me) in that mathematically (and in the absolute) they would all be deserving of a "best" rating, considering each reading of Systolic and Diastolic numbers are individually +/- 5 points of the ideal target. Obviously, the percentage difference of a 5-point variance on the lower diastolic target of 80 would be greater, but that doesn't answer your question properly. The weightings would have to be more clinical than mathematic, and I apologize for not considering this would be a factor before I posted the question.

Below is the chart I use in the much larger and more comprehensive analysis. Again in regard to matthewspatrick's question, 115/80 and 120/75 would be the best of the four examples, as they all would fall into the "Normal" range on the chart, while the other two would be in the "PreHypertension" range. (You obviously could not have known that without the chart.)

What now is the best way to write a formula, based on the chart parameters, relative to the original question?

0

LVL 92

Expert Comment

ID: 38847056
Now we're getting somewhere :)

So, just to clarify: would you always give preference to a reading in the same clinical area on the chart?  I ask because if the baseline reading happens to be, say, 119/79, which is just barely within normal, it would arguably be closer to 121/81 (prehypertension) than to 92/62 (normal).

Not trying to be difficult, just trying to get you the answer you need :)
0

LVL 40

Expert Comment

ID: 38847095
You can use conditional formatting and get something like this (look at sample)
EE1.xlsx
0

Author Comment

ID: 38847105
matthewspatrick:
No, I love your thought process. I wish everyone I worked with in my day job drilled down to the details to get the best results! :)

Good question. I think we have to consider "LOW" to be as concerning as "PREHYPERTENSION."

That said, a 92/62 would be "better" than 119/79. If the readings were equal and a choice between them had to be made for the result, I would give preference to the lower of the two equal readings.
0

LVL 40

Expert Comment

ID: 38847143
If you like to have "best" value, you can use this sample (hide or change color of cells, which you don't like to see)
EE1.xlsx
0

Author Comment

ID: 38847155
als315:
Thanks. You're thinking of all good ideas for various features of the data, and I like it. However, I've got quite a comprehensive analysis going (this is part of a larger health-oriented program) and I have already used conditional formatting for comparison data, and the "Normal," "Low," "Prehypertension," etc. qualifiers in a text and data based result in other areas of the program already. All good stuff.

What I'm really looking for here is just the "best" result from a data table as in my example, in the ###/## format, using the parameters of the BP chart to determine the "best" reading as they fall within the 90/60 - 120/80 range.

Thanks again.
0

LVL 81

Accepted Solution

byundt earned 500 total points
ID: 38848005
Although it is a defensible approach, I don't believe minimizing the sum of the absolute values is the best way of finding the closest reading.

One way of looking at the problem is to treat the systolic and diastolic readings as  orthogonal axes (i.e. like an XY plot). The closest reading is one that minimizes the distance between the ideal value and the set of actual readings.

You can use the Pythagorean theorem to find that distance as SQRT((Systolic-120)^2 + (Diastolic-80)^2). Since the request is to find the closest reading, you can eliminate the SQRT bit from the problem and just look for the minimum sum of squares--it will also be the minimum square root::
=MIN((Systolic-120)^2 + (Diastolic-80)^2)                Systolic and Diastolic are a range of values

The matching blood pressure reading will then be:
=INDEX(B2:B8,MATCH(MIN((B2:B8-120)^2+(C2:C8-80)^2),(B2:B8-120)^2+(C2:C8-80)^2,0)) & "/" & INDEX(C2:C8,MATCH(MIN((B2:B8-120)^2+(C2:C8-80)^2),(B2:B8-120)^2+(C2:C8-80)^2,0))

The MIN and INDEX formulas above are regrettably array formulas. I believe it will be necessary to choose one of the following evils:
a. Use a VBA user-defined function to hide all the nastiness and return the closest reading
b. Use an array-entered formula as suggested above
c. Use an auxiliary column (just one) that contains the sum of squares for each reading. If you put up with the auxiliary column, then a regular formula can be used like the following:
=INDEX(B2:B8,MATCH(MIN(D2:D8),D2:D8,0)) & "/" & INDEX(C2:C8,MATCH(MIN(D2:D8),D2:D8),0)
The auxiliary column (D2:D8) contains a formula like:
=(B2-120)^2+(C2-80)^2

EE1-least-squares.xlsx
0

LVL 81

Expert Comment

ID: 38848022
The colored chart showing low, normal, pre-hypertensive, etc. provides a dangerously misleading picture, in my opinion. Blood pressures of 91/41 and 50/70 are both colored green, suggesting that they are "normal" -- but I think a patient exhibiting either of those readings would not be very healthy.

The better way to make the plot would be to show a minimum and maximum systolic pressure for a "normal" patient at each of the diastolic pressures between 60 and 80 mm Hg. The area between those two lines would then be colored green and labeled "normal". The boundaries for pre-hypertensive and Stage I and Stage II hypertension can be determined similarly.

A chart made this way would not show any color for physiologically impossible situations like systolic pressure being less than diastolic. There would thus be a 45 degree line (systolic = diastolic) on the chart, with all the blood pressure diagnostic ranges being above that line.

0

Author Comment

ID: 38848039

Incredible responses you've provided. I am constantly amazed at the replies I get to various questions on EE, and yours is one of the most impressive. I'm plugging your reply into my larger spreadsheet now.

Regarding your first post, I am inserting the extra column and using the formula you provided, rather than the array solution. Thank you for both. I had no idea doing something like my this would be as complex as it is, but certainly knew it was well outside my abilities, and understand an added column is absolutely necessary if indeed I need to use a formula instead of an array.

I agree that the colored chart is a bit misleading. Using the example of the 50/70 reading, I would also agree that Px would not be very healthy. Perhaps the makers of this chart intended the X and Y axes to be viewed independently, although their shading would indicate otherwise and leads to misinterpretation. If the X axis systolic value of 50 was viewed independently as landing in the LOW category, and the Y axis diastolic value of 70 was viewed independently as landing in the NORMAL category, then taking two separate indicators away from the chart would be necessary, but the intersection point would not, as XY charts are intended.

Here's another chart that shows the two readings better. I chose not to include it in my original larger analysis spreadsheet, as it is more complex than simple ... or so I thought. At least this one isn't subject to misinterpretation of a 50/70 reading.

0

Author Closing Comment

ID: 38848070

Everything works perfectly after plugging in your solutions to my larger analysis spreadsheet. Again, I am in awe of the solutions you provided.

I truly appreciate your time, your expertise, your explanations, insight, and of course, the exact solution I was looking for.

Thank you!
0

LVL 81

Expert Comment

ID: 38849552
Tim,

Thanks for the kind words! They are much appreciated on a forum like Experts Exchange.

After sleeping on the problem, I decided that my discomfort with the graphical representation of a range of blood pressures into diagnostic groups for normal, Stage 1, Stage 2, etc. is the possibility that the systolic and diastolic readings may belong to different categories. After some more thought, I realized that the diastolic reading is influenced by renal functioning while the difference between systolic and diastolic depends more on the vascular system. With that in mind, it is both possible and meaningful for a patient to belong to two different diagnostic categories.

That said, I decided to flesh out my previous suggestion of defining min and max systolic pressures for each different diastolic pressure. To do this, I took the differences between systolic and diastolic at the beginning and end of each of the ranges shown in your second chart. By adding the smaller difference to the diastolic, I got a minimum corresponding systolic pressure. Likewise, by adding the larger difference, I got the maximum corresponding systolic. Anywhere between the min and max systolic pressure can be clearly assigned to a single diagnostic category. But if you are outside that range, then the vascular and renal functioning belong to different categories.

I also decided to take the square root in the auxiliary column for determining distance compared to the ideal 120/80 reading. You won't need to change the INDEX and MATCH formula that brings back the closest reading, but it will be easier to explain the concept of distance to somebody else if you actually take the square root of the sum of the squares. See attached workbook for implementation.

EE1-least-squares.xlsx
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

NEED TO LOOK FOR EXACT NUMBER 14 43
I need to extract city and state from a cell in Excel 6 31
Excel Date Question 8 46
Excel to show a dynamic Picklist at level2 2 23
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst youâ€™ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dâ€¦
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templaâ€¦
###### Suggested Courses
Course of the Month6 days, 6 hours left to enroll