5 Replies Latest reply on Feb 19, 2010 12:34 AM by Macl007

# Finding the second best price ?

### Title

Finding the second best price ?

### Post

Hey,

I'm trying to write a formula that will give me the second best price out of a series of prices. I explain myself.

Let's say that I have 4 fields, Price1, Price2, Price3 and Price4. I would like to a have a 5th field that would give me the second best price out of the 4 prices.

Can anybody help me because I cannot see how to do that ?

Many thanks

• ###### 1. Re: Finding the second best price ?
This - and many other things - will become much easier if you keep the prices as individual records in a related table.
• ###### 2. Re: Finding the second best price ?
I see what you mean but in this case I have to keep it in the same table.
• ###### 3. Re: Finding the second best price ?
Could you explain why?
• ###### 4. Re: Finding the second best price ?

OK, then try:

Let (
all = List ( Price1 ; Price2 ; Price3 ; Price4 )
;
Evaluate ("Min ( " & Substitute ( FilterValues ( all ; Substitute ( ¶ & all & ¶ ; ¶ & Min ( Price1 ; Price2 ; Price3 ; Price4 ) & ¶ ; ¶ ) ) & ¶ ; [ "¶¶" ; ")" ] ; [ ¶ ; " ; " ] )

)

)

• ###### 5. Re: Finding the second best price ?
Just great, thank you very much !!!