Excel - Solve for List of Numbers

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Good afternoon - I'm stuck with the highly prized role of correcting
someone elses mistakes, which are numerous. Is it possible that there
is some type of Excel add-in or VBA that could highlight which numbers
in a block of numbers sum to another number?

For example, I have a cell range that contains a block of number eg
A1 - 1
A2 - 20
A3 - 15
A4 - 36
A5 - 47

and I want Excel to highlight or tell me which combination of cells
add up to another number.

For example, if I'd like to solve for 21, I'd like Excel to highligh
cells A1 & A2. If I'd like to solve for 46, Excel would highlight
cells A1, A2, A3.

Any thoughts?
Brian
 
If it's not too many cells involved, SOLVER can do this.
With the setup you describe, in C1 enter =A1*B1 and fill down to C5 (B1:B5
are blank)
in C6 put =SUM(C1:C5)
Use solver and set up the constraints for
Set Cell C6 to value 21 (for example);
B1:B5 are integers
B1:B5 >=0
B1:B5 <=1
Solve.
The cells in B1:B5 which become 1 are the corresponding items which will add
up to the desired result.
It works for longer values but courl take a long time!

Bob Umlas
Excel MVP
 
Bob - many many thanks - this works like a champ! Looks like it only
allows for 32 cells to solve for, but this should make my life easier.
I was looking at the solver add-in, but didn't get far enough outside
the box!

Thanks again,
Brian
 
Back
Top