FREQUENCY

[LesEwing]

Hello All, I have a column I10:I10000 that has a series of numbers e.g 1,1,0,2,0,1,1,1,0,2,2,0,0,0 followed by empty cells which will eventually be filled with similiar numbers. This formula works to find the largest frequency of '1's (=MAX(FREQUENCY(IF(I10:I10000=1,ROW(I10:I10000)),IF(I10:I10000<>1,ROW(I10:I10000)))) followed by Ctl+Shift+Enter. Which the answer is three here, but the reverse to find the largest frequency of numbers <>1 just counts the blank cells.e.g. =MAX(FREQUENCY(IF(I10:I10000<>1,ROW(I10:I10000)),IF(I10:I10000=1,ROW(I10:I10000))))Ctl+Shift+Enter.

I've tried quite a few adaptions like <>"" etc but nothing is working. I can get it sort of 'working' but it counts wrong by +1 or correct but then wrong again !!

The first formula works brilliantly. Any ideas anyone ? I need a Guru !!

Cheers and Thanks.

Les ♪♫♪♫

 

[ericlind]

Might just be easier if you Advanced Filter the column for unique values and use those as your reference points for your frequency formula to calculate against (minus the MAX formula).


I also wonder if you're getting no results because MAX was included in your frequency formula. MAX, by default, is going to look for the largest value in the array, so if you exclude it I think you may also have better results.


Col-1 | Col-2 | Col-3

1 | 0 | 3 > =frequency(a:a,b$1:b$3) [& ctrl+shift+enter]

2 | 1 | 4 > =frequency(a:a,b$1:b$3) [& ctrl+shift+enter]

0 | 2 | 2 > =frequency(a:a,b$1:b$3) [& ctrl+shift+enter]

2

1

0

2

1

1

0