Forum Discussion
FORMULA TO OFFSET NEGATIVE AND POSITVE NUMBERS
Good morning I am looking for a formula where I can offset a worksheet with positive and negative numbers without manually finding the opposite number. For example if I have 100 I want to be abl...
flexyourdata this is something simple but I have a set of numbers both positive and negative and I am trying to create a formula where a can match the positive to the negative number so that it will net out to be zero. I will be able to separate the two to see what numbers don't have an offsetting number
| 1304888 | 950061 | 9/8/2020 | INVOICE | 116.44 | ||||
| 1313746 | 956297 | 10/5/2020 | INVOICE | 3,379.49 | ||||
| 1314524 | 956273 | 10/8/2020 | INVOICE | 469.44 | ||||
| 1316507 | 957971 | 10/12/2020 | INVOICE | 5,046.93 | ||||
| 1316843 | 957949 | 10/13/2020 | INVOICE | 319.47 | ||||
| 1316845 | 957948 | 10/13/2020 | INVOICE | 511.95 | ||||
| 1319153 | 959680 | 10/19/2020 | INVOICE | 3,314.40 | ||||
| 1319180 | 959680 ADD | 10/19/2020 | INVOICE | 237.3 | ||||
| 1319721 | 959664 | 10/20/2020 | INVOICE | 460.31 | ||||
| 1319739 | 959670 | 10/20/2020 | INVOICE | 376.61 | ||||
| 1321015 | 960672 | 10/26/2020 | INVOICE | 2,032.20 | ||||
| 1321960 | 961296 | 10/26/2020 | INVOICE | 4,916.23 | ||||
| 1322223 | 961267 | 10/27/2020 | INVOICE | 548.74 | ||||
| 1322454 | 961266 | 10/27/2020 | INVOICE | 556.72 | ||||
| 1304888 | 950061 | 9/8/2020 | INVOICE | -116.44 | ||||
| 1313746 | 956297 | 10/5/2020 | INVOICE | -3,379.49 | ||||
| 1314524 | 956273 | 10/8/2020 | INVOICE | -0.46944 | ||||
| 1316507 | 957971 | 10/12/2020 | INVOICE | -5,046.93 | ||||
| 1316843 | 957949 | 10/13/2020 | INVOICE | -319.47 | ||||
| 1316845 | 957948 | 10/13/2020 | INVOICE | -511.95 | ||||
| 1319153 | 959680 | 10/19/2020 | INVOICE | -3,314.40 | ||||
| 1319180 | 959680 ADD | 10/19/2020 | INVOICE | -237.3 | ||||
| 1319721 | 959664 | 10/20/2020 | INVOICE | -460.31 | ||||
| 1319739 | 959670 | 10/20/2020 | INVOICE | -376.61 | ||||
| 1321015 | 960672 | 10/26/2020 | INVOICE | -2,032.20 | ||||
| 1321960 | 961296 | 10/26/2020 | INVOICE | 4,916.23 | ||||
| 1322223 | 961267 | 10/27/2020 | INVOICE | 548.74 | ||||
| 1322454 | 961266 | 10/27/2020 | INVOICE | 1304888 | 950061 | 9/8/2020 | INVOICE | 116.44 |
| 1313746 | 956297 | 10/5/2020 | INVOICE | 3,379.49 | ||||
| 1314524 | 956273 | 10/8/2020 | INVOICE | 469.44 | ||||
| 1316507 | 957971 | 10/12/2020 | INVOICE | 5,046.93 | ||||
| 1316843 | 957949 | 10/13/2020 | INVOICE | 319.47 | ||||
| 1316845 | 957948 | 10/13/2020 | INVOICE | 511.95 | ||||
| 1319153 | 959680 | 10/19/2020 | INVOICE | 3,314.40 | ||||
| 1319180 | 959680 ADD | 10/19/2020 | INVOICE | 237.3 | ||||
| 1319721 | 959664 | 10/20/2020 | INVOICE | 460.31 | ||||
| 1319739 | 959670 | 10/20/2020 | INVOICE | 376.61 | ||||
| 1321015 | 960672 | 10/26/2020 | INVOICE | 2,032.20 | ||||
| 1321960 | 961296 | 10/26/2020 | INVOICE | 4,916.23 | ||||
| 1322223 | 961267 | 10/27/2020 | INVOICE | 548.74 | ||||
| 1322454 | 961266 | 10/27/2020 | INVOICE | 556.72 | ||||
| 1304888 | 950061 | 9/8/2020 | INVOICE | -116.44 | ||||
| 1313746 | 956297 | 10/5/2020 | INVOICE | -3,379.49 | ||||
| 1314524 | 956273 | 10/8/2020 | INVOICE | -0.46944 | ||||
| 1316507 | 957971 | 10/12/2020 | INVOICE | -5,046.93 | ||||
| 1316843 | 957949 | 10/13/2020 | INVOICE | -319.47 | ||||
| 1316845 | 957948 | 10/13/2020 | INVOICE | -511.95 | ||||
| 1319153 | 959680 | 10/19/2020 | INVOICE | -3,314.40 | ||||
| 1319180 | 959680 ADD | 10/19/2020 | INVOICE | -237.3 | ||||
| 1319721 | 959664 | 10/20/2020 | INVOICE | -460.31 | ||||
| 1319739 | 959670 | 10/20/2020 | INVOICE | -376.61 | ||||
| 1321015 | 960672 | 10/26/2020 | INVOICE | -2,032.20 | ||||
| 1321960 | 961296 | 10/26/2020 | INVOICE | 4,916.23 | ||||
| 1322223 | 961267 | 10/27/2020 | INVOICE | 548.74 | ||||
| 1322454 | 961266 | 10/27/2020 | INVOICE | 556.72 |
Tonya1205 Perhaps a pivot table (see attached) is what you need. Not sure though what the data at the bottom is for. It just seems to be a duplication op the top part but shifted a few columns.