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...
Hi Tonya1205, can you provide some context? Are you actually searching for this "opposite" in some range of cells?
The "opposite" of any number is just that number multiplied by -1.
So, you can use FIND, XLOOKUP, VLOOKUP, MATCH, XMATCH and so on by passing that into the search parameter of whichever function you choose.
If you upload an example, someone can help more easily.
The "opposite" of any number is just that number multiplied by -1.
So, you can use FIND, XLOOKUP, VLOOKUP, MATCH, XMATCH and so on by passing that into the search parameter of whichever function you choose.
If you upload an example, someone can help more easily.
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.
- I thought about a pivot table but it won't work for what I need. I will try to give a better example. I am trying to offset a balance sheet and need a formula so that I am not going line by line to find the offsetting transaction.
