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