Price Ranking
 

Hi there,

Can someone help me with a query. I need to give a ranking based on price in
certain city.

Exp:
A B C D
1 Item 1 LON 100.00
2 Item 2 MAD 80.00
3 Item 3 BCN 80.00
4 Item 4 LON 80.00
5 Item 5 LON 120.00
6 Item 6 BCN 85.00

Basically in column D, it should give me the ranking based on the cheaper
rate based on the same criteria on column B. The answer for above should be

2
1 (cheapest in MAD)
1 (cheapest in BCN)
1 (cheapest in LON)
3
2

Looking forward for your reply.

Thanks.
Kim

Price Ranking
 

=MIN(IF(B1:B6="MAD",C1:C6,""))
=MIN(IF(B1:B6="BCN",C1:C6,""))
=MIN(IF(B1:B6="LON",C1:C6,""))


These are array formulas that must be entered with CNTRL-SHFT-ENTER rathr
than just the ENTER key.
--
Gary''s Student - gsnu200851


"Kim" wrote:

Hi there,

Can someone help me with a query. I need to give a ranking based on price in
certain city.

Exp:
A B C D
1 Item 1 LON 100.00
2 Item 2 MAD 80.00
3 Item 3 BCN 80.00
4 Item 4 LON 80.00
5 Item 5 LON 120.00
6 Item 6 BCN 85.00

Basically in column D, it should give me the ranking based on the cheaper
rate based on the same criteria on column B. The answer for above should be

2
1 (cheapest in MAD)
1 (cheapest in BCN)
1 (cheapest in LON)
3
2

Looking forward for your reply.

Thanks.
Kim

Price Ranking
 

Kim,

For your example set, enter this in D1 and copy down:

=SUMPRODUCT(($B$1:$B$6=B1)*($C$1:$C$6<C1))+1

To get fancy, use

=IF(SUMPRODUCT(($B$1:$B$6=B1)*($C$1:$C$6<C1))+1=1, "1 (cheapest in " & B1 &
")",SUMPRODUCT(($B$1:$B$6=B1)*($C$1:$C$6<C1))+ 1)

HTH,
Bernie
MS Excel MVP


"Kim" wrote in message
...
Hi there,

Can someone help me with a query. I need to give a ranking based on price in
certain city.

Exp:
A B C D
1 Item 1 LON 100.00
2 Item 2 MAD 80.00
3 Item 3 BCN 80.00
4 Item 4 LON 80.00
5 Item 5 LON 120.00
6 Item 6 BCN 85.00

Basically in column D, it should give me the ranking based on the cheaper
rate based on the same criteria on column B. The answer for above should be

2
1 (cheapest in MAD)
1 (cheapest in BCN)
1 (cheapest in LON)
3
2

Looking forward for your reply.

Thanks.
Kim