offsetting positive negative

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Offsetting positive & negative values on 1 column | Excel forum

Offsetting positive & negative values on 1 column | Excel forum

resolvedResolved · Medium Priority · Version 365

Fahim has attended:
Excel Intermediate course

Offsetting positive & negative values on 1 column

I have over 50,000 numbers (positive and negative) in 1 column on a dataset and I want to find offsetting values clearing each other. Is there a way to find this please.



RE: Offsetting positive & negative values on 1 column

Hi Fahim

This is a good question Fahim and likely is connected to your need to reconcile accounting/bank statement information

There is no one formula for the task and why there is specialist accounting software to match these numbers at volume

You could sort the data so that all positive values are in one column and all negative values are in column two. You can convert a negative to a positive using =-B2 (if B2 is negative then negative and negative make a positive number )

You could then sort both columns in ascending order and match them up using an IF function =IF(A2=B2,"MATCH", "NO")

I hope that gets you started on solving this


Kind regards

Richard
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector


 

Excel tip:

Missing Field handle

If your field handle goes missing all you need to do is go to tools > options > edit tab and then make sure that the check boxes for paste and insert buttons are checked.

View all Excel hints and tips


Server loaded in 0.07 secs.