# Thread: Combination of cells to give a value (2000/any)

1. ## Combination of cells to give a value (2000/any)

I was wondering if anyone knows how to get Excel to give you a list of cell values (or maybe even highlight the cells) that yield a particular value...for example, I may have 100 cells and need to know which combinations of these 100 yield a value that I type in another cell...understand?? I'm thinking that this might have to be done through VBA??

2. ## Re: Combination of cells to give a value (2000/any)

You can highlight the cells without VBA by using conditional formatting. To apply that follow these steps :-

1. Enter the target value in a Cell, lets say F3

2. Select the range of cells you wish highlight

3. Goto Format, select Conditional Formatting.

4. In the resultant dialog, you can set a reference to the target value (e.g. \$F\$3, make sure you use absolute addressing, i.e. include the \$ signs).

5. In the drop down list beside Cell Value Is, you can select the appropriate condition, i.e. Equal to, greater than or equal to, etc.

I have attached a sample worksheet as above. If you need the list, that can be done also but would be more complcated.

Andrew C

3. ## Re: Combination of cells to give a value (2000/any)

Thanks anyway Andrew, but what I actually mean is if cell F3 is,say, 300, I need to be able to select column A and ask Excel to identify which combinations of a particular number of cell values in column A will yield F3, or 300. For my purposes, there will only ever be one combination of particular cells in column A that will equal F3. Sorry for not being clearer!!

4. ## Re: Combination of cells to give a value (2000/any)

What are the minimum and maximum number of cells that can be combined to get the desired result? Is one cell that equals the result OK? How do you want the cells identified?

This can be done with a macro, but as the maximum number of cells that can be combined and the number of values in the list increases, the time that the macro will run is going to go up exponentially.

5. ## Re: Combination of cells to give a value (2000/any)

I suppose that I was hoping for an easy answer that doesn't exist...something like:

=COMBINEDTOEQUAL(A:A, 2, "5") OR
=COMBINEDTOEQUAL(A:A, 2, F3)

where you would like 2 cells from the range A:A to equal either "5" in the first instance, or the cell value of F3 in the second...so in answer to your question you could set the maximum and minimum number yourself, and if you so desired, you could set it to only look for 1 cell to give you 5, or F3, but then you could just as easily do a find...

Any takers??

6. ## Re: Combination of cells to give a value (2000/any)

Writing a routine to find a combination of a specific number of cells that equals a specific value is not difficult. It just requires writing nested For loops equal to the number of cells in the combination. For a combination of two cells you need two nexted For loops, three cells requires three nested For loops, etc. However, to write a routine for a variable number of cells to combine requires a rather complex recursive subrotine. In addition, how would you want that function to return the cells?

The function below, when passed a cell range (must be a single column) and a target value, will return an array of up to three cells which combine to give the target.

<pre>Public Function CombineToEqual(oCells As Range, dTarget As Double) As Variant
Dim I As Long, J As Long, K As Long, lRows As Long
Dim dSum As Double
lRows = oCells.Rows.Count
With Cells(oCells.Rows(1).Row, oCells.Columns(1).Column)
If lRows < 3 Then
CombineToEqual = False
Exit Function
End If
For I = 1 To lRows - 2
If .Offset(I - 1, 0) = dTarget Then
CombineToEqual = Array(I, 0, 0)
Exit Function
End If
For J = I + 1 To lRows - 1
If .Offset(I - 1, 0) + .Offset(J - 1, 0) = dTarget Then
CombineToEqual = Array(I, J, 0)
Exit Function
End If
For K = J + 1 To lRows
If .Offset(I - 1, 0) + .Offset(J - 1, 0) + _
.Offset(K - 1, 0) = dTarget Then
CombineToEqual = Array(I, J, K)
Exit Function
End If
Next K
Next J
Next I
End With
CombineToEqual = False
End Function
</pre>

#### Posting Permissions

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