Painting cells whose sum is zero

[zizao]

I'm from Brazil and I need your help to solve a spreadsheet.

I have the following values:



8881.92
-6,735.58
-196546.73
-10,229.00
-6,003.60
-69.60
211,632.56
-2,878.32
-4,856.83
-137.24
-8,031.08



I need to paint the cells whose sum is zero.

Note that 8881.92 + - 6003.60 + - 2878.32 = zero
and - 196,546.73 + 211,632.56 + - 10,229.00 + - 4856.83 = zero


So I need formula or macro to paint those cells whose sum is zero, so:



8881.92
-6,735.58
-196546.73
-10,229.00
-6,003.60
-69.60
211,632.56
-2,878.32
-4,856.83
-137.24
-8,031.08



Spreadsheet with real data: Pintar1.xlsx


Thanks in advance.

[Kaper]

Hi zizao,

First take some time to read http://www.excelforum.com/forum-rule...rum-rules.html and attach your file properly (not as link to external server).

As of merit - I do not think it would be possible to do this with formula.

for limited number of values it can be done with macro, but for large datasets - this will be really very timeconsuming process.
So let us know what is your real dataset size - what number of values would you treat this way.

[zizao]

Hi Kaper,

I have databases with 5 values ​​and also larger ones with 600 values​​, eg.

I also think macros would be more useful in this case. But I have no idea where to start.

Thanks for the reply.

[Kaper]

So strightforward approach to code a macro is probably really useless (without extra constraints), because complexity of problem is 2^n (some 10^180 cases for 600 values ).

And I assume, that you need exactly zero. Not close enough to zero, like +/- 1.


As you posted a workbook, here is proposition of randomized locating of such groups based on my recent post: http://www.excelforum.com/excel-gene...ml#post3609912

Please Login or Register  to view this content.

But while it is reasonably efficient with small sets (as in sheet1 of example). in large dataset - it gives up

Funny side observation - rarely it is able to spot group two "groups" together - from maths point of view - absolutely correct, because if:
8881.92 + - 6003.60 + - 2878.32 = zero
and - 196,546.73 + 211,632.56 + - 10,229.00 + - 4856.83 = zero
then
8881.92 + - 6003.60 + - 2878.32 + - 196,546.73 + 211,632.56 + - 10,229.00 + - 4856.83 = zero too.
such result is visible in first sheet.

Color coding of data is not cleared, so after runnimg a macro color interiors remain. (change in code clearcontents to delete if you wish).

[zizao]

Perfect! That's exactly what I needed.

I believe it will be useful for me because the databases I have are are about this size.

Thank you, Kaper