CODEWITHSUNDEEP
excel
scubasteve
scubasteve

Reputation: 2868

How can I plot the points of a curve?

I'm not great with Math at all and so-so with Excel. I have a requirement to calculate a series of values to apply as an offset for a price schedule. I know what I basically want to achieve with the offsets and how I want them to change across span of values but I don't know how I can plot these values or something close to it in Excel. For an idea of the curve I'm roughly trying to create you can plot these values:

0
20
30
35
38
39
39.5
40
40.5
40.75
41
41.125
41.25

Or if you don't want to use excel you can take a look at this chart image: screen shot of chart of sample data above

Now it was easy to quickly create that by entering some values and tweaking them to get a shape I roughly want, but I need 1000 data points! In other words, instead of the 13 point I provided above I would need 1000. This would be crazy to try and do manually.

I can think of two possible options:

  1. Create the range of values with fewer points, then somehow extrapolate that data set into more points. Maybe extrapolate isn't correct but essentially convert 10 points of data into 1000
  2. Interactively draw a curve and then have it plotted to values within a defined min/max range

I've tried googling for help but I'm just coming up with tons and tons of "how to plot your data" sites - I need to work from the OTHER direction ;)

Thanks for any help or tips.

Upvotes: 1

Views: 2996

Answers (3)

eremzeit
eremzeit

Reputation: 4566

Like you said, you could draw a curve and plot those points, then put them into some equation solver to get an equation that could describe the curve you're looking for.

But just from looking at your curve above, you might be able to use an equation of the following form.

y = A - (B / x)

To make a curve similar to yours above, you might try...

y = 40 - (5 / x))

You can use a site like https://functionplotter.com/ to plot the function and play with the values of A and B until you have curve that you are satisfied with.

Upvotes: 1

brettdj
brettdj

Reputation: 55702

A simple and very accurate way is to plot the curve as an x-y scatter plot, and then fit a polynomial via Insert Trendline.

In this case a 4th order polynomial provides close to a perfect fit (R2 of .9973)

Y = -0.0139x4 + 0.4819x3 - 6.0607x2 + 33.026x - 26.718

You can also solve this without charts using LINEST, see Walkenbach's guide here. But charts provide the easiest reference

enter image description here

Upvotes: 0

yosukesabai
yosukesabai

Reputation: 6244

Is this kind of thing you are looking for?

basically you already got 13 y's and 13 implict x's (1 to 13). You provide bunch of "x" values of small increment, like 0.01, 0.02, 0.03, ...,12.999 13.000, to this tool. then this tool will calculate y value for each of the ones. You will plot these as "x y scatter plot"

Upvotes: 0

Related Questions