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

 

[Aladin Akyurek]

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

I suppose you mean:

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

Small(Ref,Specifier) returns the values from Ref that is given by Specifier.

Small(Ref,1) is equal to Min(Ref).

Small(Ref,2) would return the second smallest value from Ref.

The SMALL($E4:$G4,COUNTIF($E4:$G4,0)+1) bit has as Specifier:

COUNTIF($E4:$G4,0)

which counts 0's in Ref E4:G4.

If there was a single 0 in E4:G4 and no negative values (prices cannot be negative either), MIN(E4:G4) ( or SMALL(E4:G4,1) ] would return 0, which we don't want here. Since we count 0's and add 1 to that count, we are bound to determine the smallest > 0 price by skipping all 0's. in case of single 0, we get:

=SMALL(E4:G4,1+1) ===>

=SMALL(E4:G4,2)

Concluding: The specifier expression determines for us which smallest value we want.

=E4=SMALL(...)

checks whether the value in E4 is equal to the value SMALL computes. If that's the case, the formatting is applied to E4, otherwise E4 is skipped.

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

Conditional formatting can only affect the formatting, not the values themselves.

 

[dpbarry]

Okay..

Many thanks for that.

Declan