How to evaluate a piecewise function with Excel?

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!

Last edit: 2011-05-06 20:26:54

Caller Poland. May 06 2011 20:32. Posts 7267

Profile Blog #

if there's no pattern in your x's you're going to have to manually input it in brah

Watch me fail at Paradox: http://www.teamliquid.net/forum/viewmessage.php?topic_id=397564

Aim Here Scotland. May 06 2011 20:41. Posts 651

Profile Blog #

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.

Last edit: 2011-05-06 20:42:14

Starfox Austria. May 06 2011 21:17. Posts 699

Profile #

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!

Do something like
https://spreadsheets.google.com/ccc?key=0AsHwOnuKF-uIdHJqd1RGOHB6LWlFZXYzc2c2UXJJVmc&hl=en&authkey=CL6hiJgI
Then you would go about and do a SCATTER plot across the X and Y cells, there should be an option to connect the dots of the scatter plot. You can then change the X values and the how it increases at each part.

Greek Mythology 2.0: Imagine Sisyphos as a man who wants to watch all videos on youtube... and Tityos as one who HAS to watch all of them.

Glacierz United States. May 07 2011 00:59. Posts 467

Profile Blog #

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.

Are you proficient in VBA? If so try to use the switch function on x, otherwise use the above post's nested IF statements.

Please log in or register to reply.