Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Need Way to Sum 100 Categories (97)

    I want to use Excel to generate data for a Chi-Square problem. Specifically, I want to generate a large series of pairs of random integers between 1-10 in columns A and B. I can do that with =RANDBETWEEN(1,10). So I would have pairs of values like 1,8 and 5,9. There would be 100 possible pairs from 1,1 to 10,10. I know how to do all that. Now, I want to construct a 10x10 grid so that each cell in the grid represents one of the 100 possible pairs. The part I don't know how to do is to calculate a count for the grid. For example, the cell in the grid representing 5,5 should look through these say 10,000 pairs and count how many are 5,5.

    I know I can do this with a brute-force approach by writting 100 IF statements beside my pairs of random numbers where each one has a value of 1 for membership in a particular cell and 0 otherwise. I could copy that down and then total each column but that would take a huge worksheet and would not be a very nice approach.

    It seems to me that I should be able to do this with 100 COUNTIF statements but I don't have a lot of experience with Excell database functions and cannot get it to work properly. Any suggestions?


    Ronny
    Ronny Richardson

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Need Way to Sum 100 Categories (97)

    Ronny,

    If you are up to a little VBA the following code will work.
    '-----------------------------------------------------------
    'Random numbers entered into Range("A2:B11") - 2 x 10 grid
    'Count of pairs will show in Range("D2:M11") - 10 x 10 grid

    Sub AddCountToTable()
    Dim RandNum1 As Long
    Dim RandNum2 As Long
    Dim Rng As Range

    Range("D2:M11").ClearContents
    For Each Rng In Range("A2:A11")
    RandNum1 = Rng.Value
    RandNum2 = Rng(1, 2).Value
    With Cells(RandNum1 + 1, RandNum2 + 3)
    .Value = .Value + 1
    End With
    Next
    Set Rng = Nothing
    End Sub
    '-----------------------------------------------------------
    The code should be adjusted if you change the location of the tables.

    Regards,

    Jim Cone
    San Francisco, CA

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need Way to Sum 100 Categories (97)

    With data pairs in the range A16:B6532, and the values 1 to 10 in A3:A12 and in B2:K2 The following formula, entered as an array formula (ctrl-shift-enter to enter) will return the number of instances where the first number in a given pair matches the value in column A of that row, and the second number in a pair matches the value in row B for that column:
    <pre>=SUM(($A3 = $A$16:$A$6532) * (B$2=$B$16:$B$6532))
    </pre>

    (this was the formula in cell B3) Copy this formula to the range B3:K12, after adjusting for the correct data references - I just copied the randbetween an arbitrary number of rows. This should be faster than the VBA code posted to do the same things, since it uses Excel's built-in functions, which are almost always faster than VBA.

    I have attached the s/sheet to help if you get mixed up.
    Attached Files Attached Files

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Re: Need Way to Sum 100 Categories (97)

    Dean,

    I started with the Visual Basic approach suggested by Jim but this was so much easier and nicer. It worked like a champ and I was able to easily modify it to fit a few specific requirements I did not mention. Thanks a bunch!

    I also learned something unexpected. I've been using spreadsheets snice Lotus 1-2-3 was king and Lotus did not allow spaces in the formulas. I got in the habit of not using spaces and just always assumed that Excel did not allow them either. I saw the spaces in the formula you posted and just assumed you added them to make the posting easier to read but when I downloaded the worksheet you uploaded, it had them in the functional formulas as well. So, I experimented and saw that you can use them in Excel. That was a great, and unexpected, find.

    Thanks
    Ronny
    Ronny Richardson

Posting Permissions

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