How to do the calculation - 29 Nov?

[Eric]

Does anyone have any suggestions on how to do the calculation?
There is a list of raw data under column A
50, 52, 53, 53, 55, 56
There is a list of numbers under column B
3%, 9%, 5%, 4%, 8%, 8%

I would like to create a matrix to count the frequency
On X-axis, 50, 51, 52, 53, 54, 55, 56
On Y-axis, 0%, 1%, 2%, 3%, 4%, 5%, 6%, 7%, 8%, 9%

Does anyone have any suggestions on how to set the formula to determine the
frequency? For example, 4% & 5& will count 1 for each for the number 53.
Thanks in advance for any suggestions
Eric

 

[Lars-Åke Aspelin]

Does anyone have any suggestions on how to do the calculation?
There is a list of raw data under column A
50, 52, 53, 53, 55, 56
There is a list of numbers under column B
3%, 9%, 5%, 4%, 8%, 8%

I would like to create a matrix to count the frequency
On X-axis, 50, 51, 52, 53, 54, 55, 56
On Y-axis, 0%, 1%, 2%, 3%, 4%, 5%, 6%, 7%, 8%, 9%

Does anyone have any suggestions on how to set the formula to determine the
frequency? For example, 4% & 5& will count 1 for each for the number 53.
Thanks in advance for any suggestions
Eric

Assuming that your "X-axis" is on row 1, from column D to J
and your "Y-axis" is in column C, from row 2 row 11
you cab try the following formula in cell D2

=SUMPRODUCT(($A$1:$A$6=D$1)*($B$1:$B$6=$C2))

Change the 6 to fi the size of your data in column A and B.

Copy the forumula to the entire range D2 to J11 and you matrix is
ready.

Hope this helps / Lars-Åke

 

[Gary''s Student]

Consider using a Pivot Table. It can give you a list of unique values that
the frequency of occurance:

In A1 thru B31:

raw value
60 8%
58 4%
54 4%
53 6%
60 8%
55 4%
60 1%
57 6%
58 5%
58 9%
53 7%
60 7%
54 2%
59 5%
60 7%
50 2%
52 9%
57 9%
58 2%
56 10%
54 3%
59 1%
60 1%
56 8%
57 8%
52 5%
53 10%
53 5%
51 6%
57 3%


The Table is:Count of value value
raw 1% 10% 2% 3% 4% 5% 6% 7% 8% 9% Grand Total
50 1 1
51 1 1
52 1 1 2
53 1 1 1 1 4
54 1 1 1 3
55 1 1
56 1 1 2
57 1 1 1 1 4
58 1 1 1 1 4
59 1 1 2
60 2 2 2 6
Grand Total 3 2 3 2 3 4 3 3 4 3 30

 

[Shane Devenshire]

Hi,

Set up your matrix like this where 50 is in D1 and 1% is in C2, then the
formula in D2 is
=COUNTIFS($A$1:$A$6,D$1,$B$1:$B$6,$C2) (in 2007)
or
=SUMPRODUCT(--($A$1:$A$6=D$1),--($B$1:$B$6=$C2)) (in 2003)

50 51 52 ....
1% 0 0 0
2% 0 0 0
3% 1 0 0
4% 0 0 0
....

If this helps, please click the Yes button

Cheers,
Shane Devensire