Ask Your Question

How to find where in the interval a number is - For a particular category

asked 2019-11-19 21:01:50 +0200

migueldealmeida gravatar image

updated 2019-11-19 21:17:34 +0200

In this thread we found a clever way to find where in an interval a number is.

Now consider a more complex case, where we have multiple intervals (e.g an interval for each drug) and you want to find "where in the interval of the matching drug" your number is.

See the image below for an illustration, and the attached file with the example: C:\fakepath\Find in interval - complex example.ods

image description

I am thinking about using a SUMPRODUCT to automate the "calculated A Score" (and have similar columns for "caculated B score" and so on), but am struggling to find a way to integrate the previous finding into this example.

Note that the "score table" is something that I have control with, so I can change it to a different format to accommodate the solution.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-11-19 22:10:56 +0200

Hello @migueldealmeida

Try insert formula =IF(I3<>"Yes";"Not taking class A";IFERROR(SUMPRODUCT(VLOOKUP(K3;IF(($B$3:$B$9=J3)*($A$3:$A$9="A");$C$3:$D$9);2;1));0)) in M3 cell. Please note, that Dosage values for each class/drug must be sorted ascending, otherwise you will get unexpected results. Also please note, that formula matches only A class rows and it is hardcoded in $A$3:$A$9="A" part.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-11-19 21:01:50 +0200

Seen: 40 times

Last updated: Nov 19 '19