# conditional formatting (2)

rsturbo 14:26 13 Sep 2006
Locked

i have 6 columns of numerical data (1 quote from each company) i need to conditionally format the cells (highlight yellow) to show me who has the lowest quote. can somebody give me an idiots guide how to do this?

was shown how to do this for 2 columns (in previous thread) how do i do it for 6 columns?

sorry for being so dumb!

thanks for any help

johnnyrocker 14:29 13 Sep 2006

try the 'if' formula

johnny.

VoG II 14:36 13 Sep 2006

Assuming columns A to F with headings in Row 1.

In A2 Format > Conditional formatting. In the first dropdown select Formula Is. In the Formula box enter

=A2=MIN(\$A2:\$F2)

apply the formatting and OK your way out. Then click on the Format Painter (paintbrush icon) then select all of your data to apply the formatting to all the cells.

silverous 14:42 13 Sep 2006

I'm convinced Vog has an email update automatically when anyone posts an excel question :)

Do what VoGâ„˘ says.

rsturbo 17:48 13 Sep 2006

i have a big job tender i am trying to get best prices from my different supplier - so here goes:
Column A lists products (100).
Column B lists the description of products.
Column C lists Quantity of products.
Columns DEFGH are for supplier 1
D = total price (quantity x unit price)
E = total price + qty 10% (quantity (eg 100) +10% = 110) so the price for 110 items
F = total price + qty 20%
G = total price - qty 10% (qty 100 - 10% = 90 items)
H = total price - qty 20%

Columns IJKLM are for supplier 2
Columns NOPQR are for suppler 3

etc etc
i need to do conditional formatting for the relevant columns for each supplier eg i need to know who is the cheapest
supplier for each price column

i hope you can help vog, my brain hurts

VoG II 18:03 13 Sep 2006

Similar to above but start with cell D2 and in Formula Is enter

=D2=MIN(\$D2,\$I2,\$N2,\$S2,\$X2,\$AC2)

Use the Format Painter to apply all the way down column D, then apply to columns I, N, S, X and AC. This will just format the total price columns.

rsturbo 11:40 14 Sep 2006

so now; can i conditionally format the other columns by changing the cell references
eg: =e2=min(\$e2,\$j2,\$o2,\$t2,etc etc)? if this is possible then how do i ensure i paint the correct format to the relevant column, do i click on e2 then click format paintbrush.

As an aside when i format the cells they all turn green (i want the cell to turn green when it is the lowest value (price). If i put in data the lowest value cell remains green whilst the others return to no formatting (can live with this just wondering if its possible to have them all clear and only turn green if lowest value). Thank you so much for the help so far.

VoG II 11:51 14 Sep 2006

Re your first paragraph, that is exactly how to do it.

I'm not sure that I understand your second paragraph. If you mean when the cells are empty they all turn green then that is normal. It would be possible to modify the formula to get around this:

=AND(A2=MIN(\$A2:\$F2),NOT(ISBLANK(A2)))

(this is using your original 6-column example for simplicity)

rsturbo 12:23 14 Sep 2006

=AND(D2=MIN(\$D2,\$I2,\$N2,\$S2,\$X2,\$AC2),NOT(ISBLANK(A2)))

would therefore turn off the cell colour until it was shown to be the lowest value (price) by entering data into it and relative cells?

VoG II 12:27 14 Sep 2006

Yes. But - to go back to the easier 6-column example - if you have that formatting applied to A to F then as soon as you enter a value in any of the cells A to F then that cell would be coloured. Once you add data to another cell then the truly lower one would be coloured. It would be possible to adjust the formula to stop this from happening but it would be pretty complicated I think.

VoG II 12:29 14 Sep 2006

Spotted a typo in your formula

=AND(D2=MIN(\$D2,\$I2,\$N2,\$S2,\$X2,\$AC2),NOT(ISBLANK(A2)))

should be

=AND(D2=MIN(\$D2,\$I2,\$N2,\$S2,\$X2,\$AC2),NOT(ISBLANK(D2)))

D2 at the end, not A2.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

The art of 'British' pulp fiction