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

 

[Ekim]

John,

Alternative procedure for your consideration.

Using Aladin’s raw data, with slight amendments: green cells – lowest prices; orange cells – highest prices.

Cheapest shop when more than one.xls
	A	B	C	D	E	F	G	H	I
1	Title	Shop1	Shop2	Shop3	Shop4	Shop5	Shop6
2	Item1	5.99	3.49	2.89	5.99	3.99	2.89
3	Item2	3.10	2.50	3.92	3.26	2.50	3.04
4	Item3	2.02	1.81	2.91	2.64	1.81	1.81
5	Item4	3.77	3.77	4.80	3.40	4.11	4.80
6
7
8	Pricing:	Lowest
9
10		No.of	Lowest
11	Title	Stores	Price	Shops
12	Item1	2	2.89	Shop3	Shop6	#N/A	#N/A
13	Item2	2	2.50	Shop2	Shop5	#N/A	#N/A
14	Item3	3	1.81	Shop2	Shop5	Shop6	#N/A
15	Item4	1	3.40	Shop4	#N/A	#N/A	#N/A
16
Alternative (2)

B8:
Validation dropdown (Data menu | Validation)
List
Source: “Highest,Lowest” (no quotes)

B12:
=COUNTIF(B2:G2,C12)
Copy down to B15.
(this formula simply notes the number of stores with the min/max price – the formula plays no part in any other formula).

C10:
=IF($B$8="Lowest","Lowest","Highest")

C12:
=IF($B$8="Lowest",MIN(B2:G2),MAX(B2:G2))
Copy down to C15.

D12:
=INDEX($B$1:$G$1,MATCH(1,($B2:$G2=$C12)*(COUNTIF($C12:C12,$B$1:$G$1)= 0),0))
Array entered – i.e. Shift-Ctrl-Enter
Copy to G12.
Then select D12:G12 and copy down to D15:G15.

To eliminate the #NA error message - alternatives:

1. Use conditional formatting (CF):

Select D12
Format menu | Conditional Formatting
Formula is: =ERROR.TYPE(D12)=7
Format: white font
OK

Copy the CF in D12 to E12:G15 and D13:D15 using the Paintbrush tool.

2. Wrap the formula in D12 inside an IF statement:

=IF(ISNA(formula))),"",formula))

i.e.
=IF(ISNA(INDEX($B$1:$G$1,MATCH(1,($B2:$G2=$C18)*(COUNTIF($C18:C18,$B$1:$G$1)=0),0))),"",INDEX($B$1:$G$1,MATCH(1,($B2:$G2=$C18)*(COUNTIF($C18:C18,$B$1:$G$1)=0),0)))

After using the IF statement, you still have to array enter the formula (then copy it to the rest of the range).

Regards,

Mike

 

[Aladin Akyurek]

John,

Alternative procedure for your consideration...

Why, is it any faster?

 

[parsec]

First of all Thank you for all the help and ideas
I am learning quite a bit from all this. But still I have an issue how to implement those formulas in an data that I get from my vendors(shops) that first some have over 15000 items the only common ID is number and at the same time I get revisions of the pricing every month, I was considering as before to have all data in one sheet but this appears to create a big issue as to manage that data on a month to month basis, I think that if I keep my vendor files separate I can manage them better but then I have lost the way to have a price comparison. Ideally will be to have all those files as they send to me by my vendors and If I need to do a price comparison to to enter an item # or list of items #'s and then get the best pricing in my workbook.

Thank you all and Happy and Healthy New Year
John

 

[Ekim]

Why, is it any faster?

Why not, is it any slower?

 

[Aladin Akyurek]

Why, is it any faster?

Why not, is it any slower?

All.xls
	A	B	C	D	E	F	G	H	I	J
1	FastExcelWorkBookProfile
2
3	WorkBookSummary
4			CalcTime(Millisec)		%	Book	MicroSecs	Range	Total	TBytes
5		Formula	ReCalc	FullCalc	Volatile	OvHead	/Formula	Mem(K)	Mem(K)	perCell
6	Original	377	0.2	11.5	1.9%	7.7	30.4	25	27	43.5
7	Alternative	241	0.2	18.2	1.1%	6.6	75.3	29	29	60.9
8
Sheet1

The original is twice as fast as the alternative, while its volatilty score remains bearable (due to COLUMN() primarily, which is used for robustness). The figures are averages of 8 separate runs. The number items to compute for 29, that of shops 6. If you add either conditional formatting or ISNA as you suggested, the scores for the alternative will worsen.

 

[dpbarry]

Still having a few problems with the conditional format. Sometimes it works, sometimes it doesn't. Here is a snap of my page.

Toner Cartridges.xls
	E	F	G	H	I	J	K	L	M
2	SuppliesTeam			Microwarehouse			Canon
3	Price	Quantity	Sub-Total	Price	Quantity	Sub-Total	Price	Quantity	Sub-Total
4	36.18	2	72.36	23.00	2	46.00	75.10	2	150.20
5	52.52	2	105.04	12.00	2	24.00	0.00	2	0.00
6	52.51	2	105.02	0.00	2	0.00		2	0.00
7	52.51	2	105.02	0.00	2	0.00		2	0.00
Sheet1

[/img]