Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2006
    Posts
    2

    Unanswered: Excel- Find a value or values within a range that add to a number

    I am trying to find a formula that will give me the cells that add up to a given value. Think of a reconciliation process in which you are off by x amount and x can be the sum of multiple cells positive and negative. Is there an easy way or a formula that can help with that?

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by Javi767
    I am trying to find a formula that will give me the cells that add up to a given value. Think of a reconciliation process in which you are off by x amount and x can be the sum of multiple cells positive and negative. Is there an easy way or a formula that can help with that?
    I don't know of a single formula to perform that action. If you had 100 cells of values there could be multiple combinations of cells with numbers that give the same answer. If you wanted, you could think it through and come up with some type of algorithm.
    ~

    Bill

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. One possibility is to use Goal Seek (Analysis ToolPak). But it would take some planning to do so.

    If I were to attempt this, I might consider using VBA instead of an Excel-function/formula approach.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Feb 2006
    Posts
    113
    There is no easy way and no formula. VBA will be required. The best solution I saw - too long ago now to remember where - used recursion. It was only a dozen or two lines of code and worked quickly and very well. It was limited to about 2 dozen input values however, 'cause after that it took 'forever'. HTH

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Tushar Mehta has just posted a template that does this very thing:
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Dec 2006
    Posts
    2

    Thanks!

    Thank you all for your response, the template really helps!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •