How do I calculate the mode of a column of IP addresses?

[Brandon]

I've got a column of IP addresses in another worksheet. I want to find the
most common IP address, so I use this formula: =MODE('18th'!C:C) where "18th"
is the worksheet and C is the column for which I want the mode.

The result is #NUM!

 

[Tushar Mehta]

I've got a column of IP addresses in another worksheet. I want to find the
most common IP address, so I use this formula: =MODE('18th'!C:C) where "18th"
is the worksheet and C is the column for which I want the mode.

The result is #NUM!

IP addresses are a *string* of numbers and MODE ignores them.

Consider creating a PivotTable and using the 'Top {n}' option with n set to
1:

create the PT with the IP header as the row field and the data field. XL
will automatically create a COUNT(IP header) as the data field.

Double-click the row field header. Click Advanced... Set the 'Top 10
AutoShow' to 'On' and set the number next to Show Top to 1.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[David Billigmeier]

It is because MODE() only works on numerical values. Try this for a range of
strings (like your IP addresses), replace "rng" with your range:

=INDEX(rng,MATCH(MAX(COUNTIF(rng,rng)),COUNTIF(rng,rng),0))

 

[David Billigmeier]

One caveat I forgot to include in my previous post, you need to enter that as
an array formula, so commit it with CTRL+SHIFT+ENTER.

 

[Brandon]

Thanks, all, for the helpful information. I was able to get it to work,
though will need to put it elsewhere and then finesse it for my summaries. I
must say, when what I want to do is show the most-common string, and the
2nd-most-common, and so on ... that's a lot of hoops to jump through!

Much appreciation for your assistance here; with this working my work is
much simpler.

Thanks!

 

[Tushar Mehta]

Thanks, all, for the helpful information. I was able to get it to work,
though will need to put it elsewhere and then finesse it for my summaries. I
must say, when what I want to do is show the most-common string, and the
2nd-most-common, and so on ... that's a lot of hoops to jump through!

Not sure why a PivotTable would be "lots of hoops to jump through." All you
would do is select Top {n} where n is a number you provide. Same effort for
Top 1 as for Top 10.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions