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 running over this for a while now and the error makes no sense to me either. I did find this set of posting however. Was this your initial conversation?

http://www.mrexcel.com/forum/showthread.php?t=355038&page=2

 

[richsark]

Hello Bill,

Its not this one. It originally started with this. As I recall the formula did not work the way I wanted it.

Then someone else wrote a new logic.( I think, meaning I dont know if it was the same user)

Is there a way to find out why its not working right? and or why did it mark it as "False"

Thanks

 

[Bill_Biggs]

Look at it this way:

Excel Workbook
	E	F	G	H	I	J	K	L	M
7216	For Equation to be True	L7223	>	J7224	OR	J7223		L7222	*
7217	Actual	176025599		176025600	FALSE	176025344	>	176025087	FALSE
Sheet1

It is supposed to be false in this circumstance. Both halves of the equation are false in this case, making for a false. Does that make sense? It just now dawned on me.

 

[richsark]

Right, the calculation is off. according to the way subnets/networks work, that should be considered duplicate that

10.125.239.0/24 is overlapped with 10.125.236.0/22

I need the Equation fixed to justify this senerio

 

[richsark]

If you goto this link

http://www.subnet-calculator.com/subnet.php?net_class=A

and Enter 10.125.236.0
Subnet mask: 255.255.252.0

You can see that the hosts per subnet is 1022
Available networks are 10.125.236.1 - 10.125.239.254

It clearly shows that it overlaps

On the spreadsheet Column K shows 1023, I think the calculation is off by a bit

 

[Bill_Biggs]

My apologies, I did not dig deeply enough to understand the extent of the problem. I'll dig a little deeper this afternoon and see if I can help.

 

[richsark]

Great !

Look forward in solving this mystery !

 

[richsark]

Hi Bill, any clues?

Anyone else have an idea?

 

[Bill_Biggs]

Sent you a private message.