how to determine which values in a given set of cells will add up to a numerical range

2

In other words, I'm looking for a sum range that consists of combinations of cells from an array. Given a set of numbers:

1 720 56 17 59 120 153 203 39 1 690 583 582 561 256 310 232 95 108 16 26 59 538 445 42 149

(call this A1:A26)

The sum range I'm looking for is 1000-1500. I'd like to be able to see which combinations of any cells from A1:A5 will have a sum within that range. Such as taking the sum of (A23, A24, A26) or the sum of (A2, A11).

The number of cells or combinations do not matter, as long as the sum falls within the given range. On top of that, I need to be able to identify the cells used in each combination.

I am grateful to anybody who can make my life any easier.
Thank you.

Eric

Posted 2013-07-12T19:26:57.087

Reputation: 21

1Welcome to superuser. What have you tried so far to accomplish this? – CharlieRB – 2013-07-12T19:54:13.133

2

Oblig XKCD: http://xkcd.com/287/ (Not exactly the same scenario but close enough.)

– RLH – 2013-07-12T20:17:02.227

1As I’m sure you realize, there are POWER(2,n) possible combinations (subsets) of a set with n members.  I came up with a worksheet function solution, but it requires POWER(2,n) columns (or rows, if you transpose it).  POWER(2,26) is 67108864.  I’m not going to post my answer, because, even if Excel can handle that many columns (or rows), it’s not practical.  I suggest you look into VBA (and retag your question accordingly). – Scott – 2013-07-13T00:14:03.990

Maybe if you explain what the ultimate goal of an operation like this is, we would be able to point you in the right direction. – Raystafarian – 2013-07-13T08:40:22.513

Im going to try to answer this but assume you cannot use the same cell more than once. – Scheballs – 2013-07-31T13:04:14.553

In addition to @Raystafarian's comment: tell us where this set of numbers comes from, that may help. Something must generate them, and the method or moment of generation might help to tackle the problem. – Jan Doggen – 2013-08-01T06:14:22.420

Answers

1

Let's go a different direction and show what two cells can be summed to meet the critera see image below.

Still a great challenge of a question. I will keep playing with a more complete VBA answer if possible.

enter image description here

I must break for a while, but here is what I have made to show you the distinct values that would meet the criteria. I am by no means an expert in VBA, I merely took this as a learning experience. I am sure I broke a few rules.

Sub WhatCanSUM()

Dim lst As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim ilst1 As Integer
Dim ilst2 As Integer
Dim ilst3 As Integer
Dim ilst4 As Integer
Dim ilst5 As Integer
Dim ilst6 As Integer
Dim ilst7 As Integer
Dim ilst8 As Integer
Dim ilst9 As Integer
Dim ilst10 As Integer
Dim ilst11 As Integer
Dim ilst12 As Integer
Dim ilst13 As Integer
Dim ilst14 As Integer
Dim ilst15 As Integer
Dim ilst16 As Integer
Dim ilst17 As Integer
Dim ilst18 As Integer
Dim ilst19 As Integer
Dim ilst20 As Integer
Dim ilst21 As Integer
Dim ilst22 As Integer
Dim ilst23 As Integer
Dim ilst24 As Integer
Dim ilst25 As Integer
Dim ilst26 As Integer

Dim lwrlmt As Integer
Dim uprlmt As Integer
Dim result As Integer

Set lst = Sheet1.Range("lstNumbers")
i = 1
j = 1
k = 1
ilst1 = lst.Item(1).Value
ilst2 = lst.Item(2).Value
ilst3 = lst.Item(3).Value
ilst4 = lst.Item(4).Value
ilst5 = lst.Item(5).Value
ilst6 = lst.Item(6).Value
ilst7 = lst.Item(7).Value
ilst8 = lst.Item(8).Value
ilst9 = lst.Item(9).Value
ilst10 = lst.Item(10).Value
ilst11 = lst.Item(11).Value
ilst12 = lst.Item(12).Value
ilst13 = lst.Item(13).Value
ilst14 = lst.Item(14).Value
ilst15 = lst.Item(15).Value
ilst16 = lst.Item(16).Value
ilst17 = lst.Item(17).Value
ilst18 = lst.Item(18).Value
ilst19 = lst.Item(19).Value
ilst20 = lst.Item(20).Value
ilst21 = lst.Item(21).Value
ilst22 = lst.Item(22).Value
ilst23 = lst.Item(23).Value
ilst24 = lst.Item(24).Value
ilst25 = lst.Item(25).Value
ilst26 = lst.Item(26).Value
lwrmt = 1000
uprlmt = 1500
result = 0

'===============================================================================================
'Create worksheet if it doesnt exist.

Dim wrslt As Worksheet
Const strSheetName As String = "Results"

Set wrslt = Nothing
On Error Resume Next
Set wrslt = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0

If wrslt Is Nothing Then
    Worksheets.Add.Name = strSheetName
End If
'===============================================================================================
'Little header messagge

Set wrslt = ActiveWorkbook.Worksheets(strSheetName)
wrslt.Cells.Delete
wrslt.Cells(1, 1).Value = "Resulting Additions that 2 distinct cells that sum up to >=" & lwrmt & " and <=" & uprlmt

'===============================================================================================
'The Loop

For j = 1 To lst.Rows.Count

    For i = 1 To lst.Rows.Count
    ilst2 = lst.Item(i + 1).Value
        result = (ilst1 + ilst2)
        If ilst1 <> ilst2 And result >= lwrmt And result <= uprlmt Then
            wrslt.Cells(i + 1, j).Value = ilst1 & " + " & ilst2
        End If
    Next i

    ilst1 = lst.Item(j + 1).Value

Next j

MsgBox ("Done")
'===============================================================================================
'Formatting

wrslt.Range("A1:M1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge

wrslt.Cells.EntireColumn.AutoFit
wrslt.Cells.SpecialCells(xlCellTypeConstants, 23).Select

End Sub

The macro produces the picture below. enter image description here

Scheballs

Posted 2013-07-12T19:26:57.087

Reputation: 383

1Your n-dimensional Excel clone is probably still at work on n= 3,4,5...26 ;-) – Jan Doggen – 2013-07-31T13:13:54.103

Posted some VBA code that at this stage just shows what Two Distinct values from the list can be added that meet the criteria. – Scheballs – 2013-07-31T15:39:00.240

Does that work? The loop looks funky. – Raystafarian – 2013-08-01T08:24:14.367

@Raystafarian I added a photo of the output on the created Results worksheet. – Scheballs – 2013-08-01T19:10:55.897

hm. nicely done. – Raystafarian – 2013-08-01T20:02:40.760