Adding columns to make a specified value.
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Adding columns to make a specified value.

  1. #1
    Guest

    Default

     
    I have 5 columns, 3 with 22 entries and two with 11 entries. The entries are numbers between 1 and 24. I have to work out all of the combinations that would include 5 numbers, one from each column, that add up to 45. How can I do this?

  2. #2
    New Member
    Join Date
    Feb 2002
    Location
    Belgium
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It is important to know if the values in the columns without entries are equal to 0 and can be counted as:
    C1 C2 C3 C4 C5
    11 11 11 12 0

    C1+C2+C3+C4+C5=45
    Is this acceptable or not?

    I yes then, and if you have 22 entries in your 3 first columns, and 11 in your 2 last, then code follows...

    (this code is correct if you match 45 exactly, other wise change the following if 'sum = 45 then count= count+1' for if sum <= 45 then count= count+1 )

    sub Sum45()
    dim i as byte
    dim j as byte
    dim k as byte
    dim l as byte
    dim m as byte
    dim sum as byte
    dim Count as integer

    count=0
    For i=1 to i=1+22
    sum=Val(cells(i,1))
    for j=1 to j=1+22
    sum=Val(cells(i,1))+Val(cells(j,2))
    if sum =< 45 then
    for k=1 to K=1+22
    sum=Val(cells(i,1))+Val(cells(j,2))+Val(Cells(k,3))
    if sum<45 then
    for l=1 to l=1+11
    sum=Val(cells(i,1))+Val(cells(j,2))+Val(Cells(k,3))+Val(Cells(l,4))
    if sum<45 then
    for m=1 to m=1+11
    sum=Val(cells(i,1))+Val(cells(j,2))+Val(Cells(k,3))+Val(Cells(l,4))+Val(cells(m,5))
    if sum = 45 then count= count+1
    next m
    end if
    next l
    end if
    nexr k
    end if
    next j
    next i
    end sub

    I hope I could help you,
    Pierre

  3. #3
    Guest

    Default

      
    On 2002-02-21 05:41, paludgnp wrote:
    It is important to know if the values in the columns without entries are equal to 0 and can be counted as:
    C1 C2 C3 C4 C5
    11 11 11 12 0

    C1+C2+C3+C4+C5=45
    Is this acceptable or not?

    I yes then, and if you have 22 entries in your 3 first columns, and 11 in your 2 last, then code follows...

    (this code is correct if you match 45 exactly, other wise change the following if 'sum = 45 then count= count+1' for if sum <= 45 then count= count+1 )

    sub Sum45()
    dim i as byte
    dim j as byte
    dim k as byte
    dim l as byte
    dim m as byte
    dim sum as byte
    dim Count as integer

    count=0
    For i=1 to i=1+22
    sum=Val(cells(i,1))
    for j=1 to j=1+22
    sum=Val(cells(i,1))+Val(cells(j,2))
    if sum =< 45 then
    for k=1 to K=1+22
    sum=Val(cells(i,1))+Val(cells(j,2))+Val(Cells(k,3))
    if sum<45 then
    for l=1 to l=1+11
    sum=Val(cells(i,1))+Val(cells(j,2))+Val(Cells(k,3))+Val(Cells(l,4))
    if sum<45 then
    for m=1 to m=1+11
    sum=Val(cells(i,1))+Val(cells(j,2))+Val(Cells(k,3))+Val(Cells(l,4))+Val(cells(m,5))
    if sum = 45 then count= count+1
    next m
    end if
    next l
    end if
    nexr k
    end if
    next j
    next i
    end sub

    I hope I could help you,
    Pierre
    Thanks for your reply, blank cells are at 0 so I think this will work. Can you send a formula, and what cell(s) should it go in?

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com