0

I have data that looks something like this:

enter image description here

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?

7
  • and what kind of research have you done so far? Hint: what do you think the equivalent of LINEST() is?
    – gns100
    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.
    – Hannu
    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

2 Answers 2

1

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
1

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)

enter image description here

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .