• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Sumproduct -

J

Jason Walsh

New Member
Hi,
I am trying to create a formula that will dynamically sum until a "pot's" capacity is reached then move to the next pot.
In table 1 ("Prices") I have Seat Price Across the Top (Low, Low/Mid, Mid, Mid/High, High) and underneath the respective prices (£30, £45, £60, £75, £90)
In table 2 ("Demand") I have demand across the top (Low, Low/Mid, Mid, Mid/High, High) and underneath the limits (30%, 20%, 35%, 10%, 5%)
In cell D10 I have the capacity (for ease lets use 100)
In cell D11 I have actual demand
In cell D12 I have the output.
So, for example, if I input into cell D11 the actual demand of 82. I want the formula to sum the first 30% of people to pay £30, then 20% people to pay £45, then 32% (as it reaches 82) of people to pay £60. Then the total is displayed in cell D12.
Apologies, I don't seem to be able to add a picture to show the tables but hope this makes sense. Any help much appreciated.
Many thanks
Jay Query.png
 
Hi,

In your situation, i see something missing..

you say "then 32% (as it reaches 82) of people to pay £60", how is it possible? as you are expressing in %s, even 82 folks, will have 5% paying high 90?

pls clarify,

Regards,
Prasad DN
 
Hi Prasad,
Many thanks for the reply.
Apologies, my explanation was maybe rushed. In the example, the first 30% of seated capacity will be charged at the "Low" rate of £30 (for ease I have said that capacity is 100 therefore seats 1 - 30 will cost £30). Once this "quota" is full, the next set of seats (seats 31 - 50) will cost £45. Then this "quota" is full, so seats 51 to 82 will cost £60.
I want to be able to run scenarios by inputting vaious numbers in cells D10 & D11 (and the rates / thresholds in teh tables) therefore the formula in D12 needs be agile enough to take this into account.
Many thanks again
Jason
 
hi,

I hope you are fine with using helper rows. I have used two helpers rows and able to get your the results.

check and confirm if this is good.

Regards,
Prasad DN
 

Attachments

  • test1.xlsx
    8.9 KB · Views: 10
Hi Jason,

See the attached file, I had used cumulative sum on row 9 (orange cells) as helper. Final formula is in Yellow cell.

Just check for all possible scenario.

Regards,
 

Attachments

  • dynamicsumproduct.xlsx
    9.3 KB · Views: 11
Hi Prasad, Somendra,
Many thanks for these returns. These both work really well and solve the problem. I really appreciate your help in solving this problem and coming with these answers.
Thanks again
Jason
 
You must log in or register to reply here.
Back
Top