Formula to show lowest price of heading

L

Leogarvey98

New Member
Joined
Jul 10, 2002
Messages
9
sorry - bad title (didn't know how to word it), but I really need help with this:

I have a row of prices, each in a column with the heading of the shop the price is from - is there a formula that can automatically show which shop is the cheapest?

here's what I mean:

http://garvey98l.pwp.blueyonder.co.uk/shops.xls

so that in cell I3, it would automatically come up with shop 3, rather than me having to type it.

Any help would be greatly appreciated :)

Leo
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
Hi Leogarvey98:
y031226h1.xls
ABCDEFGHI
1TitleShop1Shop2Shop3Shop4Shop5Shop6Cheapest
2Item3.996.492.895.993.994.99Shop3
Sheet8


The formula in cell I3 is ...=INDEX(B1:G1,MATCH(MIN(B2:G2),B2:G2,0))
 
Upvote 0
Cheers :biggrin: , I just added the $ sign before the co-ords of the headings (so I can do multiple rows), and it works a treat :)

Thnx again, Leo
 
Upvote 0
Hi Leo:

Good idea to make the row part absolute as necessary, if you are going to use the formula in different sets of rows.
 
Upvote 0
Suppose we have the following data...
shops.xls
ABCDEFG
1TitleShop1Shop2Shop3Shop4Shop5Shop6
2Item13.993.492.895.993.992.89
3Item23.12.53.923.262.53.04
4Item32.021.812.912.641.813.79
5Item43.773.774.83.44.113.57
6
Shops


Shops will try to be competitive. So they will sell a certain item for the same price as the competition. If that price is the cheapest, a regular Index/Match formula based on Min will give you the first matching shop and nothing more. For example, Item1 in the above exhibit is cheapest at Shop3 and Shop6, yet a regular Index/Match formula cannot but list Shop3 only. The problem is in fact a special case of the Top N class of problems. It can therefore be solved with my recently developed set of formulas for the Top N problems (as can be seen in the next post).
 
Upvote 0
Aladin Akyurek said:
... The [cheapest shop] problem is in fact a special case of the Top N class of problems. It can therefore be solved with my recently developed set of formulas for the Top N problems (as can be seen in the next post).
Click to expand...

The exhibit below shows required computations in columns consecutive to data shown in the exhibit of the previous post. This area can be, if so desired, cut and pasted into another sheet.
shops.xls
HIJKLMNOPQRSTU
1RankCheapest
243165211Shop3Shop6    
341652311Shop2Shop5    
431542611Shop2Shop5    
534615201Shop4     
6
Shops


Formulas...

H2, which is copied to M2...

=RANK(B2,$B2:$G2,1)+COUNTIF($B2:B2,B2)-1

N2:

=MAX(IF(INDEX(B2:G2,MATCH($O2,H2:M2,0))=B2:G2,H2:M2))-$O2

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

O2 houses 1, a value which indicates Top N (N=1).

P2, which is copied to U2...

=IF(COLUMN()-COLUMN($P2)+1<=$O2+$N2,INDEX($B$1:$G$1,MATCH(COLUMN()-COLUMN($P2)+1,$H2:$M2,0)),"")

Now you can copy H2:U2 down for all items.
 
Upvote 0
Hello

How can you use the same formula to show the lowest price for the same item from data that is in different .xls files.

ex I have a list of UPC#'s and descriptions of items, also I have the shop files with the UPC#'s and descriptions and I want to find which shops stock those items and the price that they sell it, for best price selection .

Can you give me an idea how can this be done

Thank you
John
 
Upvote 0
parsec said:
Hello

How can you use the same formula to show the lowest price for the same item from data that is in different .xls files.

ex I have a list of UPC#'s and descriptions of items, also I have the shop files with the UPC#'s and descriptions and I want to find which shops stock those items and the price that they sell it, for best price selection .

Can you give me an idea how can this be done

Thank you
John
Click to expand...

It seems to me that it would be rather hard to set up an already elaborate set of formulas for things residing in different files. A route to pursue would be to collect the relevant data into a destination sheet where you can then apply the set up I described.
 
Upvote 0
Can you please advice if you mean to have all the shops and their invetory under one sheet or have all in one book and in separate sheets
Thank you
John
 
Upvote 0
parsec said:
Can you please advice if you mean to have all the shops and their invetory under one sheet or have all in one book and in separate sheets
Thank you
John
Click to expand...

One sheet would be more manegable.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
Show the "Edit Name" dialog from VBA for a specific Name
Replies
1
Views
370
Jaafar Tribak
Jaafar Tribak
R
  • Question
Excel Formula for showing a picture.
Replies
1
Views
275
Anfinsen
A
F
  • Question
Calculation when 3 conditions are met
Replies
8
Views
408
Fidbeck
F
A
  • Question
Should I use VBA and/or make my formulas more efficient?
Replies
0
Views
961
addms
A
S
  • Question
Overdue dates based on Status
Replies
3
Views
555
spuri78
S

Forum statistics

Threads
1,215,094
Messages
6,123,071
Members
449,092
Latest member
ipruravindra

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top