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:

ewygchvboocno43vb674b6nq46tvb

Define 'evaluate'.

Biff

"Alan" wrote in message
...

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:

ewygchvboocno43vb674b6nq46tvb

"Biff" wrote in message
...
Define 'evaluate'.

Biff



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

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

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,".","")--SUBSTITUTE(B1,".","")

=MAX(--SUBSTITUTE(A1,".",""),--SUBSTITUTE(B1,".",""))

=SUMPRODUCT(MAX(SUBSTITUTE(A1:B1,".","")))

Biff

"Alan" wrote in message
...


"Biff" wrote in message
...
Define 'evaluate'.

Biff



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

"Biff" wrote in message
...
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

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,".","")--SUBSTITUTE(B1,".","")

=MAX(--SUBSTITUTE(A1,".",""),--SUBSTITUTE(B1,".",""))

=SUMPRODUCT(MAX(SUBSTITUTE(A1:B1,".","")))

Biff

"Alan" wrote in message
...


"Biff" wrote in message
...
Define 'evaluate'.

Biff



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





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.14


However, 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

"Alan" wrote in message
...


"Biff" wrote in message
...
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

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,".","")--SUBSTITUTE(B1,".","")

=MAX(--SUBSTITUTE(A1,".",""),--SUBSTITUTE(B1,".",""))

=SUMPRODUCT(MAX(SUBSTITUTE(A1:B1,".","")))

Biff

"Alan" wrote in message
...


"Biff" wrote in message
...
Define 'evaluate'.

Biff



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





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.14


However, 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

"Biff" wrote in message
...

I knew it would be more complicated!


{Grin}


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


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 - 008.001.081 - 008001081

8.17.1 - 008.017.001 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:

ewygchvboocno43vb674b6nq46tvb

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).

If each "segment" 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

"Alan" wrote in message
...

"Biff" wrote in message
...

I knew it would be more complicated!


{Grin}


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


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 - 008.001.081 - 008001081

8.17.1 - 008.017.001 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:

ewygchvboocno43vb674b6nq46tvb

"Biff" wrote in message
...
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).

If each "segment" 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

"Alan" wrote in message
...

"Biff" wrote in message
...

I knew it would be more complicated!


{Grin}


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


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 - 008.001.081 - 008001081

8.17.1 - 008.017.001 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:

ewygchvboocno43vb674b6nq46tvb







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...
....
I think the maximum I have seen is three dots and minimum is one dot
(never just a numeric). Happy to make that assumption.
....
If we could find a way to force all segments to have, say, three
digits it would become trivial:

8.1.81 - 008.001.081 - 008001081

8.17.1 - 008.017.001 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(A1&".0.0.0",SMALL(IF(MID("."&A1&".0.0.0", seq,1)=".",seq),{1;2;3;4}),
SMALL(IF(MID(A1&".0.0.0.",seq,1)=".",seq),{1;2;3;4 })-SMALL(IF(MID("."&A1&".0.0.0",
seq,1)=".",seq),{1;2;3;4}))*100^(4-{1;2;3;4}))

Very nice!

Much shorter than where I was headed.

Think the multiplier should be changed to 1000.

Biff

"Harlan Grove" wrote in message
oups.com...
Alan wrote...
...
I think the maximum I have seen is three dots and minimum is one dot
(never just a numeric). Happy to make that assumption.
...
If we could find a way to force all segments to have, say, three
digits it would become trivial:

8.1.81 - 008.001.081 - 008001081

8.17.1 - 008.017.001 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(A1&".0.0.0",SMALL(IF(MID("."&A1&".0.0.0", seq,1)=".",seq),{1;2;3;4}),
SMALL(IF(MID(A1&".0.0.0.",seq,1)=".",seq),{1;2;3;4 })-SMALL(IF(MID("."&A1&".0.0.0",
seq,1)=".",seq),{1;2;3;4}))*100^(4-{1;2;3;4}))