If you use excel, then we have all been in the same position. Imagine you have a large number of entries in a spreadsheet and you need to determine which ones add up to a particular amount? How do you do this? Well, you are in luck. We have a simple trick that will help in identifying excel entries that add up to a specific value.
Personally, this situation arises often. I frequently analyze spreadsheets full of data in the form of account transactions. I used to spend way too much time manually adding numbers together trying to find my predetermined total. After years of doing things the manual way, I learned a simple trick that utilizes Excel’s built in capabilities. A task that would take so long using trial and error now takes minutes thanks to the Solver Add-in.
For more helpful excel hints, you might want to check out this pamphlet. It lists out all of the important tips and tricks you need to get the most out of Excel.
How to identify excel entries that add up to a specific value:
If you are looking to figure out how to use excel to help in identifying excel entries that add up to a specific number, you need to do one thing first. The first step in this process is to make sure Excel’s Solver Add-in is enabled.
To determine this, go to the Data Tab of your spreadsheet and look for the Analysis section. You should see ‘Solver’ all the way to the right. If you do not see it, please enable it. If you are unsure of how to enable solver, please visit this site. Luckily, once you load this add-in you should never have to do it again.
Data Format is Important
When identifying excel entries that add up to a certain number, make sure you data is in a particular format. You will need to list all of the values of the individual entries in a column of the worksheet. To make things easy, name this column Data.
In this example I will be using column B. In the adjacent column, column C, enter the number ‘1’ next to all of your values. Name this column Multiplier.
Lastly, in a third column, enter a formula that multiplies the first two columns. In this example, you would enter =B2*C2. The spread sheet should look something like this (click photo to enlarge):
Once you have entered your data and added the two additional columns, the next step is to create an input cell for the value you are seeking. You will also need to include a cell which will sum all of products. Lastly, add a cell that will be the difference between the target number and the sum of the products. See below for clarification.
Last Steps:
Finally, it is time actually start identifying excel entries that add up to your target number. The way this works is that the Solver either keeps the multiplier 1 or changes it to a 0. This then changes the product which affects the Sum of Products and Difference totals calculated in the previous step. The solver keeps testing combinations until the difference between the Target and Sum of Products is 0.
So, configure the solver to meet the following constraints:
- Set Objective: enter the cell that has the difference amount in it (in this example it is G4) and remember to add the absolute reference symbols.
- To: click on the ‘Value of:’ button and enter in ‘0’
- By Changing Variable Cells: the variable cells are the Multiplier column of the worksheet. Enter these cells and again, remember to add the absolute reference symbols. In this example, it would be cells C2:C31.
- Subject to the Constraints: one constraint needs to be added that will make sure the multiple that is changed in the multiple column is either 1 or 0. Click on the add button, select the values in cell C, and change the first drop down menu to bin. Click ok.
- Select a Solving Method: change the solving method to Simple LP in the drop down.
The solver should look like this (click photo to enlarge):
Click solve!
Now it’s time to hit Solve and start identifying excel entries that add up to your target. The solver will find the combination of numbers that add up to the target value specified in cell F4. However, there are two limitations to the solver: the solver limits the user to 200 variables as well as 100 constraints. After running this, the multiplier column is updated (click photo to enlarge):
The way the solver works is that it adjust the value in the multiplier column. It tests all of the different combinations until the difference between your target and the sum product is 0.
More on Solver
The solver tool is very flexible and can be used for a variety of tasks. These tasks can be as simple as identifying excel entries that add up to a target value. The more complex ones include data models, Monte Carlo simulation, and more. If you are used VLOOKUP and HLOOKUP formulas, the solver will be able to take things to the next level.
Summary
Hopefully knowing how to do this will save you some time and replace the manual process of trial and error. Again, if you found this article helpful, check out this pamphlet containing everything you need to know about Excel.
Also, for more excel tips and tricks check out our professional development section.