Multi-tiered pricing - Excel formula

Did you get it to work? Do you think you can share your spreadsheet so I can see what's inputted into Solver etc.? Many thanks

Did the screenshot not pull through properly?

Here is the workbook.

Upon further inspection, I see that it is improperly choosing 3x50 packs to hit 150 instead of 1x50 pack and 1x100 pack.
That comes to $1,890 which is $20 more.
I suspect solver found a solution and stopped looking for better ones.
Will see if I can tweak further.

It looks like sorting the user pack column (column A) in descending order fixes the issue with 150 needed.

It is now choosing 1x100 and 1x50.

Great!

Bizarrely, it's working for me. But does the user need to always run Solver to get the answer after the user inputs the quantity in E2? The reason I'm asking is because I need to go to Data -> Solver -> Solve in order for F2 to update the price.

Yes, solver is not automated.

You can probably create a macro to run solver when that cell is changed, or there might be someone who can make this work in a formula, but that's where I bow out.

Cool - thanks for this.

Wondering if you know if it's possible to run 2 solvers on a worksheet? I've updated your workbook with 'Logic 2' where I'd like to run the 2nd solver in conjunction to the 1st solver.

The intention for Logic 2 is to price up based on the cheapest combination on a per user basis. For example:

- If 45 is inputted in E2, then C13 will be populated with 45 because 45 x $12.6 = $576. This is cheaper than giving them the 50 user pack priced at $630.
- If 55 is inputted in E2, then C13 will be populated with 5 and C14 will be populated with 1 because (5 x $12.6) + (1 x $630) = $693. This is cheaper than 1 x 100 user pack priced at $1,240, which is what the original solver logic would have done.
- Likewise, if 3,499 is inputted in E2, then 49 x 1 user pack, 1 x 50 user pack, 4 x 100 user pack and 3 x 1000 user pack will be pricier than 1 x 3500 user pack.

Do you think this is possible to do on Solver?

Thanks

I'm not sure I follow. The only difference that I can see between logic 1 and logic 2 is that logic 2 has an option for 1 user pack priced at $12.60.

Can't you just insert a row for that option in the original table and adjust the solver prompts to include that row?

This would return the following based on your examples in post #10:

45 Needed: 45 x 1 user pack
55 Needed: 55 x 1 user pack (this is the same price as 5 x 1 user pack & 1 x 50 user pack ... $693)
3,499 Needed: 1 X 3,500 user pack

Please see attached.

Ah yes. I think I kind of confused myself. Haha

This means both logics work:
- Logic 1: calculates the cheapest combination of user packs from 50->25,000
- Logic 2: calculates the cheapest combination of user packs from 1-> 25,000

Is it possible to get solver to solve both logics at the same time? If so, how can it be implemented?

Thanks

If the only difference between logic 1 and logic 2 is that you have an option for a single user pack in logic 2, can't you just use the one solver solution from post #11?

If it uses the single user pack, then logic 2 is cheaper.
If it doesn't use the single user pack, then logic 1 is cheaper.

You can put this in a formula (using the workbook from post #11) like this:
=IF(C2>0,"Logic 2 cheaper","Logic 1 cheaper")