formula for binary to decimial not working right

[richsark]

Hello All, I honestly forget who helped me with this back in Dec 08, I tried to look for my thread, but I cant find it.

Anyway, this formula was working great since 12-08, but someone came to me yesterday saying there is a flaw in the formula between Company A and Company B

If you note that 10.125.239.0/24 is overlapped with 10.125.236.0/22, yet is marked FALSE

FindDuplicateIPRanges12-26-08-v7-Sorted.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
7221	CompanyA	10.125.236.0	255.255.252.0	3	7	11	4	8	12	176024576	1023	176025599	TRUE
7222	CompanyA	10.125.237.0	255.255.255.0	3	7	11	4	8	12	176024832	255	176025087	TRUE
7223	CompanyA	10.125.239.0	255.255.255.0	3	7	11	4	8	12	176025344	255	176025599	FALSE
7224	CompanyB	10.125.240.0	255.255.255.0	3	7	11	4	8	12	176025600	255	176025855	TRUE
7225	CompanyB	10.125.240.0	255.255.255.252	3	7	11	4	8	12	176025600	3	176025603	TRUE
DottedDecimalMasks

Can someone please look at the formula to see where the calculation is flawed.

Thanks

 

[Bill_Biggs]

I have been looking this over and I am wondering about this formula. Have you done these manually (with a calculator) and seen if you are getting what you want for sure. I know Excel works on the MDAS principle, but are they actually calculating what you want?

=MID(IP_Address,1,_1stDotIP-1)*256*256*256+MID(IP_Address,_1stDotIP+1,_2ndDotIP-_1stDotIP-1)*256*256+MID(IP_Address,_2ndDotIP+1,_3rdDotIP-_2ndDotIP-1)*256+MID(IP_Address,_3rdDotIP+1,LEN(IP_Address)-_3rdDotIP)

If so, I am at a loss. I wish I could be better help.

 

[richsark]

HI Bill, I think the issue is in the column M

FindDuplicateIPRanges-1.xls
	M	N	O	P
2	FALSE
DottedDecimalMasks

where the true and false are set. I think it fails for overlapping subnets that were not adjacent to each other. I am not that savvy to prove it, but perhaps thats the cause.

There is a possibility that it could miss in the other direction as well.

Thanks