Well, okay, to take this out a step farther.... If cell A1 contains an integer in the range [ 0 - ((2^40)-1) ] (basically the valid integer ranges for an IP address) then this formula in cell B1 will hold the IP address in dotted quad format:
=CONCATENATE(TEXT(MOD(INT(A1/POWER(2;32));POWER(2;8));0);".";TEXT(MOD(INT(A1/POWER(2;16));POWER(2;8));0);".";TEXT(MOD(INT(A1/POWER(2;8));POWER(2;8));0);".";TEXT(MOD(INT(A1/POWER(2;0));POWER(2;8));0)) Not optimized, of course, for clarity. Note that cell A1 is referenced four times in the above formula. To get the integer value for IP A.B.C.D use: ( A * (2^32) )+( B * ( 2^16) )+( C * ( 2^8) ) + D so for our favorite 192.168.1.2 we get ( 192 * (2^32) )+( 168 * ( 2^16) )+( 1 * ( 2^8) ) + 2 = 824644731138 Ask me sometime about the "Binary-to-7-segment decoder" I implemented as an Excel spread sheet one boring winter day.... -----Original Message----- >From: Rick DeNatale <[EMAIL PROTECTED]> >Sent: Jan 3, 2006 2:02 PM >To: Triangle Linux Users Group discussion list <[email protected]> >Subject: Re: [TriLUG] openoffice question > >On 1/3/06, Ryan Leathers <[EMAIL PROTECTED]> wrote: >> Mark, >> >> I've learned a couple of tricks, but none are as elegantly simple as the >> excel alternative. > >Hmmm, I've never seen this in excel. Does it really understand ip dot >notation? Does it properly handle the sequence: > >192.168.0.254 >192.168.0.255 >192.168.1.0 > >or does it end up at 192.168.0.256. > >IP address dot notation is a kind of pseudo base 256 notation. >-- >Rick DeNatale > >Visit the Project Mercury Wiki Site >http://www.mercuryspacecraft.com/ >-- >TriLUG mailing list : http://www.trilug.org/mailman/listinfo/trilug >TriLUG Organizational FAQ : http://trilug.org/faq/ >TriLUG Member Services FAQ : http://members.trilug.org/services_faq/ -- [EMAIL PROTECTED] Innovation is a wildflower. You cannot choose where it will blossom; you can only choose where it will not. -- TriLUG mailing list : http://www.trilug.org/mailman/listinfo/trilug TriLUG Organizational FAQ : http://trilug.org/faq/ TriLUG Member Services FAQ : http://members.trilug.org/services_faq/
