Excel: Piecewise Functions Without using "IFS"

Question

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.

Just wondering. What if the valueis exactly 90? will the right gradient be 0 or 0.03? Anyways, sounds like a perfect case for SUMIFS — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Feb 14, 2024 at 12:13
since the piecewise functions are continuous, the program wouldn't have a problem processing the boundary values. whichever gradient the system chooses, at the end of the day, that output will still be 4.33 as expected. — lems
– lems, Commented Feb 28, 2024 at 17:57

Halbert · Accepted Answer · 2024-02-14 08:04:16Z

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.

Thank you! With a little bit of tweaking I was able to use this method.

Black cat · Accepted Answer · 2024-02-14 08:58:33Z

Try this formula:

=F40*INDEX(C52:C61,MATCH(F40,A52:A61,1))+INDEX(D52:D61,MATCH(F40,A52:A61,1))

F40 the grade

A52:D61 the new table range. (adjust the formula ranges according to it)

A52:D61 the reversed table this is the formula to reverse

=OFFSET(A$49,-ROW()+52,0)

This formula in cell A52 and copy to right and down for the new table

52 is the row number of the first row.

A$49 is the last cell of the original table in column A.

Result

Collectives™ on Stack Overflow

Excel: Piecewise Functions Without using "IFS"

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related