Finding the biggest value in a custom list

[HosteDenis]

Does anyone know how to do this? I know how to make customs lists, but it's not getting me anywhere.

Let's say I have a lot of data containing values as follows and in the following list I sorted them from small to large:

5a
5a+
5b
5b+
5c
5c+
6a
6a+
6b
6b+
6c
6c+
7a
7a+
7b
7b+
7c
7c+
8a

and so on... In the list, the date isn't sorted from small to large, many values (if not all) occur multiple times and on occasion some values are added to the column. With that last part I mean that you can see it as a batch of 500, and if so far the batch only contains 450 values there will be (for the time being until items get added) 50 empty cells in the column as well.

You can't order numerically or alphabetically. I know I can make a custom list and then just sort according to that custom list so the biggest value would come out on top or bottom but that's not what I'm looking for either, as the data needs to be sort by another colomn (dates in chronological order).

So I'm just looking for a formule saying "return the biggest value if the order of things is as I typed above'.

Thanks for any help, I appreciate it!

Denis

[Ron Coderre]

With your sample data in A1:A19

This regular formula returns the "largest" value from that list:

Please Login or Register  to view this content.

For the sample data, that formula returns: 8a
Is that something you can work with?

[HosteDenis]

Excel says my formula contains an error. It looks exactly like this now:

Please Login or Register  to view this content.

[Ron Coderre]

In your scenario, the formula should be accepted...but, it will probably return an incorrect value. I'm guessing it's a "Mac Excel" issue.
Try this regular formula:

Please Login or Register  to view this content.

Does that help?

[HosteDenis]

I'll check it later (on my phone now) but I thought excel said the error was in the first C117:C742, and that'll probably be the same with the new formula. Forgot to mention that in my previous post, sorry. Could the error be in the fact that some of the cells in that range are empty? Also, almost all values (inclusing the biggest) occur several times in the list.

So far no cigar but still, thanks for the help!

[HosteDenis]

No, Excel is saying the same thing. The new formula contains an error and excel highlights the C742 part. But that cell is the last (empty) cell of a column that contains these values.

[martindwilson]

what is your regional list separator is it ; or ,

[HosteDenis]

Not entirely sure what you mean by that, but if I type the number 4.67 in excel, I have to type it as 4,67 so excel considers it a number (and it moves to the right of the cell). If I use the . or ; character, excel considers it a string.

So I'm guessing ; is my regional list separator?

[Jacc]

I think what Martin is after is that maybe all the comma's in Ron's formula really should be ;. Could it be that simple?
Hit Ctrl + h to use the Find and Replace feature.

[HosteDenis]

It is now no longer giving me an error, but the cell does display '#NAME' now.

Please Login or Register  to view this content.

[martindwilson]

what language is your excel in? that formula doesnt give me a #name error

[HosteDenis]

Language as in the language I run excel in, or as in the language excel uses to run?

To answer the first, that would be Dutch. So technically it's giving me a '#NAAM' error instead of a '#NAME' error. To answer the second, I have no idea, I haven't installed any add-ons or messed with any code, I'm not that good at programming, but since's it's a Microsoft office program I guess it's written in C, C++, and C#?

[martindwilson]

so did you put the functions in Dutch?
=INDEX(C117:C742;VERGELIJKEN(MAX(INDEX(AANTAL.ALS(C117:C742;"<"&C117:C742);0));INDEX(AANTAL.ALS(C117:C742;"<"&C117:C742);0);0))

[HosteDenis]

example file.xlsx

here's what I have

[HosteDenis]

so did you put the functions in Dutch?
=INDEX(C117:C742;VERGELIJKEN(MAX(INDEX(AANTAL.ALS(C117:C742;"<"&C117:C742);0));INDEX(AANTAL.ALS(C117:C742;"<"&C117:C742);0);0))

Oh I see you just replied. I'll try that (could it really be that simple?) although I seem to remember I used to have worked with english functions in the past...

[HosteDenis]

Yes, it's giving me 7b now, which is the largest value!

Thanks a lot for all the help guys, this is really good stuff. Quick replies and nice people, if I ever need help again with excel I'll come back!

[HosteDenis]

Just one more question, say if I wanted to return the biggest value, also taking in mind another value in another column (for example next to the three columns I gave in my example file, there'd be a fourth column saying small, medium, large for example) how would I have to alter the formula? How would it return the biggest value from all values that are medium, for example?

[martindwilson]

in my excel it gives a value of 7b

[HosteDenis]

In my file, it does now too. I had to translate the functions like you did, but I had already replied before I saw your post. Thanks a lot for the help. Just one more question, say if I wanted to return the biggest value, also taking in mind another value in another column (for example next to the three columns I gave in my example file, there'd be a fourth column saying small, medium, large for example) how would I have to alter the formula? How would it return the biggest value from all values that are medium, for example?

[HosteDenis]

Can't seem to find what to alter in the original formula. But so far your help's been great!

[HosteDenis]

Can anyone help?

[Ron Coderre]

With
C117:C742 containing your list to rank
and
D117:D742 containing a category in: Large, Medium, Small
and
B1: a value to match in Col_D....Medium

I don't know the Dutch translations...but this ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)
returns the largest Col_C value where the Col_D value matches B1 (works in the English version):

Please Login or Register  to view this content.

Slightly longer regular (non-array) formula:

Please Login or Register  to view this content.

Is that something you can work with?

[HosteDenis]

I used the following formula (based on your second formula):

Please Login or Register  to view this content.

Take in mind that 'countif' is 'aantal.als' in dutch and 'if' is 'als' and 'match' is 'vergelijken' and also that my list separator is ; and not a comma. The value I want in B1 is 'trad', but there might be more info in the cell other than 'trad' so I should look for '*trad*', right?

Well, the formula doesn't give me an error, but it's not returning the correct value either... I'll give an example file.

[HosteDenis]

Here is my example file:

example file I need help with.xlsx

So the formula i used is your second one, not the array one. I have worked and am working with array formula's right now, so I know what they do and how they work, but I haven't used the IFERROR function before so I preferred your second option.

[martindwilson]

handy translator here
http://en.excel-translator.de/

[Ron Coderre]

Only certain functions recognize wildcards.

Try this regular formula in your workbook...(changing commas to semicolons and replacing english functions with dutch):

Please Login or Register  to view this content.

Is that better?

[HosteDenis]

That does seem to work in my example file, I'll try it in the real file with the different column- and row-numbers now but it should work! Thanks a lot!

Really great help here on this forum. If it works I'll change to title to SOLVED!

For those interested:

Please Login or Register  to view this content.

EDIT: it works guys, thanks a lot! Changed title to Solved