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:
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:
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?