Fomula for selecting text

[KarlB]

I have a row of variables showing different values in text, 800 x 600 or 1024 x 767. I'm trying to come up with a formula that will always show the max value present. MAX doesn't work as they are text values. I've used the formula shown wich works fine if I just use any side individually but combining just gives an error. I also need it to return 0 if no values present.

=IF(D4+OR(E4)+OR(F4)+OR(G4)+OR(K4),"800 x 600",0)+IF(H4+OR(I4)+OR(J4)+OR(L4)+OR(M4),"1024 X 767",0)

[contaminated]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

Doing this will ensure you get the result you need!

[KarlB]

But if I need a cell to show the highest value in a range but it's not strictly a number as Excel sees it is that possible ?

[martindwilson]

that formula doesnt make any sense OR requires 2 or more values
=or(a1="x",b1="y") then if either is true OR =true
as contaminated said post a workbook.

[KarlB]

workbook attached its the only formula showing error

[contaminated]

Lets imagine that's in range A1:C1 you have number, but they stored as text. Like below

Please Login or Register  to view this content.

You need to find MAX value

Simple MAx(A1:D1) will return zero. bur =MAX(INDEX(--(A1:D1),0)) will return desired result.
I just didn't understood what is in E4. F4, K4 and so on..

[KarlB]

but if you have a text string like 800 x600 or 1024 x 767 rathe that 254 or 255 that formula doesnt work, well for me at least

[contaminated]

Maybe this... but I'm not sure

=IF(COUNTIF(D19:M19,"800 x 600")>COUNTIF(D19:M19,"1024 x 767"),"800 x 600","1024 x 767")

[KarlB]

thanks, nearly there I think. It only seems to select 1024x767 when other values there if M19 shows it. Also need to get it to 0 if no values present. By the way thanks for the help. Think maybe as it does it as a count. What I'm trying to achieve is selecting the larger of those two variables dependant or what is present no matter of quantity

[martindwilson]

make a list in ascending order as on sheet 3 then look up the max

[KarlB]

is there no way to show highest value in the row based on the first set of numbers in the string

[martindwilson]

why? the vlook up does whats required. then again
=MAX(INDEX(--LEFT(D19:M19,FIND("x",D19:M19)-1),0)) might work for you

[KarlB]

Vlook doesnt really work unless all variables present. I don't want it to depend on the quantity of variables present just on which one is largest.

[martindwilson]

yes it does if there is nothing or 0 it will return 0, the column a sheet 3 will populate resolutions are added to sheet 1 there are only a few standard screen resolutions anyway
800x600
1024X768
1152x864
1280x600
1280x720
1280x768
1440X900

[KarlB]

Ok, thanks, I figured out how it works. But it works on quantities in terms of which number is the max. But I'm trying not to make it dependant on how many pcs require that resolution but rather making the cell equal the highest spec regardless of quantity

[martindwilson]

no it just indicates if its in the list and just puts the row number for max to find ,there is no counting of anything as such exept for =if(countif(d19:n19,b2),row(),""), part any value greater than 0 =true and none found = false
{wrapded it up in an if means =if( countif,"what to do if true","what to do if false")}
so if an instance is there it will put the row number if not blank
same as putting
=if(countif(d19:n19,b2)>0,"do something","do something else if its not")
you could use match instead
if(isnumber(match(b2,d19:n19,0),row(),"")

[KarlB]

Thanks, but just not working out for me and slowly loosing the will here. Thanks for help anyway

[martindwilson]

so what doesnt work in the sheet i gave you?

[KarlB]

Well, looks like I've saved over the version you posted so you're formula not there. But even when it was depending on what was clicked in Row 4 it the answer didnt result didnt always work out. Try it yourself and see. Click off all except column M, i think that might highlight the problem or at least just check the answer as you click the boxes and you can see what I mean.

[martindwilson]

ak i didnt see the check boxes try it now but you will need all the availiable sizes

[KarlB]

Thanks, perfect, just have one addition if possible and thats to show 0 if neither condition fulfilled. Is it as simple as addin ,0 after FALSE

[martindwilson]

doh change vlookup to
=VLOOKUP(MAX(Sheet3!A1:A14),Sheet3!A1:B14,2,FALSE)
i forgot to include a1 in the range

[KarlB]

Thanks a lot dude, not exactly sure how it works but it's finally sorted, thanks for your time. Much appreciated