We many times have a scenario where we need to find positive and negative numbers that net off or offset to zero in a range of cells in Excel. Especially when we are trying to delete the offsetting amounts like in an accounting report where debits and credits that offset each other are to be eliminated or where we match debits and credits to arrive at a net sum of zero.
We will now just look at how this can be done in Excel. The version I am using is Microsoft Office 2016. If you want to see the excel file for this purpose, you can download it from the link given at the end of this article.
Assuming we have listed the numbers in one column ‘B’ as in the below screenshot.
In column ‘A’ enter zero and fill it down till the row where you have amounts in Column 'B'.
Sort the data by Column ‘B’ – Smallest to Largest. It will look like as shown below screenshot
In Cell F1, insert formula
=SUMPRODUCT(A1:A8,B1:B8)
If you see, we are just considering the negatives.
Similarly, in Cell F2, insert formula
=SUMPRODUCT(A9: A17, B9: B17)
This time we are including the positive values.
Next in Cell H1, insert a formula to sum values in Column ‘A’
=SUM(A1:A8)+SUM(A9:A17)
In cell H2, sum the positive and negatives sum-products. We don’t need the sign so we will take the absolute value using ABS function as shown below
=ABS(F1+F2)
Next open the solver menu, if the solver menu is not available in Excel, then you may need to install the add-in, follow the below steps
1. Click on File Menu
2. Click Options
3. Click Add-ins
4. Click Go…
4. Install Solver (check it)
5. Click OK
You can find the detailed steps in the below link
Or watch the video to see how to install the solver
Now in Excel, click "Data" in the Ribbon and then click on Solver.
In the Solver dialog, select the options as shown in the below screenshot.
In Set Objective enter cell address $H$1 To Select option ‘Max’. If you want more accurate Net Off and wiling to allow more time for processing, you can also select option - Value Of: and Type 0 in box next to it.
By Changing Variable Cells, enter cell address $A$1:$A$17 the range in Column ‘A’ which we had filled down earlier. You should change this according to the number of rows to be solved.
Next click on Add
In the Cell Reference field enter ‘$A$1:$A$17’ You should change this according to the number of rows to be solved. In the dropdown select bin and in constraint enter '0' and click Add.
Again, click Add in Solver Parameters dialog.
In the cell reference field Enter Cell Address $H$2.
In the drop-down select '<=' and in the 'constraint' field enter '5' and click Add
The window should now look like the one shown below
Click on Solve.
After a minute you may get to see this message
Click on Continue
Allow the solver to work on the range. This may take a few minutes depending on the size of data to be processed.
Once finished, you will get the message below
Click on OK.
The rows with net offs will be highlighted changing the values in Column ‘A’ to 1s.
I hope this tip was helpful, please share it with your friends and let me know your feedback in the comment box below.
Follow us on:
