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