Error Checking 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?

Some videos you may like

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
  •