Conditional Formatting Question

L

lindsayr

I have created a pricing matrix in Excel 2003 and I am trying to use the
conditional formatting function to highlight the top 3 cheapest prices in
each row. I have tried to set up the formatting, but I continue to get an
error message that says "Change the reference to a single cell, or use the
reference with a worksheet function."

I'm a bit of a novice when it comes to this program so any ideas would be
appreciated.

Thanks,
 
T

T. Valko

highlight the top 3 cheapest prices

In other words, you want to highlight the 3 *lowest* prices?

Ok, will there *always* be at least 3 prices?

Where are these prices?
 
L

lindsayr

Yes, I want to highlight the 3 "lowest" prices, which will change daily. I
have them linked to another page which will be updated everyday.
 
T

T. Valko

Assuming there will *always* be at least 3 prices...

The prices are in the range A1:E1...

Select the range A1:E1
Goto the menu Format>Conditional Formatting
Formula Is:

=AND(A1<>"",A1<=SMALL($A1:$E1,3))

Click the Format button
Select the desired style(s)
OK out
 
L

lindsayr

When I enter in the formula - no change occurs.....

Some rows maybe 20 prices, and some may have 10. What I ultimatley am
looking to do, is rank the top 3 prices from cheapest, to 2nd cheapest, to
3rd cheapest and have each price highlighted a different color. Sorry if this
is confusing.... :)
I appreciate your help!
 
T

T. Valko

Ok...

Let's assume the range of interest is A1:G5.

Not all rows will have the same amount of numbers in them but the last
column that might have a number is column G.

Select the range A1:G5
Goto the menu format>Conditional Formatting
Condition 1
Formula Is:

=AND(A1<>"",A1=MIN($A1:$G1))

Click the Format button
Select the desired style(s)
OK

Click the Add button

Condition 2
Formula Is:

=A1=SMALL($A1:$G1,2)

Click the Format button
Select the desired style(s)
OK


Click the Add button

Condition 3
Formula Is:

=A1=SMALL($A1:$G1,3)

Click the Format button
Select the desired style(s)
OK out
 
Top