We have 24 values that represent each hour of the day. Some of the hours share one value; the rest share a second.

Represented here in this graph:

enter image description here

We are trying to flatten the hourly value so that each hour is the same.

The problem: there is a specific constraint: The total subtracted from the peak hours must equal the total added * 0.8.

In other words; The total added on the trough hours should be 1.125 times what we took from the peak.

Example in this:

enter image description here

How do we calculate the value to subtract from each peak hour in order to flatten the chart while considering our rule?

P.S: Please advise on tags. I don't know what tag this applies to.

FYI: I tried searching for answers but I have no idea what I should be searching.

EDIT: To clarify what I'm trying to do: For an internal application, I need to make a set of 24 different values exactly the same value, by taking off from a high value and adding what I take off to a low value. Until all values are the same. With 1 single rule: Any value I take from a Peak value (high value), I must multiply that value by a factor, before adding it to the Trough (a low value).


For the sake of simplicity, instead of 24 values, let's assume I only had the following four values: 4, 3, 2 and 1. And our Factor is 1.5 (instead of 1.25, again, for simplicity). I.e. for each value I take off Peak, I multiply by 1.5 before adding to a Trough.

If I wanted all those values to be the same, I would do the following:
1- Take 1 off the (4).
2- Considering our rule, I'd multiply that 1 by (1.5), so I have 1.5 to spend.
3- Then I divide that value between the Troughs.

So now, I would have the following values:
First: 4 - 1 = 3 (Peak)
Second: 3 (unchanged) (Peak)
Third: 2 + (0.25 of the 1.5 I took earlier) = 2.25 (Trough)
Fourth: 1 + (1.25 of the 1.5 I took earlier) = 2.25 (Trough)

The values are still not the same, so I need to repeat again.
Thinking logically (without a formula), I will need to take off 0.3 off each peak, and multiply that by 1.5, add it to troughs and they will be equal:

First: 3 - 0.3 = 2.7 (Peak)
Second: 3 - 0.03 = 2.7 (Peak)
Third: 2.25 + (0.3 * 1.5) = 2.7 (Trough)
Fourth: 2.25 + (0.3 * 1.5) = 2.7 (Trough)

This makes all values the same: 2.7, while keeping to the constraint.

What I'm having a problem with, is finding a formula to calculate the last bit. That is, how much I need to take off a peak, and multiply by the factor, add to a trough, so all peaks and all troughs are equal. I'm looking for a formula to calculate the (0.3) in the last example

What I tried

I tried coming up with an equation, but the equation gives inconsistent results when the number of peak hours and trough hours are not the same (e.g. instead of 2 and 2 like the above example, we had 1 peak and 3 troughs)

The formula I came up with is the following:
((hp - hx) / h) = (xri + tr) / r

h: being how many Peak Hours there are.
p: being the value of a peak hour (we only take one because all of them should be the same)
x: being the missing variable that we're looking for (e.g. 0.3 in the previous example)
r: being how many Trough Hours there are.
t: being the value of a Trough (again, assuming they're all the same because we've already flattened the previous hours)
i: being our factor (e.g. 1.5 in the previous example)

My rationale: The average (1/h) value of all hours (hp) after subtracting an x from each one (- hx), should be equal to the average of (1/r) -> [how much we took off, multiplied by the factor and by the number of Trough hours (xri)], and added to the total of Trough hours (tr).


I have realized that that ratio between Peak reduction and Trought addition is = Peak Count / Trough Count * Factor

For example: If the 11 peak hours are 0.3272 higher than the 13 trough

13/11 = 1.181818181
1.181818181 * 0.8 = 0.9454545454

I used Excel to Goal-Seek the answer and this is correct.

Which leaves me at the following point:

If X = Y * 0.9454545454
And X + Y = 0.3272

What is the best way to calculate X and Y?


If I understand correctly, you can start out at the very beginning with up to $24$ different values, each value assigned to one or more of the $24$ hours of the day. That is, initially the values $v_1, v_2, v_3, \ldots, v_{24}$ (not necessarily all different) are assigned to the $24$ hours, respectively. The final state you want to achieve is that every hour has the same value $v_f$ assigned to it, and the sum of all the amounts you have to add to the (initially) lesser values in order to bring them up to $v_f$ is $i$ times the sum of all amounts you have to subtract from the (initially) greater values in order to bring them down to $v_f$. (In your example, $i = 1.5$.)

Expressed as a summation, this says that

$$\sum_{k\ :\ v_k\ <\ v_f} (v_f - v_k) = i \sum_{k\ :\ v_k\ >\ v_f} (v_k - v_f)$$

where the notation $k : v_k < v_f$ (for example) under the summation symbol means that we take the sum for just the index values $k$ such that $v_k < v_f$.

The algorithm you have described is an iterative algorithm that takes something off the top of the "peak" hours (the ones whose value is the largest) and redistributing $i$ times this amount to the "trough" hours (hours whose values are lower than the others; exactly how many hours depends on how much value you have to "redistribute" from the peak hours). Your notion is that you reduce each peak hour enough each time to bring it down to the next highest value, except at the last step where you assume you will have only two remaining distinct values and you have to reduce the peak hours by less than the difference.

I am not sure this is guaranteed to work quite like that; I think it is quite possible that you may end up having to reduce the peak hours by a fractional amount when you still have three or more distinct values. For example, suppose initially you have three hours each with value $15$, one hour with value $10$, and the other twenty hours all with value $9$. There are therefore three "peak" hours, and you cannot reduce them all to the next lower value (which is $10$) because you would then be reducing the total "peak" value by $3 \times 5 = 15$ hours, you would therefore need to add $1.5 \times 15 = 22.5$ hours to the "trough", but the size of the trough is only $20$ hours.

I propose that there is a (somewhat) more direct algorithm for finding $v_f$ that is guaranteed to work without this kind of hiccup.

First, search the initial values $v_1, \ldots, v_{24}$ until you find two values $v_L$ and $v_H$ such that:

  1. $v_L < v_H$.

  2. There are no other initial values between $v_L$ and $v_H$.

  3. $\displaystyle\sum_{k\ :\ v_k\ <\ v_L} (v_L - v_k) \leq i \displaystyle\sum_{k\ :\ v_k\ >\ v_L} (v_k - v_L).$

  4. $\displaystyle\sum_{k\ :\ v_k\ <\ v_H} (v_H - v_k) > i \displaystyle\sum_{k\ :\ v_k\ >\ v_H} (v_k - v_H).$

The last two conditions say that if you made a guess that $v_f = v_L$, you would find that the total "extra" value in the hours with greater value was at least $i$ times the total "missing" value in the other hours, possibly more; but if you guessed that $v_f = v_H$ you would find that the "extra" value is less than $i$ times the "missing" value. The true answer is therefore some intermediate value, that is, $v_L \leq v_f < v_H$.

One way to find $v_L$ and $v_H$ is to arrange the values in increasing order and perform either a linear or binary "search" on them. In fact, a simpler way to specify $v_L$ is to say that it is the greatest $v$ among the set $\{v_1, \ldots, v_{24}\}$ such that

$$\sum_{k\ :\ v_k\ <\ v} (v - v_k) \leq i \sum_{k\ :\ v_k\ >\ v} (v_k - v).$$

Once you find the correct value to use for $v_L$, let $A$ be the total amount of "excess" value above $v_L$ and $B$ be the total amount of "missing" value below $v_L$, that is,

\begin{align} A &= \sum_{k\ :\ v_k\ >\ v_L} (v_k - v_L), \\ B &= \sum_{k\ :\ v_k\ <\ v_L} (v_L - v_k). \end{align}

These are values you should already have computed in order to check the condition(s) above while verifying that this is the correct value $v_L$. Because of that, you also know that $iA \geq B$.

Let $h$ be the number of hours whose values are greater than $v_L$. For convenience, define the unknown value $y = v_f - v_L$, where $v_f$ is the (still unknown) desired final value of all hours.

Therefore the actual amount of "excess" value above $v_f$ is $A - hy$, while the amount of "missing" value below $v_f$ is $B + (24 - h)y$. That is, the following equation must be true:

$$ B + (24 - h)y = i(A - hy).$$

At this point $y$ will be the only unknown in that equation. A little algebra gives the following solution for $y$:

$$ y = \frac{iA - B}{24 + (i - 1)h}.$$

And then of course $v_f = v_L + y.$

It is possible that your customer may not trust these calculations. That's OK. You should be able to use this algorithm to "guess" $v_f$, and then run a second, much simpler algorithm to check that indeed the total amount you had to add to the lower values to bring them all up to $v_f$ was $i$ times the total amount by which you had to reduce the higher values.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.