How do I calculate the mode of a column of IP addresses?
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! |
How do I calculate the mode of a column of IP addresses?
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)) -- Regards, Dave "Brandon" wrote: 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! |
How do I calculate the mode of a column of IP addresses?
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. -- Regards, Dave "Brandon" wrote: 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! |
How do I calculate the mode of a column of IP addresses?
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! |
How do I calculate the mode of a column of IP addresses?
In article ,
says... 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 |
All times are GMT +1. The time now is 06:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com