2ndMax Custom Function

[davidp]

I'm looking for a simple solution (who isn't?)...

I want to create a 2ndMax function similar to the built-in Max(field1,field2,field3,...) function, but it would return the 2nd highest value. For example, 2ndMax(height,width,depth) would return 12 if height=20, width=12 and depth=8.

Any suggestions?

[comment]

I have posted a formula, edited it, and now I am removing it altogether.

You need to specify what should happen when the values are:

a) 20 ; 12 ; 12

: 20 ; 20 ; 12

c) 20 ; 20 ; 20

If it's really only 3 values, and you always want the second one in their ordered position, you could use:

h + w + d - Max ( h ; w ; d ) - Min ( h ; w ; d )

[davidp]

Thanks for the response. The number of values is not always 3 (unfortunately, because I like the cleverness of your solution). There will be anywhere from 3 to 17 values, and it doesn't matter if there are duplicate values. I have a solution that involves the use of a separate table where each field gets put into its own record, the table gets sorted and then I pull out the 2nd value. I was looking for a different way. It's not that important (since I do have the alternative), so please do not go out of your way to figure this out.

[aaa]

Use valuelistitems function. See attachment.Or i dont understand your wish?

aaa.zip

[comment]

it doesn't matter if there are duplicate values

But it does matter, because one needs to know how to handle such situation. For example, consider a contest where there is a tie between 2 winners. The results table will look like this:

PLACE....SCORE

1........20

1........20

3........17

So here you have 3 possible answers to 'what is the second best score': it could be 20, it could be 17, and it could be "there's no one in the second place". The basic approach is entirely different in each case.

[davidp]

Here are the outcomes I'd like:

2ndMax(20,20,17,10,5) = 20

2ndMax(5,5,5) = 5

2ndMax(1,2,3,4,5) = 4

2ndMax(5) =

The problem with valuelists is (I believe) it doesn't account for duplicates as it would produce the outcome of 17 in the 1st example above instead of 20 which is the desired outcome.

[RalphL]

You can overcome the problem with duplicates in a value list using a calculated field = value & " " & Serial number. This will make them unique. It does require a little extra work.

[comment]

Ralph points in the right direction, IMHO, but it requires more than a little extra work. The concatenation

value & " " & Serial number

is text, and will be sorted by text rules (1, 11, 2 ,21, etc.). Numbers must be processed into text strings in order to be sorted in the desired order. This can get quite complex, even slow, if one needs to anticipate the full range of numeric input.

The attached demo uses a "light" version of pre-processing, dealing with zeros and positive numbers in the range of 10^-9 to 10^9, exclusive.

2ndMax.fp7.zip

[davidp]

On second thought, I don't think I can use value lists since I'm looking for the 2nd Max among several fields within a single record, rather than across records (like the Max(field1,field2,field3...) function).

[comment]

If the number of items is variable, and can go up to 17, and the items need to be compared - they really should go into a separate table with a record per item.

[davidp]

OK, Thanks all.