close sharing button

Find numbers that net off to zero in range of cells


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 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.



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 solver menu, if solver menu is not available in Excel, then you may need to install the add-in, follow 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


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 Solver dialog, select the options as shown in the below screenshot.



In Set Objective enter cell address $H$1 To Select option ‘Max’
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 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 cell reference field Enter Cell Address $H$2. 

In the drop-down select '<=' and in '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 féedback in the comment box below.


No comments:

Post a Comment