How to evaluate a piecewise function with Excel?

On May 06 2011 20:25 Betalump wrote:
I fully understand that Excel is not the best program to do this type of work. However it is the medium I have available and I would like to use Excel so that I can tie it into a bunch of work that I've already done with it.

The goal is to get Excel to successfully plot a curve of a piecewise function that looks something like this.

If X=0 y=240
If X=1-2 y=240+45(X-0)
If X=3-10 y=240+90+39(X-2)
If X=11-14 y=240+90+232+24(X-10)
If X=15-18 y=240+90+232+96+12(X-14)
etc etc

Then I need to be able to integrate piecewise or be able to get it to calculate the total area under the curve up until any value of X.

If possible, I would like to be able to substitute a different series of equations and different limits of X for each equation to quickly generate a different curve.

Thanks!

On May 06 2011 20:41 Aim Here wrote:
This is more of a blog, but anyways, there's two ways to get a cell with the values of your function.

If your function is easy, use nested IFs, as in a formula with
=IF(x=<1,240,IF(x<2,240+45*(x),IF ... )))

Or you could put the bounding values of the piecewise chunks of X in as a column in a table and the Y formulae in the second column, and use vlookup to look them up.

Once you can put the values in a cell, then you can knock up a table with a list of X values, and the formulae values, and then turn it into a graph.

If you need more clarification, you can PM me.