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

 

[onlyadrafter]

Hello,

One way is to have another column with =MIN(A1:C1)

Then in A1 use conditional formatting

CELL VALUE IS equal to D1

Is this any use or do you not want to add another column?

NO, just found it

CELL VALUE IS less than or equal to = MIN($A$1:$C$1)

change cell refs as required,

use the format painter to format other cells

 

[Aladin Akyurek]

See my contrib in:

http://www.mrexcel.com/board2/viewtopic.php?t=72823

which gives you more than a mere eyeballing.

You can augment the formula system I describe with conditional formatting where you can use a formula like in:

=A$1=MIN($A$1:$L$1)

 

[dpbarry]

Hi..

Yes, that would partly do the trick but is there also a way of telling the =min function that if the cell is zero (maybe don't have a price for or don't sell it) exclude it from the test.

0.00 is cheaper than £74.12.

Regards

Declan

 

[eliW]

Hi Barry,

It can be done by Conditional Formate

Follow the steps in my example:

1) Select your range
2) From menu choose Format | Conditional Formatting
3) Select Formula is and enter the following formula (adjust to yor range)
=A2=MIN($A2:$C2)
4)Select Format | Patterns and choose the red color
5) Finish with OK

Hope you'll manage,

Eli

Book1
	A	B	C	D
1	A	B	C
2	10	20	30
3	12	22	7
4	45	40	42
5
6
Sheet1

 

[eliW]

Hi..

Yes, that would partly do the trick but is there also a way of telling the =min function that if the cell is zero (maybe don't have a price for or don't sell it) exclude it from the test.

0.00 is cheaper than £74.12.

Regards

Declan

No prblem.

MIN function doesnt take into account Blank cells.

 

[dpbarry]

IS there a way of filtering out 0.00 prices so that the equation only checks prices that are greater than 0.00

Regards

Declan

 

[onlyadrafter]

Hello,

But it does include a cell value of 0.

 

[eliW]

IS there a way of filtering out 0.00 prices so that the equation only checks prices that are greater than 0.00

Regards

Declan

Turn all 0 to empty cells

 

[eliW]

Another solution to avoide 0 is to ARRAY ENTER the formula:

{=A2=MIN(IF(A2:C2<>0,A2:C2))}

in my previous example

Eli