Forum Discussion

Tonya1205's avatar
Tonya1205
Copper Contributor
May 16, 2022

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...
Formulas and Functions
flexyourdata's avatar
flexyourdata
Iron Contributor
May 16, 2022
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.
Tonya1205's avatar
Tonya1205
Copper Contributor
May 17, 2022

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 

13048889500619/8/2020INVOICE116.44    
131374695629710/5/2020INVOICE3,379.49    
131452495627310/8/2020INVOICE469.44    
131650795797110/12/2020INVOICE5,046.93    
131684395794910/13/2020INVOICE319.47    
131684595794810/13/2020INVOICE511.95    
131915395968010/19/2020INVOICE3,314.40    
1319180959680   ADD10/19/2020INVOICE237.3    
131972195966410/20/2020INVOICE460.31    
131973995967010/20/2020INVOICE376.61    
132101596067210/26/2020INVOICE2,032.20    
132196096129610/26/2020INVOICE4,916.23    
132222396126710/27/2020INVOICE548.74    
132245496126610/27/2020INVOICE556.72    
13048889500619/8/2020INVOICE-116.44    
131374695629710/5/2020INVOICE-3,379.49    
131452495627310/8/2020INVOICE-0.46944    
131650795797110/12/2020INVOICE-5,046.93    
131684395794910/13/2020INVOICE-319.47    
131684595794810/13/2020INVOICE-511.95    
131915395968010/19/2020INVOICE-3,314.40    
1319180959680   ADD10/19/2020INVOICE-237.3    
131972195966410/20/2020INVOICE-460.31    
131973995967010/20/2020INVOICE-376.61    
132101596067210/26/2020INVOICE-2,032.20    
132196096129610/26/2020INVOICE4,916.23    
132222396126710/27/2020INVOICE548.74    
132245496126610/27/2020INVOICE13048889500619/8/2020INVOICE116.44
    131374695629710/5/2020INVOICE3,379.49
    131452495627310/8/2020INVOICE469.44
    131650795797110/12/2020INVOICE5,046.93
    131684395794910/13/2020INVOICE319.47
    131684595794810/13/2020INVOICE511.95
    131915395968010/19/2020INVOICE3,314.40
    1319180959680   ADD10/19/2020INVOICE237.3
    131972195966410/20/2020INVOICE460.31
    131973995967010/20/2020INVOICE376.61
    132101596067210/26/2020INVOICE2,032.20
    132196096129610/26/2020INVOICE4,916.23
    132222396126710/27/2020INVOICE548.74
    132245496126610/27/2020INVOICE556.72
    13048889500619/8/2020INVOICE-116.44
    131374695629710/5/2020INVOICE-3,379.49
    131452495627310/8/2020INVOICE-0.46944
    131650795797110/12/2020INVOICE-5,046.93
    131684395794910/13/2020INVOICE-319.47
    131684595794810/13/2020INVOICE-511.95
    131915395968010/19/2020INVOICE-3,314.40
    1319180959680   ADD10/19/2020INVOICE-237.3
    131972195966410/20/2020INVOICE-460.31
    131973995967010/20/2020INVOICE-376.61
    132101596067210/26/2020INVOICE-2,032.20
    132196096129610/26/2020INVOICE4,916.23
    132222396126710/27/2020INVOICE548.74
    132245496126610/27/2020INVOICE556.72
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    May 17, 2022

    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.

     

    • Tonya1205's avatar
      Tonya1205
      Copper Contributor
      May 18, 2022
      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.
      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        May 18, 2022

        Tonya1205 Perhaps the attached file contains something you could use. It a small example I picked up a long time ago on myonlinetraininghub. Originally meant for bank reconciliations, you can use the same technique for for matching debits and credits in any transaction list. The helper column B in the table holds the formula that finds offsetting amounts.

Resources