C# aggregation function for a non-linear series

Question

I'm given two arrays, one representing a price, the other representing a number of units:

e.g.

decimal[] price = new decimal[] {1.65, 1.6, 1.55, 1.4, 1.3};
long[] quantity = new long[] {5000, 10000, 12000, 20000, 50000};

So the first 5000 units will cost 1.65 each, the next will cost 10000 will cost 1.6 each, and so on...

It's pretty easy to get the average price with an aggregate funct when you know the amount of units you wish to order e.g.Average price for 7000 units = (5000/7000 * 1.65) + (2000/7000 * 1.6), however, I'm having trouble coming up with an algorithm for when the total unit amount is the unknown variable, and we are given the target average price.

E.g. How many units would I have to order so the average unit price = 1.57

Are all of the units unknown, just one, something in between, or what? — Servy, Feb 19 '13 at 19:23
It is set of linear functions between each point in `quantity` array - draw "quantity vs average price" chart on list of paper and you'll see the solution. — Alexei Levenkov, Feb 19 '13 at 19:40
I've calculated what the average price would be at each tranche, and then at the level prior to my desired unit price I increment the quantity (T as below), recalculating the average price until I match/exceed my desired unit price. This is kind of like the geometrical solution that has been mentioned (plotting each point one by one and continually adjusting the curve). Obviously I'm looking for something more efficient. — user250837, Feb 19 '13 at 20:27

Jeppe Stig Nielsen · Accepted Answer · 2013-02-19T21:44:04.620

If you think geometrically about it, consider a chart showing the total price (ordinate axis) as a function of the total number of items (abscissa) bought. The plot starts in (0, 0) (buying zero costs zero). First we get a straight line segment of slope 1.65 and horizontal width 5000. Then from the end-point of that comes a new segment of slope 1.6 and width 10000. The total plot is continuous and piece-wise straight-lined but with bends where the unit price changes.

Then to solve your problem, find the intersection with the line of equation y == 1.57 * x, i.e. the line starting at (0, 0) and having slope 1.57. For each of the segments (whose two endpoints you know), check if this segment meets y == 1.57 * x, and if it does, there's your solution.

If the numbers in your price array are decreasing, there can be at most one solution (given that 1.57 is strictly less than the first price, price[0]), the plot representing a concave function.

EDIT: I tried to code this geometry in C#. I didn't add checks that price are all positive and decreasing, and that quantity are all positive. You must check that. Here's my code:

        decimal[] price = { 1.65m, 1.6m, 1.55m, 1.4m, 1.3m, };
        long[] quantity = { 5000, 10000, 12000, 20000, 50000, };
        decimal desiredAverage = 1.57m;

        int length = price.Length;
        if (length != quantity.Length)
            throw new InvalidOperationException();

        var abscissaValues = new long[length + 1];
        var ordinateValues = new decimal[length + 1];
        for (int i = 1; i <= length; ++i)
        {
            for (int j = 0; j < i; ++j)
            {
                abscissaValues[i] += quantity[j];
                ordinateValues[i] += price[j] * quantity[j];
            }
        } // calculation of plot complete

        int segmentNumber = Enumerable.Range(1, length).FirstOrDefault(i => ordinateValues[i] / abscissaValues[i] <= desiredAverage);
        if (segmentNumber > 1)
        {
            decimal x = (ordinateValues[segmentNumber - 1] * abscissaValues[segmentNumber] - abscissaValues[segmentNumber - 1] * ordinateValues[segmentNumber])
                / (desiredAverage * (abscissaValues[segmentNumber] - abscissaValues[segmentNumber - 1]) - (ordinateValues[segmentNumber] - ordinateValues[segmentNumber - 1]));
            Console.WriteLine("Found solution x == " + x);
        }
        else
        {
            Console.WriteLine("No solution");
        }

I don't know if someone can write it more beautifully, but it seems to work. Output is:

Found solution x == 29705.882352941176470588235294

Hi thanks Jeppe, I understand how I can solve this geometrically, but as far as an algorithm goes that would mean plotting each point iteratively, which is too inefficient for practical use so I was hoping to come up with a solution that could be called upon as a formula. — user250837, Feb 19 '13 at 20:52
@user250837 I outlined a precise algorithm to find the solution (except that I didn't give a formula to find intersection of lines). But now I coded everything. So here's your solution, enjoy (see edit above). — Jeppe Stig Nielsen, Feb 19 '13 at 21:46
Thanks Jeppe! That works super fast. Pardon my ignorance of basic maths... that formula is exactly what I needed. — user250837, Feb 19 '13 at 22:25

Alptigin Jalayr · Answer 2 · 2013-02-19T19:41:45.740

0

I believe that's because there's no one answer, no one combination of prices which will result in one average, no close form equation. What we may be looking at is a variant of the Knapsack Problem. http://en.wikipedia.org/wiki/Knapsack_problem with a minimization of value instead of maximization.

EDIT: As correctly pointed out below, this is not a variant of the knapsack problem. There is a closed form solution:

If T = total units bought,

1.57 = 1.55 * (12000/T) + 1.6 * ((T-12000)/T). Solve for T.

The starting price block (here 1.55) is the block just below the average price per unit given in the problem (here 1.57).

edited Feb 19 '13 at 19:41

answered Feb 19 '13 at 19:22

Alptigin Jalayr

719
4
12

I think you may be misinterpreting his question. In this case, there absolutely is exactly one quantity purchased which will cause the average price to hit 1.57 (or dip below 1.57 if only whole quantities are considered). It's just a matter of whether someone here finds the time to code the algorithm in C# for the OP. :) – jam40jeff Feb 19 '13 at 19:26
I believe that he's asking for an algorithm, not a closed-form equation. Since closed-form equations are technically limited to O(1) algorithms, there are many more algorithms than closed-form equations. – RBarryYoung Feb 19 '13 at 19:26
1

Also, the Knapsack Problem will have multiple solutions. However, this problem (where prices fall as quantity purchased rises) will only have one solution. – jam40jeff Feb 19 '13 at 19:28
Your new formula only applies if there is a single unknown quantity, not if there are multiple unknown quantities. – Servy Feb 19 '13 at 19:44
Hmm, what other unknown is present in the problem? I may be missing something again. – Alptigin Jalayr Feb 19 '13 at 19:47
@AlptiginJalayr What if the quantity of three of the products is unknown, not just one, or what if all of the quantities are unknown? – Servy Feb 19 '13 at 20:21
1

@Servy The way I understand it, there's only one product. If you buy a small amount, you pay `1.65` per item. If you buy more items (of the same product), the price per item gets lower. – Jeppe Stig Nielsen Feb 19 '13 at 20:23
@JeppeStigNielsen Ah, that makes a lot more sense than what I was thinking. – Servy Feb 19 '13 at 20:24
@JeppeStigNielson, yes that's right. Yup I'm looking for an algorithm that can be run on any list of prices/units. I played around with the above formula inside an aggregate function and I came up with a quadratic equation which gave me two answers, both of which were wrong :) – user250837 Feb 19 '13 at 20:32

C# aggregation function for a non-linear series

2 Answers2