Need Way to Sum 100 Categories (97)

Topic

New Reply

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 10×10 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

Reply | Quote

Replies

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

Reply | Quote

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:

=SUM(($A3 = $A$16:$A$6532) * (B$2=$B$16:$B$6532))

(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.

Reply | Quote

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

Reply | Quote