Formula to show lowest price of heading

[Leogarvey98]

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

 

[Yogi Anand]

Hi Leogarvey98:

y031226h1.xls
	A	B	C	D	E	F	G	H	I
1	Title	Shop1	Shop2	Shop3	Shop4	Shop5	Shop6		Cheapest
2	Item	3.99	6.49	2.89	5.99	3.99	4.99		Shop3
Sheet8

The formula in cell I3 is ...=INDEX(B1:G1,MATCH(MIN(B2:G2),B2:G2,0))

 

[Leogarvey98]

Cheers , 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

 

[Yogi Anand]

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.

 

[Aladin Akyurek]

Suppose we have the following data...

shops.xls
	A	B	C	D	E	F	G
1	Title	Shop1	Shop2	Shop3	Shop4	Shop5	Shop6
2	Item1	3.99	3.49	2.89	5.99	3.99	2.89
3	Item2	3.1	2.5	3.92	3.26	2.5	3.04
4	Item3	2.02	1.81	2.91	2.64	1.81	3.79
5	Item4	3.77	3.77	4.8	3.4	4.11	3.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).

 

[Aladin Akyurek]

... 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).

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
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Rank								Cheapest
2	4	3	1	6	5	2	1	1	Shop3	Shop6
3	4	1	6	5	2	3	1	1	Shop2	Shop5
4	3	1	5	4	2	6	1	1	Shop2	Shop5
5	3	4	6	1	5	2	0	1	Shop4
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.

 

[parsec]

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

 

[Aladin Akyurek]

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

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.

 

[parsec]

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

 

[Aladin Akyurek]

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

One sheet would be more manegable.