asking for solution for using VBA to test all the possibilities

dylan32123

New Member
Joined
Dec 7, 2016
Messages
6
Thank you so much for reading,

here is what troubles me, i want to test the different price for 15 days, the price (from 40-100) could be different everyday, with each price and different days the demand varies, hence the profit would be different, need pro to show me how to crate loop for this situation.

a simpler case would be like this, the problem is how to achieve all the possibilities.
111 112 113
121 122 123
131 132 133
211 212 213
221 222 223
231 232 233
311 312 313
321 322 323
331 332 333
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Rhodie72

Well-known Member
Joined
Apr 18, 2016
Messages
546
Thank you so much for reading,

here is what troubles me, i want to test the different price for 15 days, the price (from 40-100) could be different everyday, with each price and different days the demand varies, hence the profit would be different, need pro to show me how to crate loop for this situation.

a simpler case would be like this, the problem is how to achieve all the possibilities.
111 112 113
121 122 123
131 132 133
211 212 213
221 222 223
231 232 233
311 312 313
321 322 323
331 332 333


Not exactly sure what you're asking for.
i want to test the different price for 15 days
Price of what?
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,633
There are many ways to generate combinations and permutations. You can search this forum for those keywords. However, in your case, I really doubt that it would be useful. If your price can vary from 40-100 on 15 days, you'd end up with 61^15 combinations, or around 600000000000000000000000000. There's no way you could examine all of them. You could impose some rules on it, like only allowing the price to change by +/- 1 each day, but that still generates about 300 million combinations. If you could impose some reasonable constraints, you might be able to get it to a reasonable number.

However, what would make more sense would be to create a model. A series of equations that map how you think the market works. Then by varying the parameters, you could figure out trends without having to look at every possibility. However, creating such a model is well beyond the scope of a basic question here.

You might also look at the Solver. If you can create a model, then the Solver might be used to figure out how to maximize profit.

Hopefully, this gives you some ideas.
 

dylan32123

New Member
Joined
Dec 7, 2016
Messages
6
thx a lot for answering,

the goods sells in a 15 days of period, like a football ticket, and ticket varies every day from 40 to 100. would this be clearer?

thx again for the help
 

dylan32123

New Member
Joined
Dec 7, 2016
Messages
6

ADVERTISEMENT

There are many ways to generate combinations and permutations. You can search this forum for those keywords. However, in your case, I really doubt that it would be useful. If your price can vary from 40-100 on 15 days, you'd end up with 61^15 combinations, or around 600000000000000000000000000. There's no way you could examine all of them. You could impose some rules on it, like only allowing the price to change by +/- 1 each day, but that still generates about 300 million combinations. If you could impose some reasonable constraints, you might be able to get it to a reasonable number.

However, what would make more sense would be to create a model. A series of equations that map how you think the market works. Then by varying the parameters, you could figure out trends without having to look at every possibility. However, creating such a model is well beyond the scope of a basic question here.

You might also look at the Solver. If you can create a model, then the Solver might be used to figure out how to maximize profit.

Hopefully, this gives you some ideas.

thanks a lot mate!!!

your answer is exactly what i need! and its really helpful! It is too many cases to test indeed! we can think of this model as a ticket show, as the price will goes higher when it come closer to the performance, and make the price goes by step 10. Appreciate your time and consideration, but could you please advice more?

thx again
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,633
Given those constraints, the number of combinations is more reasonable. Assuming that the starting price can be 40-100 in a multiple of 10, and each day the price can either stay the same or go up by 10, and maxes out at 100, then we get a count of 12012 combinations.

I can write a macro to enumerate all of those, but it's still fairly big for a human to scan through. Given that list, what are you going to do with it? What rules determine whether to raise the price? How do you determine the profit?
 

dylan32123

New Member
Joined
Dec 7, 2016
Messages
6

ADVERTISEMENT

Given those constraints, the number of combinations is more reasonable. Assuming that the starting price can be 40-100 in a multiple of 10, and each day the price can either stay the same or go up by 10, and maxes out at 100, then we get a count of 12012 combinations.

I can write a macro to enumerate all of those, but it's still fairly big for a human to scan through. Given that list, what are you going to do with it? What rules determine whether to raise the price? How do you determine the profit?

really appreciate your help! the goal is to find out the max profit out of these combinations, and also return the best price strategy. when inputting the price there would be formula to calculate the demand, hence get the profit (lets say in the cell G10), i am think if the price for day 10 (in cell B1) starts with 40, and for the price in day 9 (in cell B2) we can try to input the price as 40 and also these the value for 50 and 60 and 70, get the highest revenue from cell G2, and return the value. then goes to next day. (i am so sorry, will this be too complicated? i would love to here your suggestion!) this may not necessarily be accurate enough, but just as an advice for the user to have a genera idea.

thx SO much!! U are absolutely a legend!!!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,633
The good news is that actually sounds like a reasonable project. The bad news is that it sounds like a fair amount of work, and I don't have that much time to devote to this question. One thing I can say is that if your ticket prices continually go up, and if your demand function also continually goes up, any model will tell you to hold the tickets until the last day before selling to maximize the profit. You'll also have to add some way of considering the possibility of not selling some tickets.

This is basically a linear programming problem. For example, I buy tickets at $40. each. I can sell 100 at $50 each (high availability), netting $1000. Or I could sell 10 at $200 each (low availability) and net $1600. This is exactly the type of thing that the Solver was designed for. Google "Excel Solver" to get some good links on how to use it.

Good luck!
 

dylan32123

New Member
Joined
Dec 7, 2016
Messages
6
The good news is that actually sounds like a reasonable project. The bad news is that it sounds like a fair amount of work, and I don't have that much time to devote to this question. One thing I can say is that if your ticket prices continually go up, and if your demand function also continually goes up, any model will tell you to hold the tickets until the last day before selling to maximize the profit. You'll also have to add some way of considering the possibility of not selling some tickets.

This is basically a linear programming problem. For example, I buy tickets at $40. each. I can sell 100 at $50 each (high availability), netting $1000. Or I could sell 10 at $200 each (low availability) and net $1600. This is exactly the type of thing that the Solver was designed for. Google "Excel Solver" to get some good links on how to use it.

Good luck!


many thanks for the help! Ill try figure it out! would you mind if i ask you again if I encounter some problems on the way solving that?

best wishes!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,633
You can certainly post another question if you wish. Send me a PM if you don't get an answer. But do as much as you can first. Ask for help with a piece of the puzzle, not the whole puzzle. You'd need to get a consultant for that. Designing the demand function for example is pretty much on you. You know the business you're in, no one here does. But if you set up the Solver, and it tells you to sell 100 tickets when you only have 10, someone here will probably be able to figure out why.

:wink:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top