I have a table describing piecewise functions, with the gradients, intercepts, and the bounds detailed. I am trying to obtain an exact value for an arbitrary grade (which has decimals, if that adds to anything).
The problem here is that the bounds don't have the same sizes and so I can't describe the boolean range like: 0 < {upper bound} - {value} <= {size}. If I want to describe it like that, I will have to go for: {lower bound} < {value} <= {upper bound}, which in and of itself is okay, except that the formula is monstrously long and painful to look at if I were to use IFS with this very table because of how many conditions I have
An example here
I feel like this is very inefficient. How do I go around this issue?
- Tried using "MEDIAN" but that only works if the value is exactly in the middle of the bounds. This is not the case.
- Tried using "ROUNDUP", "ROUNDOWN", "MROUND", but as I mentioned earlier, the bounds don't have a constant size, so the formula doesn't work all the way down.
This is a good case for INDEX and MATCH, or VLOOKUP; since the 'bounds' columns are ordered, match will return the correct lower- or upper- bound row, and you can then use INDEX to get the correct gradient and intercept from the same row; VLOOKUP works similarly, though the bounds would have to be ascending instead of descending.
Suppose the score you are checking (say 89.5) is in cell A1, and the data in your table is in B1:E10 then:
=MATCH(A1,B1:B10,1)in A2 will give you the the row number of the largest value in B1:B10 that is less than 89.5 (in this case, it returns '2' corresponding to lower bound 89).Now in A3 you can use
=INDEX(C1:C10,A2)to get the corresponding gradient, and similarly=INDEX(D1:D10,A2)to get the intercept.You can also combine them.
=INDEX(gradient_range,MATCH(grade,lower-bound-range,1))would return the gradient for a given target grade.