Results 1 to 4 of 4

20021224, 14:10 #1
 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 ChiSquare problem. Specifically, I want to generate a large series of pairs of random integers between 110 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 bruteforce 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?
RonnyRonny Richardson

20021224, 16:03 #2
 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

20021224, 16:45 #3
 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 (ctrlshiftenter 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 builtin functions, which are almost always faster than VBA.
I have attached the s/sheet to help if you get mixed up.

20021226, 14:18 #4
 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 123 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
RonnyRonny Richardson