Excel Tip – Solver Add On

You find yourself working with columns and columns of data for a client. In this particular scenario, you need to find a combination of transactions that total a certain amount. As you can imagine, this can be endless amounts of combinations to iterate through. There is a data trick using an Excel add-in called Solver. Here’s how it works...

1. In Excel, go to the file tab and click Options
2. Under Add-Ins, select Solver Add-In and click Go
   
   
3. A window will pop up, check Solver Add-In and click Ok.
   
   
   
   
4. Now you can see the Solver function on your toolbar (you may need to scroll all the way to the right)
   
   

• • • Situation:
      • • List of transactions and amounts
        • Need to identify transactions that will total $505
    • Here are the steps:
      1. 1. In Excel, have one column for amount and add 2 columns: Multiplier and Product.
            • • Multiplier will start with 1 in all the cells
              • Product is the product of Amount and Multiplier
         2. Add 3 cells
            • • Target = this is the target value, $505
              • Sum = this is the sum of the Product Column, $4,550 (Note: make sure this is a Sum formula and not hardcoded)
              • Difference = this is the difference between Target – Sum = - $4,045
                
                
                
                
         3. Now let’s use the Solver: Click on the Solver Add-In
            
            
            
            
         4. Set the Objective: Difference
         5. Set To: Value of 0
         6. Set By Changing Variable Cells: select your Multiplier column
         7. Subject to the Constraints: add 3 constraints:
            • • Select the cells in the Multiplier column <=1
              • Select the cells in the Multiplier column = Integer
              • Select the cells in the Multiplier column >=0
         8. Select a Solving Method = Simplex LP
         9. Click Solve then click Ok
            
            
            
            
            
         10. Here is the result
             1. • The Solver iterated through many combinations and then kept 1 in the transactions that totaled to the Target amount.
                  
                  

Let us know if this helped you with your data needs. Feel free to reach out to Concentrus for any data analysis or migration needs.

About Us

Concentrus is a complete NetSuite solutions provider that guides organizations through how to use NetSuite to reach highly focused business goals and objectives. We provide NetSuite implementation, developer, integration, and customization services to ensure that you have a long-term solution that is tailored to fit your systems, people, and processes.

Read About Our Implementation Methodology

Want more NetSuite Tips and Tricks? Check out our Short & 'Suite videos