Budget / historical data / prorate / conditions

Hi guys,

I have the following problem, trying to obtain the new quantities for the historical data skeleton, achieving the 6 respective conditions.

Let me explained a little bit better, I can prorate the current historical data, and then apply its % to condition 1, then some rounding and I will achieve condition 1 (example: sum of p23 = 586) , but it wouldnt achieve conditions 2-6 (example: sum of venue d/C11 = 435). This because conditions 2-6 are not based on the historical data %. Historical data is mainly to generate a brief idea of budget behavior.

Also, I could start prorating by conditions 2-6 , and then some rounding, and I will achieve it, but I cannot comply with condition 1. (Example: sum of venue a/C1 = 9086 ) but (sum of p24= 321).

The deal here, is to comply with the 6 conditions at the same time. (Product Qty, Venue, Client).

Sum of Condition 1= 166035

Sum of Condition 2-6 = 166035

Got any ideas?

Thanks.

Sorry, I'm lost on this. I really can't follow what you're trying to do here, nor can I even figure out what cells you're trying to populate...

I think we're going to need a much clearer walk through than this.

Hi Ken,

Im trying to populate cells D3 to D8426. Its current values can be used as historical data in order to determine weight % values to prorate against Condition 1 to Condition 6.

Cells A3:C8426 is the budget skeleton I want to keep.

For instance, if you =SUMIF(A:A,A3,D), you are going to obtain 1750; then if you divide 34/1750=0.019429. Then you multiply this times 2976 (p1 value condition 1) , you are going to obtain that for P1, Venue A, Client C1, its new prorated value is 57.81 Doing this for the whole skeleton, you will be able to achieve condition 1 (Products Quantities) , but this wont match condition 2 to 6.

If you do the opposite, SUMIFS(D,B:B,B3,C:C,C3), you can prorate in the same way but using conditions 2-6, then you will achieve these, but it wont match Condition 1.

Im trying to obtain the new quantities values for the budget skeleton complying with the 6 conditions. All conditions are related and must be achieved.

And if wasnt enough, quantities must be rounded.

Let me know if you need any other explanation.

Thanks Ken.