ExcelTip.com
Account Icon Account Icon Account Icon
ExcelTip.com

Go Back   Excel Help Forum > Limelight Media - ExcelTip.com Books > F1 Get the most out of Excel Formulas & Functions

Notices

Closed Thread
 
Thread Tools Search this Thread Rate Thread Display Modes
  #1  
Old 08-31-2007, 12:38 AM
pol67 pol67 is offline
Registered User
 
Join Date: 30 Aug 2007
Posts: 2
pol67 is on a distinguished road
Sum of the possible numbers in a column

I need Excel formula to find one amount who is sum of more than one number in a column.

At the given example 279 is the research amount from column B


A B

1 2,251.00
2 2,136.00
3 10.00
4 279.00 56,633.00
5 13.00
6 256.00
7 12,513.00
8 504.76
9 43.62
10 31.44
11 338.48
12 82.39
13 72.21
14 87.24
15 1,090.75
16 141.29
17 208.03
18 55.56
19 71.64


The sum can be from 2 to 14 numbers. How can I be sure to find that my 279 is exactly the amount of 10+13+256.
In fact this is possibility to pick up them to complete the amount.

Thanks in advance
  #2  
Old 08-31-2007, 05:10 PM
mrice's Avatar
mrice mrice is offline
Forum Guru
 
Join Date: 22 Jun 2004
Location: Surrey, England
Posts: 1,454
mrice is on a distinguished road
This is a combinations problem and can be addressed with the following macro. It takes a significant time to run as there are obviously a lot of combinations to potentially consider.

Code:
Sub Test()
Dim MyArray()
TargetValue = 279
LastRow = Cells(65536, 2).End(xlUp).Row
CombinationColumn = 2
For N = 1 To 2 ^ LastRow
    MyNumber = N
    ReDim MyArray(LastRow - 1)
    For M = LastRow - 1 To 0 Step -1
        If 2 ^ M <= MyNumber Then
            MyArray(M) = True
            MyNumber = MyNumber - 2 ^ M
        Else
            MyArray(M) = False
        End If
    Next M
    Total = 0
    For M = 0 To LastRow - 1
        If MyArray(M) = True Then Total = Total + Cells(M + 1, 2)
        If Total > Total Then Exit For
    Next M
    If Total = TargetValue Then
        CombinationColumn = CombinationColumn + 1
        For M = 1 To LastRow
            If MyArray(M - 1) = True Then Cells(M, CombinationColumn) = "X"
        Next M
    End If
Next N
End Sub
__________________
Martin

Eighty Twenty Spreadsheet Automation http://homepage.ntlworld.com/martin.rice1/ for all your Excel customisation and consulting needs.
  #3  
Old 09-01-2007, 12:44 AM
pol67 pol67 is offline
Registered User
 
Join Date: 30 Aug 2007
Posts: 2
pol67 is on a distinguished road
Thanks a lot Martin
I trayed with my example. It works perfectly.
I`ll try with other amounts and I hope that it will be good too.


pol
Closed Thread

Bookmarks

New topics in F1 Get the most out of Excel Formulas & Functions


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off
Forum Jump


All times are GMT -4. The time now is 12:12 PM.


Powered by vBulletin® Version 3.7.3
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Search Engine Friendly URLs by vBSEO 3.2.0