Conditional formula to show cheaper price

[dpbarry]

Hi all..

I'm looking at trying to set up a spreadsheet that will compare prices between 3 computer suppliers. (all supplier calculation on same spreadsheet)

Basically, what I want it to do is, after I enter in the prices for a product into each suppliers column, I want to change the colour of the cell (to red) that has the cheapest price for that particular product so that I can see what I have to order from each supplier that has the best price.

Is that possible?

Regards

Declan Barry

 

[dpbarry]

Still having a few problems. Here is a snap of my spreadsheet.

I just need to compare the price between each supplier and hilight cheapest

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

 

[onlyadrafter]

Hello,

I think I may have cracked it using CONDITIONAL FORMATTING

If values are in A1, B1 and C1

The condition is

CELL VALUE IS equal to =IF($A1=0,MIN($B1:$C1),IF($B1=0,MIN($A1,$C1),IF($C1=0,MIN($A1:$B1),MIN($A1:$C1))))

use the format painter to format other cells.

 

[dpbarry]

Hi onlyadrafter.

The spreadsheet is as above (using PRICE as the conditional values)

Regards

Declan

 

[onlyadrafter]

Hello,

In that case

change A1 to E5, B1 to H4 and C1 to K4, and put this condition in cell E4, use the format painter to format cells H4 and K4 then format the other rows.

Here, I've done it for you and made some other changes i.e. : to ,

=IF($E4=0,MIN($H4,$K4),IF($H4=0,MIN($E4,$K4),IF($K4=0,MIN($E4,$H4),MIN($E4,$H4,$K4))))

Working OK?

 

[dpbarry]

I must be thick cus I can't get it to work. I've changed the layout slightly to make things more clearer.

Hope this helps with setting up the conditional formatting.

Is ther a way of filling down the conditional formatting?

Regards

Declan

Toner Cartridges Master.xls
	A	B	C	D	E	F	G
2					Supplies Team	Micro warehouse	Canon
3	Description	Colour	OrderCode	Yield	Price	Price	Price
4	HP4500	Black	C4191A		35.00	45.00	75.10
5		Cyan	C4192A		52.52	12.00	20.00
6		Yellow	C4193A		52.51	0.00	0.00
7		Magenta	C4194A		52.51	0.00	0.00
8	HP4100	Black	C8016	10k	51.81	0.00	0.00
9	HP4000	Black	C8061X	10k	51.81	0.00	0.00
10	HP4050	Black	C4127X	10k	51.81	0.00	0.00
11	HP2100	Black	C4127X	10k	40.71	0.00	0.00
12	HP5MP/6MP	Black	C3903A		35.89	0.00	0.00
Sheet1

 

[Aladin Akyurek]

I must be thick cus I can't get it to work. I've changed the layout slightly to make things more clearer.

Hope this helps with setting up the conditional formatting.

Is ther a way of filling down the conditional formatting?...

Remove the coloring in E3:G12 first.

Select E4:G12.
Activate Format|Conditional Formatting.
Choose Formula Is for Condition 1.
Enter the following in the white box:

=E4=SMALL($E4:$G4,COUNTIF($E4:$G4,0)+1)

Activate the Format button.
Choose a color from the Patterns tab.
Click OK, OK.

 

[eliW]

Clever! Aladin,

But how can you do it on non- contiguous ranges as was put at first??

Regards,

Eli

 

[Aladin Akyurek]

...

But how can you do it on non- contiguous ranges as was put at first??...

Exploit the regularity of spacing between the price columns in order to compute the non-zero minimum price per row in an additional column...

Book2
	A	B	C	D	E	F	G	H	I	J
1
2	SuppliesTeam			Microwarehouse			Canon
3	Price	Quantity	Sub-Total	Price	Quantity	Sub-Total	Price	Quantity	Sub-Total	MinPrice
4	36.18	2		23	2		75.1	2		23
5	52.52	2		12	2		0	2		12
6	52.51	2		0	2			2		52.51
7	52.51	2		0	2			2		52.51
8
Sheet2

The formula in J4, which is copied down, is:

=MIN(IF((MOD(COLUMN(A4:I4)-CELL("Col",A4)+0,3)=0)*A4:I4,A4:I4))

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

Now select the ranges of interest one by one and use a formula for example for the A-range...

=A4=$J4

and so on for the other price ranges.

 

[dpbarry]

Hi Aladin

Many many thanks to one and all for the input. All is now working fine.

To Aladin..

Could you explain in laymans terms, what exactly the conditional formatting equation is doing. I don't fully understand it. All I know is, it works but would like to know a bit more about what it is doing

Just as a matter of interest, is there a way of now getting it to multiply a quantity by the cheapest price?

Regards

Declan

 

[dpbarry]

Hi Aladin

Many many thanks to one and all for the input. All is now working fine.

To Aladin..

Could you explain in laymans terms, what exactly the conditional formatting equation is doing. I don't fully understand it. All I know is, it works but would like to know a bit more about what it is doing

Just as a matter of interest, is there a way of now getting it to multiply a quantity by the cheapest price?

Regards

Declan