• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

FREQUENCY

LesEwing

New Member
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

New Member
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
 
Top