Results 1 to 3 of 3

Thread: Budget / historical data / prorate / conditions

  1. #1

    Budget / historical data / prorate / conditions



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •