Finding second price

[Guest]

I have a spreedsheet which I get pricing from up to 10 vendors. Using the Min function Iconditionally format and highlight the lowest minium price for any one item.
My question is how do I write a formula to determine the second best price?

 

[Chip Pearson]

You can use the LARGE function to get the Nth largest item in a
list. Similarly, you can use the SMALL function to get the Nth
smallest item in a list. For example, to return the second
largest item, use

=LARGE(A1:A10,2)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

I have a spreedsheet which I get pricing from up to 10 vendors.

Using the Min function Iconditionally format and highlight the
lowest minium price for any one item.

My question is how do I write a formula to determine the second

best price?

 

[Aladin Akyurek]

Let A2:B12 house the following data:

{"Vendor","Price";
"THV",62.4;
"KVA",75.6;
"ZJU",83.7;
"OCV",78.6;
"OKF",60.7;
"KYJ",71;
"SRM",64.3;
"QSV",62.4;
"EEV",89.1;
"PVM",67.2}

Note that A2:B2 houses the relevant labels.

In C2 enter: D_Rank [ a label, derived from Descending Rank]

In C3 enter & copy down.

=RANK(B3,$B$3:$B$12,1)+COUNTIF(B3:$B$3,B3)-1

In F2 enter:

=MAX(IF(INDEX(B3:B12,MATCH(G2,C3:C12,0))=B3:B12,C3:C12))-G2

which must be confirmed with control+shift+enter instead of just with enter.

In G2 enter: 2 [a parameter indicating N best/lowest/top prices]

In F3 enter: Vendor

In G3 enter: Price

In F4 enter & copy down:

=IF(G4<>"",INDEX($A$3:$A$12,MATCH(ROW()-ROW($F$4)+1,$C$3:$C$12,0)),"")

In G4 enter & copy down:

=IF(ROW()-ROW($G$4)+1<=$G$2+$F$2,INDEX($B$3:$B$12,MATCH(ROW()-ROW($G$4)+1,$C
$3:$C$12,0)),"")

Note that the ROW($F$4) and ROW($G$4) bits are references to cells where
these formulas start.

A2:C12 looks now like this:

{"Vendor","Price","D_Rank";
"THV",62.4,2;
"KVA",75.6,7;
"ZJU",83.7,9;
"OCV",78.6,8;
"OKF",60.7,1;
"KYJ",71,6;
"SRM",64.3,4;
"QSV",62.4,3;
"EEV",89.1,10;
"PVM",67.2,5}

and F2:Gx like this:

{1,2;
"Vendor","Best Price";
"OKF",60.7;
"THV",62.4;
"QSV",62.4;
"","";"",""}

As can be noted, the results area lists 3 vendors and 3 lowest prices while
2 are asked for. The reason for this is that the Nth (the 2nd) place is a
tie. The above scheme allows you to retrieve such tied values.

I have a spreedsheet which I get pricing from up to 10 vendors. Using the

Min function Iconditionally format and highlight the lowest minium price for
any one item.

My question is how do I write a formula to determine the second best

price?