software

Hi All,

Is there any way to 'evaluate' a dotted number (similar to an IPv4
address but not just those) using standard worksheet formulae (ie no
UDFs, VBA, or ATP functions)?

To make it a little more complicated, I need to be able to evaluate
not just IP addresses (although that would be a use) but also a
'number' such as 9.0.2 compared to 8.7.5 so that they can be sorted
and / or compared.

I thought it looked easy, and perhaps it is, but I am getting nowhere
at the moment.

Thanks,
--

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:
This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvbDefine 'evaluate'.

Biff

quot;Alanquot; gt; wrote in message
...
gt;
gt; Hi All,
gt;
gt; Is there any way to 'evaluate' a dotted number (similar to an IPv4 address
gt; but not just those) using standard worksheet formulae (ie no UDFs, VBA, or
gt; ATP functions)?
gt;
gt; To make it a little more complicated, I need to be able to evaluate not
gt; just IP addresses (although that would be a use) but also a 'number' such
gt; as 9.0.2 compared to 8.7.5 so that they can be sorted and / or compared.
gt;
gt; I thought it looked easy, and perhaps it is, but I am getting nowhere at
gt; the moment.
gt;
gt; Thanks,
gt; --
gt;
gt; The views expressed are my own, and not those of my employer or anyone
gt; else associated with me.
gt;
gt; My current valid email address is:
gt;
gt;
gt;
gt; This is valid as is. It is not munged, or altered at all.
gt;
gt; It will be valid for AT LEAST one month from the date of this post.
gt;
gt; If you are trying to contact me after that time,
gt; it MAY still be valid, but may also have been
gt; deactivated due to spam. If so, and you want
gt; to contact me by email, try searching for a
gt; more recent post by me to find my current
gt; email address.
gt;
gt; The following is a (probably!) totally unique
gt; and meaningless string of characters that you
gt; can use to find posts by me in a search engine:
gt;
gt; ewygchvboocno43vb674b6nq46tvb
gt;
gt;
gt;
gt;


quot;Biffquot; gt; wrote in message
...
gt; Define 'evaluate'.
gt;
gt; Biff
gt;Hi Biff,

The objective is to be able to sort and compare two numbers such that
I can return the 'greater' number. This would be used in checking
version numbers so:

9.0.5 is 'greater than' 8.7.4

Evaluate can mean anything that allows that comparison to be done -
whatever meets the objective.

Does that explain enough? Post back if I am not being clear and I
will give some more examples (if that will help).

Thanks,

Alan.
--

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:
This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb
gt;The objective is to be able to sort and compare two numbers such that I can
gt;return the 'greater' number. This would be used in checking version
gt;numbers so:
gt;9.0.5 is 'greater than' 8.7.4

That's a fairly simple example to work with but I'm betting things get a lot
more complicated!

For something like the above you could use the Substitute function to get
rid of the dots:

=--SUBSTITUTE(A1,quot;.quot;,quot;quot;)gt;--SUBSTITUTE(B1,quot;.quot;,quot;quot;)

=MAX(--SUBSTITUTE(A1,quot;.quot;,quot;quot;),--SUBSTITUTE(B1,quot;.quot;,quot;quot;))

=SUMPRODUCT(MAX(SUBSTITUTE(A1:B1,quot;.quot;,quot;quot;)))

Biff

quot;Alanquot; gt; wrote in message
...
gt;
gt;
gt; quot;Biffquot; gt; wrote in message
gt; ...
gt;gt; Define 'evaluate'.
gt;gt;
gt;gt; Biff
gt;gt;
gt;
gt;
gt; Hi Biff,
gt;
gt; The objective is to be able to sort and compare two numbers such that I
gt; can return the 'greater' number. This would be used in checking version
gt; numbers so:
gt;
gt; 9.0.5 is 'greater than' 8.7.4
gt;
gt; Evaluate can mean anything that allows that comparison to be done -
gt; whatever meets the objective.
gt;
gt; Does that explain enough? Post back if I am not being clear and I will
gt; give some more examples (if that will help).
gt;
gt; Thanks,
gt;
gt; Alan.
gt; --
gt;
gt; The views expressed are my own, and not those of my employer or anyone
gt; else associated with me.
gt;
gt; My current valid email address is:
gt;
gt;
gt;
gt; This is valid as is. It is not munged, or altered at all.
gt;
gt; It will be valid for AT LEAST one month from the date of this post.
gt;
gt; If you are trying to contact me after that time,
gt; it MAY still be valid, but may also have been
gt; deactivated due to spam. If so, and you want
gt; to contact me by email, try searching for a
gt; more recent post by me to find my current
gt; email address.
gt;
gt; The following is a (probably!) totally unique
gt; and meaningless string of characters that you
gt; can use to find posts by me in a search engine:
gt;
gt; ewygchvboocno43vb674b6nq46tvb
gt;
gt;


quot;Biffquot; gt; wrote in message
...
gt; gt;The objective is to be able to sort and compare two numbers such
gt; gt;that I can return the 'greater' number. This would be used in
gt; gt;checking version numbers so:
gt;gt;9.0.5 is 'greater than' 8.7.4
gt;
gt; That's a fairly simple example to work with but I'm betting things
gt; get a lot more complicated!
gt;
gt; For something like the above you could use the Substitute function
gt; to get rid of the dots:
gt;
gt; =--SUBSTITUTE(A1,quot;.quot;,quot;quot;)gt;--SUBSTITUTE(B1,quot;.quot;,quot;quot;)
gt;
gt; =MAX(--SUBSTITUTE(A1,quot;.quot;,quot;quot;),--SUBSTITUTE(B1,quot;.quot;,quot;quot;))
gt;
gt; =SUMPRODUCT(MAX(SUBSTITUTE(A1:B1,quot;.quot;,quot;quot;)))
gt;
gt; Biff
gt;
gt; quot;Alanquot; gt; wrote in message
gt; ...
gt;gt;
gt;gt;
gt;gt; quot;Biffquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; Define 'evaluate'.
gt;gt;gt;
gt;gt;gt; Biff
gt;gt;gt;
gt;gt;
gt;gt;
gt;gt; Hi Biff,
gt;gt;
gt;gt; The objective is to be able to sort and compare two numbers such
gt;gt; that I can return the 'greater' number. This would be used in
gt;gt; checking version numbers so:
gt;gt;
gt;gt; 9.0.5 is 'greater than' 8.7.4
gt;gt;
gt;gt; Evaluate can mean anything that allows that comparison to be done -
gt;gt; whatever meets the objective.
gt;gt;
gt;gt; Does that explain enough? Post back if I am not being clear and I
gt;gt; will give some more examples (if that will help).
gt;gt;
gt;gt; Thanks,
gt;gt;
gt;gt; Alan.
gt;gt; --
gt;gt;
gt;gt; The views expressed are my own, and not those of my employer or
gt;gt; anyone
gt;gt; else associated with me.
gt;gt;
gt;gt; My current valid email address is:
gt;gt;
gt;gt;
gt;gt;
gt;gt; This is valid as is. It is not munged, or altered at all.
gt;gt;
gt;gt; It will be valid for AT LEAST one month from the date of this post.
gt;gt;
gt;gt; If you are trying to contact me after that time,
gt;gt; it MAY still be valid, but may also have been
gt;gt; deactivated due to spam. If so, and you want
gt;gt; to contact me by email, try searching for a
gt;gt; more recent post by me to find my current
gt;gt; email address.
gt;gt;
gt;gt; The following is a (probably!) totally unique
gt;gt; and meaningless string of characters that you
gt;gt; can use to find posts by me in a search engine:
gt;gt;
gt;gt; ewygchvboocno43vb674b6nq46tvb
gt;gt;
gt;gt;
gt;
gt;

Hi Biff,

That may have legs but doesn't quite work for some examples.

I'll call your formulae above I, II, and III respectively.

I and II both fail for the following example:

A1 = 9.0.5
B1 = 8.17.14

I and III both fail for this example:

A1 = 8.11.4
B1 = 8.17.14However, the general approach seems to have merit. Is there a way to
make each 'section' between dots have a fixed number of character
(doesn't matter how many - let's say 10 to be really safe)?

Thanks,

Alan.
--

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:
This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb
I knew it would be more complicated!

How many dots will there/can there be?

I'm thinking that maybe we can get something to work if we get rid of all
but the first dot:

9.05
8.1714

Biff

quot;Alanquot; gt; wrote in message
...
gt;
gt;
gt; quot;Biffquot; gt; wrote in message
gt; ...
gt;gt; gt;The objective is to be able to sort and compare two numbers such that I
gt;gt; gt;can return the 'greater' number. This would be used in checking version
gt;gt; gt;numbers so:
gt;gt;gt;9.0.5 is 'greater than' 8.7.4
gt;gt;
gt;gt; That's a fairly simple example to work with but I'm betting things get a
gt;gt; lot more complicated!
gt;gt;
gt;gt; For something like the above you could use the Substitute function to get
gt;gt; rid of the dots:
gt;gt;
gt;gt; =--SUBSTITUTE(A1,quot;.quot;,quot;quot;)gt;--SUBSTITUTE(B1,quot;.quot;,quot;quot;)
gt;gt;
gt;gt; =MAX(--SUBSTITUTE(A1,quot;.quot;,quot;quot;),--SUBSTITUTE(B1,quot;.quot;,quot;quot;))
gt;gt;
gt;gt; =SUMPRODUCT(MAX(SUBSTITUTE(A1:B1,quot;.quot;,quot;quot;)))
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;Alanquot; gt; wrote in message
gt;gt; ...
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; quot;Biffquot; gt; wrote in message
gt;gt;gt; ...
gt;gt;gt;gt; Define 'evaluate'.
gt;gt;gt;gt;
gt;gt;gt;gt; Biff
gt;gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; Hi Biff,
gt;gt;gt;
gt;gt;gt; The objective is to be able to sort and compare two numbers such that I
gt;gt;gt; can return the 'greater' number. This would be used in checking version
gt;gt;gt; numbers so:
gt;gt;gt;
gt;gt;gt; 9.0.5 is 'greater than' 8.7.4
gt;gt;gt;
gt;gt;gt; Evaluate can mean anything that allows that comparison to be done -
gt;gt;gt; whatever meets the objective.
gt;gt;gt;
gt;gt;gt; Does that explain enough? Post back if I am not being clear and I will
gt;gt;gt; give some more examples (if that will help).
gt;gt;gt;
gt;gt;gt; Thanks,
gt;gt;gt;
gt;gt;gt; Alan.
gt;gt;gt; --
gt;gt;gt;
gt;gt;gt; The views expressed are my own, and not those of my employer or anyone
gt;gt;gt; else associated with me.
gt;gt;gt;
gt;gt;gt; My current valid email address is:
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; This is valid as is. It is not munged, or altered at all.
gt;gt;gt;
gt;gt;gt; It will be valid for AT LEAST one month from the date of this post.
gt;gt;gt;
gt;gt;gt; If you are trying to contact me after that time,
gt;gt;gt; it MAY still be valid, but may also have been
gt;gt;gt; deactivated due to spam. If so, and you want
gt;gt;gt; to contact me by email, try searching for a
gt;gt;gt; more recent post by me to find my current
gt;gt;gt; email address.
gt;gt;gt;
gt;gt;gt; The following is a (probably!) totally unique
gt;gt;gt; and meaningless string of characters that you
gt;gt;gt; can use to find posts by me in a search engine:
gt;gt;gt;
gt;gt;gt; ewygchvboocno43vb674b6nq46tvb
gt;gt;gt;
gt;gt;gt;
gt;gt;
gt;gt;
gt;
gt; Hi Biff,
gt;
gt; That may have legs but doesn't quite work for some examples.
gt;
gt; I'll call your formulae above I, II, and III respectively.
gt;
gt; I and II both fail for the following example:
gt;
gt; A1 = 9.0.5
gt; B1 = 8.17.14
gt;
gt; I and III both fail for this example:
gt;
gt; A1 = 8.11.4
gt; B1 = 8.17.14
gt;
gt;
gt; However, the general approach seems to have merit. Is there a way to make
gt; each 'section' between dots have a fixed number of character (doesn't
gt; matter how many - let's say 10 to be really safe)?
gt;
gt; Thanks,
gt;
gt; Alan.
gt; --
gt;
gt; The views expressed are my own, and not those of my employer or anyone
gt; else associated with me.
gt;
gt; My current valid email address is:
gt;
gt;
gt;
gt; This is valid as is. It is not munged, or altered at all.
gt;
gt; It will be valid for AT LEAST one month from the date of this post.
gt;
gt; If you are trying to contact me after that time,
gt; it MAY still be valid, but may also have been
gt; deactivated due to spam. If so, and you want
gt; to contact me by email, try searching for a
gt; more recent post by me to find my current
gt; email address.
gt;
gt; The following is a (probably!) totally unique
gt; and meaningless string of characters that you
gt; can use to find posts by me in a search engine:
gt;
gt; ewygchvboocno43vb674b6nq46tvb
gt;
gt;

quot;Biffquot; gt; wrote in message
...
gt;
gt;I knew it would be more complicated!
gt;

{Grin}

gt;
gt; How many dots will there/can there be?
gt;
gt; I'm thinking that maybe we can get something to work if we get rid
gt; of all but the first dot:
gt;
gt; 9.05
gt; 8.1714
gt;
gt; Biff
gt;

I think the maximum I have seen is three dots and minimum is one dot
(never just a numeric). Happy to make that assumption.

Does that help?

What about this though:

8.1.81 is prior to 8.17.1

But if we lose the second dot we would have:

8.181 is before 8.171

which would be hard to cover.

If we could find a way to force all segments to have, say, three
digits it would become trivial:

8.1.81 -gt; 008.001.081 -gt; 008001081

8.17.1 -gt; 008.017.001 gt; 008017001

The final numerics can be compared easily and will always work (we may
have to make it five digits or even ten, but the principle is sound).

Would that be possible somehow?

Thanks for helping me!

Alan.
--

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:
This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvbgt; The final numerics can be compared easily and will always work (we may
gt; have to make it five digits or even ten, but the principle is sound).

If each quot;segmentquot; is padded to a certain number of chars and based on the
concept of converting the string to a numeric number the limitation is 15
digits.

Let me tinker around with this and see if I can come up with something. It's
getting late where I'm at so I may not respond again until tomorrow.

Biff

quot;Alanquot; gt; wrote in message
...
gt;
gt; quot;Biffquot; gt; wrote in message
gt; ...
gt;gt;
gt;gt;I knew it would be more complicated!
gt;gt;
gt;
gt; {Grin}
gt;
gt;gt;
gt;gt; How many dots will there/can there be?
gt;gt;
gt;gt; I'm thinking that maybe we can get something to work if we get rid of all
gt;gt; but the first dot:
gt;gt;
gt;gt; 9.05
gt;gt; 8.1714
gt;gt;
gt;gt; Biff
gt;gt;
gt;
gt; I think the maximum I have seen is three dots and minimum is one dot
gt; (never just a numeric). Happy to make that assumption.
gt;
gt; Does that help?
gt;
gt; What about this though:
gt;
gt; 8.1.81 is prior to 8.17.1
gt;
gt; But if we lose the second dot we would have:
gt;
gt; 8.181 is before 8.171
gt;
gt; which would be hard to cover.
gt;
gt; If we could find a way to force all segments to have, say, three digits it
gt; would become trivial:
gt;
gt; 8.1.81 -gt; 008.001.081 -gt; 008001081
gt;
gt; 8.17.1 -gt; 008.017.001 gt; 008017001
gt;
gt; The final numerics can be compared easily and will always work (we may
gt; have to make it five digits or even ten, but the principle is sound).
gt;
gt; Would that be possible somehow?
gt;
gt; Thanks for helping me!
gt;
gt; Alan.
gt; --
gt;
gt; The views expressed are my own, and not those of my employer or anyone
gt; else associated with me.
gt;
gt; My current valid email address is:
gt;
gt;
gt;
gt; This is valid as is. It is not munged, or altered at all.
gt;
gt; It will be valid for AT LEAST one month from the date of this post.
gt;
gt; If you are trying to contact me after that time,
gt; it MAY still be valid, but may also have been
gt; deactivated due to spam. If so, and you want
gt; to contact me by email, try searching for a
gt; more recent post by me to find my current
gt; email address.
gt;
gt; The following is a (probably!) totally unique
gt; and meaningless string of characters that you
gt; can use to find posts by me in a search engine:
gt;
gt; ewygchvboocno43vb674b6nq46tvb
gt;
gt;
gt;
gt;


quot;Biffquot; gt; wrote in message
...
gt;gt; The final numerics can be compared easily and will always work (we
gt;gt; may have to make it five digits or even ten, but the principle is
gt;gt; sound).
gt;
gt; If each quot;segmentquot; is padded to a certain number of chars and based
gt; on the concept of converting the string to a numeric number the
gt; limitation is 15 digits.
gt;
gt; Let me tinker around with this and see if I can come up with
gt; something. It's getting late where I'm at so I may not respond again
gt; until tomorrow.
gt;
gt; Biff
gt;
gt; quot;Alanquot; gt; wrote in message
gt; ...
gt;gt;
gt;gt; quot;Biffquot; gt; wrote in message
gt;gt; ...
gt;gt;gt;
gt;gt;gt;I knew it would be more complicated!
gt;gt;gt;
gt;gt;
gt;gt; {Grin}
gt;gt;
gt;gt;gt;
gt;gt;gt; How many dots will there/can there be?
gt;gt;gt;
gt;gt;gt; I'm thinking that maybe we can get something to work if we get rid
gt;gt;gt; of all but the first dot:
gt;gt;gt;
gt;gt;gt; 9.05
gt;gt;gt; 8.1714
gt;gt;gt;
gt;gt;gt; Biff
gt;gt;gt;
gt;gt;
gt;gt; I think the maximum I have seen is three dots and minimum is one
gt;gt; dot (never just a numeric). Happy to make that assumption.
gt;gt;
gt;gt; Does that help?
gt;gt;
gt;gt; What about this though:
gt;gt;
gt;gt; 8.1.81 is prior to 8.17.1
gt;gt;
gt;gt; But if we lose the second dot we would have:
gt;gt;
gt;gt; 8.181 is before 8.171
gt;gt;
gt;gt; which would be hard to cover.
gt;gt;
gt;gt; If we could find a way to force all segments to have, say, three
gt;gt; digits it would become trivial:
gt;gt;
gt;gt; 8.1.81 -gt; 008.001.081 -gt; 008001081
gt;gt;
gt;gt; 8.17.1 -gt; 008.017.001 gt; 008017001
gt;gt;
gt;gt; The final numerics can be compared easily and will always work (we
gt;gt; may have to make it five digits or even ten, but the principle is
gt;gt; sound).
gt;gt;
gt;gt; Would that be possible somehow?
gt;gt;
gt;gt; Thanks for helping me!
gt;gt;
gt;gt; Alan.
gt;gt; --
gt;gt;
gt;gt; The views expressed are my own, and not those of my employer or
gt;gt; anyone
gt;gt; else associated with me.
gt;gt;
gt;gt; My current valid email address is:
gt;gt;
gt;gt;
gt;gt;
gt;gt; This is valid as is. It is not munged, or altered at all.
gt;gt;
gt;gt; It will be valid for AT LEAST one month from the date of this post.
gt;gt;
gt;gt; If you are trying to contact me after that time,
gt;gt; it MAY still be valid, but may also have been
gt;gt; deactivated due to spam. If so, and you want
gt;gt; to contact me by email, try searching for a
gt;gt; more recent post by me to find my current
gt;gt; email address.
gt;gt;
gt;gt; The following is a (probably!) totally unique
gt;gt; and meaningless string of characters that you
gt;gt; can use to find posts by me in a search engine:
gt;gt;
gt;gt; ewygchvboocno43vb674b6nq46tvb
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;
gt;

Hi Biff,

No worries - it is getting on here too (NZ).

This is not urgent, it is a big problem that is ongoing so I thought
I'd sort it out, only to find it wasn't as simple as I had figured.

Tomorrow or next week would be fine.

Thanks again for working this through with me.

Alan.
--

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:
This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb
Alan wrote...
....
gt;I think the maximum I have seen is three dots and minimum is one dot
gt;(never just a numeric). Happy to make that assumption.
....
gt;If we could find a way to force all segments to have, say, three
gt;digits it would become trivial:
gt;
gt;8.1.81 -gt; 008.001.081 -gt; 008001081
gt;
gt;8.17.1 -gt; 008.017.001 gt; 008017001

Could any of the segments be 3 or more digits? I'll assume not.

One possibility for numeric encoding involves using a defined name like
seq referring to

=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,64,1))

Then you could convert such a composite value in A1 into a distinct
number using the array formula

=SUM(MID(A1amp;quot;.0.0.0quot;,SMALL(IF(MID(quot;.quot;amp;A1amp;quot;.0.0.0quot;, seq,1)=quot;.quot;,seq),{1;2;3;4}),
SMALL(IF(MID(A1amp;quot;.0.0.0.quot;,seq,1)=quot;.quot;,seq),{1;2;3;4 })-SMALL(IF(MID(quot;.quot;amp;A1amp;quot;.0.0.0quot;,
seq,1)=quot;.quot;,seq),{1;2;3;4}))*100^(4-{1;2;3;4}))