Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help: Need Excel formula to return correct price from price history table | Excel Discussion (Misc queries) | |||
Price List overall price increase | Excel Discussion (Misc queries) | |||
base price list compute on to another price list? on excel work sh | Excel Discussion (Misc queries) | |||
calculate/convert volume price to monthly average price | Excel Worksheet Functions | |||
create a formula for price * discount* tax =final price | Excel Discussion (Misc queries) |