• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional formatting multiple rows for Large & Max values

T

Tom Repden

New Member
Hello all, new member and first post / question. Will try and be as complete as possible. Be gentle!
My full sheet consists of approx 800 rows of customers and approx 90 columns of the various products they purchase from the company.
My goal is to have conditional formatting highlight the MAX value, and the 2nd & 3rd LARGEest values for each ROW / Customer each a different color. e.g. 'Account 1001' highest value is Product 5 at $13,333.55 in blue and 2nd largest Product D at $7290.82 in yellow. gradient bars or color scales is not a solution as I want a specific single highlight for each result.

Alone MAX value is easy using 'TOP 1' and a color but if I add a second condition format formula LARGE then it stops working.
2nd largests however seems to format arbitray values or several values in the row.
Using Conditional formatting 'New' > 'Formula' I have entered =C2=LARGE(C2:K2, 2) or =C3=MAX(C3:K3)
Another issue is that I will want to copy the formatting to all rows and Excel uses an absolute reference with LARGE and if I change the relative to $C2:$K2 Excel changes it back to an absolute $C$4:$K$4 .
Kinda lost after that.
thx
Tom
 

Attachments

  • conditional formatting large.xlsx
    12.1 KB · Views: 6
You were pretty close. Need to adjust the formula to use absolute columns, while the applies to range uses complete absolute over entire range. See attached. :D
 

Attachments

  • Example CF.xlsx
    12.1 KB · Views: 10
Hi, Tom Repden!

Try defining the 2 CF conditions as follows:

a) Higher value
Formula: =C2=MAX($C2:$K2)

b) 2nd higher value
Formula: =C2=LARGE($C2:$K2,2)

Both applied to: =$C$2:$K$25

Keep in mind that there cases where due to LARGE function behavior you'll get 2 highest values and no 2nd higher one if there're 2 products with the highest value for the same customer. Check Account 1013 and 1024.

Regards!
 
thx all - close is good! means I'm getting better! I amy have had it right but then I have a tendancy to reverse things - so probably entered the absolutes backwards like I stated the colors... ooops.... but thx yes this is what I wanted! Tom
 
Hi, Tom Repden!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
You must log in or register to reply here.
Back
Top