SUM TO ZERO

Dear Users, I have over 6000 data and i want to Match all amount that sums to zero or a set value without the try and error method which takes time and prone to errors, please how can i achieve t...

BI & Data Analysis

Charting

excel

Excel on Mac

Formulas and Functions

Macros and VBA

power bi

IDRIS_A

Copper Contributor

Nov 06, 2020

mathetes Thanks for your response. yes it is to identify, attached shows a random sample which i am sure sum of the highlighted is equal to zero (which is what i want to achieve).

suppose i have 6000 or more cells cells to identify . how best can i go about it. i hope this clarify my questions.

SAMPLE.xlsx14 KB

MindreVetande

Iron Contributor

Nov 06, 2020

IDRIS_A

Do you want to find/markup any positive number that has a matching negative number (and any negative number with a positive match)? Try something like this i B2

=Countif(A:A,-A2)

And copy down.

0 there is no match with opposite sign

1 there is one match with opposite sign

>1 (ambivalent)There is at least one match with opposite sign, but the match might be "used" by another number with the same sign

Try some kind of IF statement to sort things out

=if(countif(A:A,-A2)=1,"pair",if(Countif(A:A,-A2)=0,"alone",IF(COUNTIF(A:A,-A2)=Countif(A:A,A2),"Even pairs")))

I'm using a non-english excel and i translating in my head, Check the spelling of the formulas

/DS