Frequency distribution
I have a worksheet with two columns of data
Column A has contains a reference code, d1, d2, d3....and so on to d6 however, this code can occur more than once. Column B has a random number against the reference codes (could be anything between 1 -100) e.g. d1 5 d2 7 d1 10 d3 1 d1 5 d5 6 d4 20 d4 5 d6 15 d5 6 d1 10 I would like to calculate the number of occurances of each reference code with each number, i.e. the number of d1's with number 5 = 2, the number of d1's with 10 = 1. Can anyone help? Thanks |
Place the following formula in an adjacent cell, substituting the ranges to
match yours =COUNT(IF(A18&B18=A18:A23&B18:B23,B18:B23,"")) A18 represents the adjacent reference code, b18 represents the adjacent number. a18:a23 is the full range of reference codes, which you should enter in absolute format, whilst b18:b23 is the range of numbers which also should be entered in absolute format. Finally, this formula needs to be entered as an array, meaning, press cntrl-shift-enter after typing it in. http://HelpExcel.com "Ms MIS" wrote: I have a worksheet with two columns of data Column A has contains a reference code, d1, d2, d3....and so on to d6 however, this code can occur more than once. Column B has a random number against the reference codes (could be anything between 1 -100) e.g. d1 5 d2 7 d1 10 d3 1 d1 5 d5 6 d4 20 d4 5 d6 15 d5 6 d1 10 I would like to calculate the number of occurances of each reference code with each number, i.e. the number of d1's with number 5 = 2, the number of d1's with 10 = 1. Can anyone help? Thanks |
All times are GMT +1. The time now is 11:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com