Ask Your Question

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

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

migueldealmeida gravatar image

updated 2020-07-28 20:49:53 +0100

Alex Kemp gravatar image

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 reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-07-28 20:50:06.701703

1 Answer

Sort by » oldest newest most voted

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

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

Question Tools

1 follower


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

Seen: 57 times

Last updated: Nov 19 '19