# Finding the Value with Highest Frequency in a Range

1. ## Finding the Value with Highest Frequency in a Range

I have a range of numbers on column "A" where some of numbers have been repeated several times. My question is:" How can I find the value more than zero with highest frequency in a range?

I tried below formulas but it does not work truly (sometimes gives me true value and sometimes gives me wrong value). On the other hand, I need the value more than zero with highest frequency.

=INDEX(B:B, 10000*MOD(MAX(COUNTIF(B2:B16,B2:B16)+(ROW(B2:B16)/10000)),1), 1)

=INDEX(B2:B20;MODE(MATCH(B2:B20;B2:B20;0)))

Do you have any solution for my problem?  Register To Reply

2. ## Re: Finding the Value with Highest Frequency in a Range

Hi,

Array formula**:

=MODE(IF(B2:B20,B2:B20))

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).  Register To Reply

3. ## Re: Finding the Value with Highest Frequency in a Range

=INDEX(B2:B20,MODE(IF(B2:B20<>0,MATCH(B2:B20,B2:B20,0))))

array formula
use ctrl + shift + enter  Register To Reply

4. ## Re: Finding the Value with Highest Frequency in a Range

Thanks.Your formulas as well as works.  Register To Reply