Forum Discussion
SUM TO ZERO
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.
You're correct that the highlighted cells do sum to zero.
That raises at least a number of questions:
- How did you identify them? (Or was this just a made-up set where you simply changed the signs?)
- Presumably every number in those highlighted ranges (or the vast majority of them) has a matching number:
- do you want therefore to identify
- every pair that sums to zero,
- every set of four that sums to zero,
- every set of six that sums to zero,
- etc
- Or just the largest set (as you have in your sample)
- ?
- do you want therefore to identify
You need to become a lot clearer.....
and maybe you could describe the context you're working in here as well, so long as it's not confidential. Maybe there's a different way to meet the objective.
IDRIS_A as mathetes (and others) have said we need more information to understand exactly what you want/need. But looking at the number it almost appears that you have a ledger entries and are trying to find values that don't match up and need to be investigated. So in that case or similar you want to have paired values highlighted (or ignored). Here is a conditional formatting formula you can use to highlight any value that does not have a corresponding matched but opposite value. Note that it will highlight the FIRST x number of occurrences of that value so if you have 5, 5, -5, 5, 5, -5, -5 it will highlight the FIRST 5 and not the following 5s or -5s because those have 'matches'. BUT if this is a ledger and you want to know the unmatched line item you may need additional info to distinguish which 5 and -5 actually match up. AGAIN, we need more info to fully help.
Here is the Conditional Formatting formula I mentioned and used in the attached sheet to highlight the UNmatched values in RED:
=((COUNTIF(A:A,A1)-COUNTIF(A:A,-A1))>=COUNTIF($A$1:A1,A1))*ISNUMBER(A1)
