• 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

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