Frequency of associated numbers in a table

**telcotech** · 03-08-2009, 01:08 PM

Hi,

I am looking to find the frequency of times 1 number appears with another number in a row in a table

Click here for an example

or see the attached spreadsheet. I included the excel 2007 and 2003 versions.

Thanks

**shg** · 03-08-2009, 01:15 PM

Looks to me that the table at the top IS the table you describe. If not, please complete a portion of the lower table manually and explain the logic you used.

**telcotech** · 03-08-2009, 01:36 PM

Originally Posted by shg

Looks to me that the table at the top IS the table you describe. If not, please complete a portion of the lower table manually and explain the logic you used.

Yes, the table at the bottom is the data table, i put some examples in the top table, the frequency table, I want to populate the top table;

each cell will contain the number of times the numbers in column A appear in the rows of the data table with the numbers in row 6 of the top table.

**shg** · 03-08-2009, 01:39 PM

OK, but then the first two rows of the lower table are not consistent with the first two rows of the upper table. Or if they are, please explain how.

**telcotech** · 03-08-2009, 02:59 PM

Originally Posted by shg

OK, but then the first two rows of the lower table are not consistent with the first two rows of the upper table. Or if they are, please explain how.

row 8 in the upper table, cell A2 = 2, looking at the data table below, the number 2 appears with the values in row 6 along the top of the upper table that many times, the value of A7=1 it appears 0 times with the value of B6(1), 1 time with the value of C6(2), 0 times with the value of D6(3), 0 times with the value of E6(4), 0 times with the value of F6(5), 0 times with the value of G6(6) and 1 time with the value of H6(7)

The upper table is the frequency in which each number in column A appears with each number in row 6 in the lower table, the data table, the frequency table is looking at the data table to determine the number of times the combination of column A and row 6 numbers appear together. I did not put all the available numbers to compare in the frequency table column A and row 6, the numbers 1-7 are there for example purposes.

**daddylonglegs** · 03-08-2009, 03:43 PM

I assume that each number can only appear a maximum of once per row

There might be an easier way but......

You can use this formula in B7 copied across and down

=IF(B$6=$A7,"",SUM(IF(FREQUENCY(IF($B$16:$G$22=B$6,ROW($A$16:$A$22)-ROW($A$16)+1),ROW(INDIRECT("1:"&ROWS($B$16:$G$22))))+FREQUENCY(IF($B$16:$G$22=$A7,ROW($A$16:$A$22)-ROW($A$16)+1),ROW(INDIRECT("1:"&ROWS($B$16:$G$22))))=2,1)))

This is an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar, see attached.

I custom formatted the upper table as

0;;

which means that zeroes won't display