highlighting

E

excelguy

I have 2 columns of numbers. I want to highlight the 4 lowest numbers in the
2 columns. For example, a1=90, a2=100, a3=0, b1=100, b2=100, b3=0, b4=0,
b5=0. In this example, I would want a3, b3, b4, b5 to be highlighted in some
color because they are the 4 lowest numbers out of the 2 columns.
 
M

MartinaPa (MCT)

So you can use conditional formatting for whole range by following formula
=OR(SMALL($A$1:$B$5;1)=A1;SMALL($A$1:$B$5;2)=A1;SMALL($A$1:$B$5;3)=A1;SMALL($A$1:$B$5;4)=A1)
Don't forget to set color for formatting.
 
D

Dave Peterson

=SMALL($A$1:$B$3,4)
Will give you the 4th smallest number in the range A1:B3.

So you could select A1:B3 (with A1 the activecell) and check to see if the value
in the cell is less than or equal to that 4th smallest number:

=a1<=small($A$1:$B$3,4)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top