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

IP Address Analysis

M

megatron77

New Member
Hi there, this is my first post on this forum so apologies if the explanation of my issue isn't particularly good. If you require clarification let me know.

I am a web analyst and currently I am doing some analysis of IP Addresses that visit our website - the primary reason is to identify the IP ranges of bots that Google, Yahoo etc are using so that we can filter their sessions out of our reporting. I export a a list of 10,000 IP addresses from the database and have then built a dashboard to identify the highest bouncing IP addresses, view their total sessions etc.

Once these have been identified I now want to create a list of known IP's so that I can monitor their activity before adding them to a block list. The issue that I have is that Google can own an IP range and send any bots from that range to our site e.g. 66.196.0.0 - 66.249.255.255

This causes an issue because I want to be able to SUM the number of sessions from IP's within this range but I am unsure how excel handles these types of numbers. I have tried doing a SUMIFS using "<=" and ">=" on the start and end of the range but it isn't quite working for me because I think Excel identifys the IP 66.249.34.0 as a higher number than 66.249.255.255 when in fact the latter is the higher value in terms of IP's.

I have also tried using the LEFT function to narrow the IP down to 66.196 and then applied a wildcard function *, then done the same for the upper limit to create 66.249* but my SUMIFS formula also isn't working in this instance.

Any help would be greatly appreciated.

Josh
 

Attachments

Hi Josh ,

You are right that Excel will put 66.249.34.0 as a higher number than 66.249.255.255 ; since there is more than one period in the address , Excel treats it as a text string.

When comparing two text strings , Excel proceeds from left to right ; this after the 66.249. match in both strings , 3 is higher than 2 ; Excel does not know that 34 is an octet , and 255 is an octet , and that the comparison of the two IP addresses has to be done octet-wise.

Why don't you split up the IP address into its 4 constituents and compare them from left to right individually ?

Narayan
 
Hi Narayan,

That sounds like a good idea, my first thought is to use text to columns however this isn't going to work because every monday morning I want to paste in 10,000 rows of data and have formula's return each octet and also retain the original IP address. And of course each octet can range from 1 - 3 digits long.

How would you go about splitting the IP?

Josh
 
Narayan,

Thanks for the MID, LEFT and RIGHT functions.

However I am now having a further issue, and I think it might be something to do with numbers stored as text but I could be wrong.

I can't do a SUMIF based on the results of the MID, LEFT and RIGHT functions?

Josh
 
Hi Josh ,

Use COUNTIF instead of SUMIF ; if you explain what you wish to do , it will make it easier to suggest the appropriate formula.

Narayan
 
Hi Narayan,

I want to be able to sum the number of sessions that falls between two IP ranges based on one of the octets of the IP address but the previous octets need to also be criteria.

For example sum the values between 195.0.0.0 and 195.255.255.255. The octets have been split as per your mid, left and right functions.

I thought SUMPRODUCT would do it but I couldn't get it to work for me. :-(

Does that make sense? I've attached a sample file.

Thanks

Josh
 

Attachments

Just a bit more info to that, I need it to sum values that are greater than or equal to the first IP and less than or equal to the second IP.
 
So I took your formula, pasted it into my main workbook seeing as the formats are exactly the same and the only thing I did was change the ranges from 13 down to 10000 and I'm getting a #VALUE! error. Any ideas?
 
There were #VALUE error in the MID, LEFT and RIGHT functions, I've added IFERROR formulas to them and that seems to have solved the #VALUE problem.
 
Some of the values that I am working with need to be Averaged instead of Summed. Do you know a way to do an "AVERAGEPRODUCT" sort of formula?

I've tried SUMPRODUCT/COUNTIFS but receiving a #DIV/0 error.

Thanks

Josh
 
Hi Josh ,

Can you not try either the AVERAGEIF / AVERAGEIFS functions or the AVERAGE(IF( combination ?

Can you say which field needs to be averaged ?

Narayan
 
You must log in or register to reply here.
Back
Top