Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2001
    Location
    Canberra, ACT, Australia
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Jul 2001
    Location
    Canberra, ACT, Australia
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Jul 2001
    Location
    Canberra, ACT, Australia
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

Posting Permissions

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