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.
chrisk said:
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?
