[TriLUG] openoffice question

[sholton at mindspring.com]

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 <rick.denatale at gmail.com>
>Sent: Jan 3, 2006 2:02 PM
>To: Triangle Linux Users Group discussion list <trilug at trilug.org>
>Subject: Re: [TriLUG] openoffice question
>
>On 1/3/06, Ryan Leathers <ryan.leathers at globalknowledge.com> 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/


-- 
sholton at mindspring.com
Innovation is a wildflower. You cannot choose where it will blossom; you can only choose where it will not.