sorting data to obtain a fixed amount

  • Thread starter sorting data to obtain a fixed amount
  • Start date
S

sorting data to obtain a fixed amount

Please help!! I have numbers in a row of cells and I would like to have a
formula to sort the data to give me a fixed total... (like which cells would
equal x) i don't know which of the cells make up that total... can excel tell
me which cell combinations would equal any given amount entered...
 
G

glenton (glenton

This is not a trivial sort of calculation at all. The number of cell
combination is 2^n, so it gets seriously big quickly. E.g. 2^100 is far more
electrons than you could squeeze into the universe, so if you've got 100 rows
you're probably not going to do it with a "brute force" method.

Obviously it would help if you apply your mind first, e.g. if all the
numbers are positive, eliminate all rows that are bigger than the total
you're looking for, and checking for repeated values in your list (do you
have any, cos than the below method could be improved quite a bit).

A way you could do this is to write a macro that runs from 0 to 2^n-1. Then
convert this number to binary, populate an adjacent row with the digits of
the binary number, sumproduct these two rows, and save the values which are
equal to your target value. Or just save all 2^n values if you're going to
all this effort. Of course that limits you to 16 rows!

All the best
Glenton
www.leviqqio.com
 
Top