How to identify/mark rows in a column that equals a sum..

**Stealth1965** · 08-22-2011, 03:36 AM

Hi everyone,

I'm Jojo from Philippines and i have been working on one project about excel...

i need help in identifying number of rows (numeric) in a column that is equal to a certain sum derived from the same column of numbers...

example:

Column A:
1
2
3
4
5
6
7
8

25 - is the given sum

question: identify the numbers that will total to the given sum above, if exact total is not possible from the given series of numbers then it will say not possible, but if found the exact combination of numbers then it will change the text color from default black to blue.

Thank you in advance,

Jojo Aguirre

**tarquinious** · 08-22-2011, 10:41 AM

This is an extremely difficult one to solve. Basically you want a macro (as this cannot be done with a simple formula) to work out every unique combination of values and find which one adds up nearest to the value.

Unfortunately I cannot think of an easy way to get every unique combination.

What I have attached is close, but still does not do exactly what you're after. It is a good starting point for somebody who might know how to work out all of the different combinations though.

**Stealth1965** · 08-22-2011, 07:16 PM

Originally Posted by tarquinious

This is an extremely difficult one to solve. Basically you want a macro (as this cannot be done with a simple formula) to work out every unique combination of values and find which one adds up nearest to the value.

Unfortunately I cannot think of an easy way to get every unique combination.

What I have attached is close, but still does not do exactly what you're after. It is a good starting point for somebody who might know how to work out all of the different combinations though.

@tarquinious... thank you very much just for replying, i really appreciate the information of knowing the level of difficulty of this particular project.

To anyone, please help me solve this....

**tigertiger** · 08-22-2011, 07:35 PM

Originally Posted by Stealth1965

@tarquinious... thank you very much just for replying, i really appreciate the information of knowing the level of difficulty of this particular project.

To anyone, please help me solve this....

You can by SOLVER for the solution to find the combination and then using the format conditional to color the text

**Stealth1965** · 08-22-2011, 10:08 PM

Thank you Tigertiger... i will try it as you adviced, however, can you send me what formula do yo have in mind?...

God bless

**Alf** · 08-24-2011, 04:14 PM

It is as tarquinious says extermly difficult to solve.

I did a test run with solver (Excel 2003) and got a difference between target value and solver value of 0,05.

Still this is only one solution and there are a lot more solutions. I also did sort the values before running Solver. A thing that probably influence the result.

Tushar Mehta has some example on how to solve problem like this:

http://www.tushar-mehta.com/excel/te.../match_values/

I've uploaded an excel file with the Solver parameter. Before running solver clear range C3 to C33.

Alf