The scenario is that I want to tip a person x amount of tokens. I want to figure out the cost to get those tokens based on the data in the table in the attached workbook.



Let's create a scenario. Person A I want to tip 1200 tokens to. What would be the least dollar amount to purchase those tokens?

I also have to take into consideration the budget at the time of the purchase. I also need to look at do I want to have left over tokens for a later tip. Also the least amount of purchases is preferable

Say I have $120 to get the 1200 tokens. How can I have Excel figure out the different purchase options for me?

So manually doing it. I can do 2x550 for $99.98 and then 2x50 for $10.98 thus giving me exactly 1200 tokens for the $120 budget. Now 6x200 for 119.94 is more then doing the other purchase option.

Is this even within the scope of using Excel.

I am not very proficient with Excel or even that advanced with math. So if someone can steer me in the correct direction to figure out this problem I would appreciate that.

Attached Files

Tokens.xlsx (8.3 KB, 8 views)

Given that you haven't even decided whether you want to keep unused tokens for a future trip, this isn't something Excel can solve. Even if you don't, it may sometimes be cheaper to buy more tokens than the exact number you need. There may also be more than one solution.

Your example of 200 tokens @ $19.99 each = $119.94 is certainly not the best you can do in that instance. You could even buy 1300 tokens for less (900 @ $74.99 + 200*2 @ $19.99 = $114.97) and, if you wanted 100 tokens for something else, that might be the way to go. Alternatively, there are at least two ways of getting exactly 1200 tokens for $105.96:
900 @ $74.99 + 200 @ $19.99 + 90 @ $9.49 + 10 @ $1.49
900 @ $74.99 + 200 @ $19.99 + 50*2 @ $5.49

PS: Instead of posting links to workbooks you've loaded elsewhere, you can attach files here, so people don't have to go looking elsewhere. That gives you a better chance of getting an answer. I've replaced your link with a workbook containing the same data.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Unused tokens would be kept for future tips as it would reduce the outlay for the next tip.

So based on what I am reading here this is out of the scope of Excel?

There is no way to tell Excel to figure out different combinations of token purchase to add up to the amount I want to tip from that list.

Sorry about the link. I didn't think it would be an issue. I will keep that in mind if I have to link another sheet.

Well, if you're quite happy to buy tokens for future trips, you'd simply buy as many as you need of the cheapest tokens that will give you at least as many as you need. In this case, depending on how many future trips you might plan, and how far into the future they might be, you might buy:
550 @ $49.99 + 900 @ $74.99 = 1450 @ $124.98 (11.60 tokens/$)
1875 @ $149.99 (12.50 tokens/$)
3775 @ $299.99 (12.58 tokens/$)

This really isn't a problem for Excel; it's a decision-making one, for which a judgment call is required. Part of that decision-making should probably include an assessment as of the opportunity cost of buying token now & locking up you money in them, vs investing the money in something else. Of course, while you're investing, the cost of tokens might go up, too...

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]