Max Value with 2 decimels in the Data

[DavinderAnand]

Hi Community
I have a data which lists Hardware firmware versions. In the columns i have different versions listed. If the vresions is 2.3 or 3.55 with one decimel, i can get the results with =Max(c2,C3).

But as you know the firmware versions are not simple. I have data like 2.0.7, 3.5.6.1 etc and i want the max value from all data fields, but it returns 0, because of the Dta type. I have tried formatting it to Text, Number or even custom, but no luck.

Is there a way to resove this issue with Formula as i dont want to go thrigh rows to get the highest versions.

thanks

Dav

[benishiryo]

hi Dav, welcome to the forum. assuming 2.0.7 in C2 & 3.5.6.1 in C3, try:
=IF(SUBSTITUTE(C2,".","")&REPT(0,4-LEN(SUBSTITUTE(C2,".","")))>SUBSTITUTE(C3,".","")&REPT(0,4-LEN(SUBSTITUTE(C3,".",""))),C2,C3)

but what is the maximum numbers the version can go to? Change the ones in red if it's more than 4

[:) Sixthsense :)]

May be this...

Array Formula - Requires Ctrl+Shift+Enter

Please Login or Register  to view this content.

Adjust the range A1:A5 to your range

[DavinderAnand]

Hi Benishiryo
Thanks for the reply.

I have around 20 columns of the versions to the Max value from and there are multiple rows.
So i want to use the formula not for just C2 and C3 but for multiple columns.

I think your reply compared the C3 and C4 only. Can the formula be Dynamic to get th MAx value from multiple columns of a row?

thanks
Dav

[DavinderAnand]

Sixthsense
Thanks, It works fine.

Champion.:-)

[DavinderAnand]

Sixthsense
The output is not as the highest value format, i.e. if the highest value is 3.1.9 it appears as 3.19.

Can this be fixed?

[benishiryo]

maybe this array formula then:

Please Login or Register  to view this content.

do mark it as "Solved" if that's it

[:) Sixthsense :)]

Array Formula - Requires CTRL+SHIFT+ENTER

Please Login or Register  to view this content.

[:) Sixthsense :)]

Try this...

Array Formula - Requires CTRL+SHIFT+ENTER

Please Login or Register  to view this content.

[DavinderAnand]

Sixthsense
Thanks, it works. Honestly i dont really undetsand the complex formula, but i am Glad thi is what i needed