Formula for a bit of a non-linear curve

I have data that looks something like this:

I need an Excel formula that given the dollars on the left would produce the quantities on the right.

It's not a linear regression. The slight curve it has is important.

I just need to generate dummy data along a curve like this. Nothing fancy, and doesn't have to be exact. Ideally, there would be some kind of log or exponent or inversion that I could put into some other cell to change the curve as needed--but the curving is important.

What is some Excel formula that given the value in column A would produce something close to column B?

• and what kind of research have you done so far? Hint: what do you think the equivalent of LINEST() is? Mar 1 at 23:30
• B = A * (21000000 - A) / 20000000000 Mar 2 at 1:30
• @JaromandaX, may I suggest that you elaborate on that, and add it as an answer. Mar 2 at 6:08
• @Hannu - not really, it's simple maths that gives the results as required Mar 2 at 6:22
• @JaromandaX, that's exactly it. Can you add it as an answer so I can up vote it. Don't underestimate the power of simple math. Everything is simple when you can see it. Mar 3 at 0:47

The Tl;DR - the formula is B = A * (21000000 - A) / 20000000000

How did I get to this.

OK, at first step, it's clear that B is A/1000 less some value

B = A / 1,000 - fn(A)


The value of fn(A) is

A           fn(A)
1000000     0
2000000     100
3000000     300
4000000     600
5000000     1000


Looks like triangle numbers 1,3,6,10,15 etc (multiplied by 100 of course)

Formula for that is n(n+1) / 2 - though, since we want 0 for the first term, n(n-1)/2

For our purpose n = A / 1000000 and want to multiply the result by 100

Therefore

So fn(A) = 100 * ((A / 1000000 * (A - 1000000) / 1000000) / 2 )


or

fn(A) = 100 * A * (A - 1000000) / 2000000000000


which then becomes

fn(A) = A * (A - 1000000) / 20000000000


So, now we have

B = A / 1000 - A * (A - 1000000) / 20000000000


Simplifying using basic methods you end up with

B = A * (21000000 - A) / 20000000000


You need Lagrange Interpolation

I do this in sagemath but you can also calculate it manually.

I first create a list of points which I need to interpolate

sage: pts
[(1000000, 1000),
(2000000, 1900),
(3000000, 2700),
(4000000, 3400),
(5000000, 4000),
(6000000, 4500),
(7000000, 4900),
(8000000, 5200),
(9000000, 5400),
(10000000, 5500)]


Then I do Lagrange Interpolation of those points in Rational numbers

sage: Q.<x> = PolynomialRing(QQ)
sage:
sage: Q.lagrange_polynomial(pts)
-1/20000000000*x^2 + 21/20000*x


I write the above polynomial as an excel formula in B1 Cell =(-1/20000000000)*POWER(A1,2)+(21/20000)*A1

Then I copy the cell to remaining 9 cells (the cell ids will adjust)

Since the resulting polynomial is quadratic, 3 points would suffice but we don't know that by looking at the points.

Just any 3 points would have sufficed for the interpolation.

For e.g.

pts = [(1000000, 1000), (2000000, 1900), (3000000, 2700)]

If you want to try it manually, just try the above 3 points or any 3 points from the list in the formula for Lagrange Interpolation.