How can I find a function that accurately matches a x,y scatter plot

Question

I have a complicated piecewise function(see below), that I use in a spreadsheet to determine what a subcontractor get paid on a particular job.

For example if a job is worth $$270 dollars the subcontractor gets paid 60% of that. Or if a job is worth $18 the subcontractor keeps 95% of the value.

I have a few problems:

• The spread sheet gets really slow when there are a ton of these equations in there
• the equation isn't smooth, so a subcontractor who does a job worth $$269.00 makes $174.85, while a subcontractor who does a job worth $291.41 makes $174.85. This doesn't make any sense.

Attempts:

• I tried to come up with a few equations, by graphing them in excel as a scatterplot and using the "add trendline" function. This failed and seemed like the function it came up with never very accurate.
• I also tried to just tinker with a formula for a long time and the best I could come up with was: -7.1257 * ln(x + 0.9999) + 100.6672 . It was pretty accurate at the low and some what accurate at the higher end, but is in accurate in the middle.

Questions

• Do I just need to give up and find some constant based off of statistically the profit I want to make? ( about 20% on average)
• Is there a formula that really accurately fits this piecewise?
-

Answer 1

Try to use a quadratical minimization to a polinomial. For example, Levenberg-Marquardt method fit data to a polinomial of arbitrary degree and give good results (is used generaly with continous functions). Try different degrees. If your domain is well covered more or less in all subzones can give you good results.

Answer 2

I placed points in the middle of your intervals, created a scatter plot, and then used the "Add Trendline" function with degree = 3 or 4 or 5. Using a higher degree gives a closer fit, but the curve starts to get more "wiggly".

The degree 5 curve is

y = -1E-12x5 + 1E-09x4 - 3E-07x3 + 5E-05x2 - 0.0049x + 0.9983

You need to do something special when $x > 269$ because there's no way to get a polynomial curve to flatten out and become a horizontal line.