The other day someone asked me if there was an easy way to figure out how many of each of a set of things to purchase, to have the least amount of money left over. In this particular case, there was a choice between asking for per diem funds, or asking for overnight stay funds for conferences. I said sure! Based on the timestamps in the chat convo, it took me exactly 3 minutes and 5 seconds to have an answer ready. (It then took me 45 minutes to write this post about how to do it!)
Here’s an example of how to do it when you only have two possible things to spend money on. It only uses basic math, but having a spreadsheet speeds up checking all the possible combinations. Feel free to change the names or skip them entirely if you’re comfortable doing so – I didn’t use them in figuring my quickie answer. I just included them here because it makes it a lot easier to explain.
1) Name a cell TotalAvail and enter the value of the total available funds in it.
2) Name a cell Cost1 and enter the value of the more expensive item in it.
3) Name a cell Cost2 and enter the value of the less expensive item in it.
4) Name and label a column Item1.
5) In the first cell below the Item1 label, enter the formula: =rounddown(TotalAvail/Cost1,0).
That means divide the total available funds by the cost of item 1, and round the result down to a whole number (i.e. zero decimal places).
6) Set the cell below that to the first cell minus one. That is to say: if Item1 is column A, and the label is in A1, then the formula from step 5 would be in A2. So A3 would have the formula =A2-1.
7) Copy that cell, and paste it down the column until the result is 0.
8) Copy that column, and paste it over itself as values.
9) Name and label a column Item2.
10) In the first cell below the Item2 label, enter the formula: =rounddown((TotalAvail-Item1*Cost1)/Cost2,0).
That means subtracts the money spent on item 1 from the total available funds, then divide the remaining amount by the cost of item 2, and then round the result down to a whole number.
11) Copy that formula, and paste it down the column until you reach the row with the 0 in Item1.
12) Name and label a column Spent.
13) In the first cell below the Spent label, enter the formula: =Item1*Cost1+Item2*Cost 2.
14) Copy that formula and paste it down the rest of the rows.
15) Name and label a column Remaining.
16) In the first cell below the Remaining label, enter the formula: =TotalAvail-Spent
17) Copy that formula and paste it down the rest of the rows.
18) Sort by Remaining.
The lowest number in Remaining (either the top of bottom, depending on how you sorted) has the least amount of money left over.
Before you sort, your spreadsheet might look something like this: