# 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?

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).

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

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

Thanks.Your formulas as well as works.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1