# 2ndMax Custom Function

This topic is 6989 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

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?

##### Share on other sites

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 )

##### Share on other sites

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.

##### Share on other sites

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

aaa.zip

##### Share on other sites

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.

##### Share on other sites

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.

##### Share on other sites

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.

##### Share on other sites

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

##### Share on other sites

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).

##### Share on other sites

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.

OK, Thanks all.

##### Share on other sites

This topic is 6989 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account