# Thread: Returning MAX value in Column of Version Numbers

1. Registered User
Join Date
Jan 2003
Location
Nottinghamshire, UK
Posts
364

## Unanswered: Returning MAX value in Column of Version Numbers

Hi Does anyone know how to do this?

I have a Column of Values (Application Version Numbers) in Excel 2000

1.0.1
1.0.2
1.0.3
1.0.4
1.1.0
1.1.1
1.1.2
1.2.0

I want to return maximum value (1.2.0) using something like the =MAXA(A1:A10) function (which does'nt work in this case)

2. Registered User
Join Date
Oct 2003
Posts
1,091
Howdy. As a starting step, you could use helper column. Assume your data starts in A2 and goes to A17. Then in B2 put this formula:

=(MID(A2,LEN(A2)/2+1,1)&RIGHT(A2,1))*1

Copy it down to then end of your data. Define the named range: MyRng to be A2:B17 (or make it dynamic)

Then use this formula in C2

=MAX(B2:B17)

(or make it a dynamic named range)

Then this will give your result (in cell D2)

=INDEX(MyRng,MATCH(C2,\$B\$2:\$B\$17,FALSE),1)

3. Registered User
Join Date
Jan 2003
Location
Nottinghamshire, UK
Posts
364

Nearly got it but not quite - your suggestion did exactly as you said it would but fell over when the version number got to 2.0.1

My Fault for not including it in the first list -

Heres a revised list - hope you can help

1.0.1
1.0.2
1.0.3
1.0.4
1.1.0
1.1.1
1.1.2
1.2.0
2.0.6
2.8.7
2.9.8
2.10.9
2.11.10
2.11.11
2.11.12

4. Registered User
Join Date
Jul 2003
Location
Amsterdam, Nederland
Posts
450

## bit of the same but still different

Hi,

Some what of what shades said.

But bit different. Had once the same prob but then with dir´s
What you can do is to eliminate the dots,
and then just use the max value

hope this help.
cant give you the code because i´ve got open office the dutch version
so the functions are all F.C.U.K.-ed up

5. Registered User
Join Date
Jan 2003
Location
Nottinghamshire, UK
Posts
364
Nice idea marvels but doe'snt work either

Simply Removing the Dots means 1.11.2 ends up higher than 1.2.0

Again sorry this scenario is not in the provided version numbers list

Revised list here

1.0.1
1.0.2
1.0.3
1.6.4
1.9.0
1.11.1
1.11.2
1.2.0
2.0.6
2.8.7
2.9.8
2.10.9
2.11.10
2.11.11
2.11.12

6. Registered User
Join Date
Jul 2003
Location
Amsterdam, Nederland
Posts
450

## Re

Are you going to revise the list every time

OK hows this 1

First look at the highest value before the 1st dot then the 2nd and 3rd as last

&#236;f first number = 2 and there are no others then that the highest value
if 2 or more remain with start number 2 then look at 2nd value etc

See if you can revise your list now
Last edited by Marvels; 03-13-08 at 09:15.

7. Registered User
Join Date
Oct 2003
Posts
1,091
You just need another aspect to handle the change in the first digit.

i.e.

=(LEFT(A2,1)&MID(A2,LEN(A2)/2+1,1)&RIGHT(A2,1))*1

It may need refining also if the digits become 2 (i.e. 21.23.12), so you can use a formula to find the first period, then count left, and last period and count right, etc.

8. Registered User
Join Date
Oct 2003
Posts
1,091
Getting close:

You will need four helper columns (copy down, assume your data is in column A, starting at A2).

Column B (for left item), in B2

=(LEFT(A2,FIND(".",A2)-1))*10000

Column C (for mid item) in C2

=LEFT(RIGHT(A2,LEN(A2)-FIND(".",A2,1)),FIND(".",RIGHT(A2,LEN(A2)-FIND(".",A2,1)))-1)*100

Column D, in D2

=(RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2,".","*",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))))*1

Column E, in E2

=SUM(B2: D2)

copy down. Then in Cell F2,

=MAX(E2:E16)

Now, if you use dynamic named ranges, it will work for any number going down without changing the formulas.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•