Conditional formatting and basic array question

[DublinMeUp]

Hey all,

An example of the data is attached, I'm trying to accomplish two tasks.

Firstly, the column called "Second largest" seems to be showing the largest number again, I assume this is due to there being duplicate "largest" numbers? Is there a way of finding the second largest, like if i have the following numbers; 6,7,7,5,4. Can i get the large function to give me the correct 2nd largest which is 6, ( =large(A1:E1,2) = 6? ).

The other thing i want to do is work out a conditional formatting formula to fill the largest number(s) in each row green and the second largest in each row orange. Can i select the whole table and accomplish this or do i need to select each row individually?
I don't even have an example for this as I'm totally new to conditional formatting, I can only get the highest ranks per row to be filled in green by selecting that row, then i have to copy this formatting into each subsequent row.

Many thanks in advance

Array and conditional formatting.xlsx

[TMS]

No, the second largest is 7 ... you have two sevens in the list, so, in ranking, you'd have first, joint first, third, etc.

Regards, TMS

[FDibbins]

for the 1st part, change your formula to...=LARGE(B2:P2,COUNTIF(B2:P2,LARGE(B2:P2,1))+1)

for the 2nd part, highlight the entire table, go into CF - use formula - and for largest use - =B2=LARGE(B2:P2,1)

for 2nd largest, use =B2=LARGE(B2:P2,COUNTIF(B2:P2,LARGE(B2:P2,1))+1)

let me know how that works for you?

[DublinMeUp]

for the 1st part, change your formula to...=LARGE(B2:P2,COUNTIF(B2:P2,LARGE(B2:P2,1))+1)

for the 2nd part, highlight the entire table, go into CF - use formula - and for largest use - =B2=LARGE(B2:P2,1)

for 2nd largest, use =B2=LARGE(B2:P2,COUNTIF(B2:P2,LARGE(B2:P2,1))+1)

let me know how that works for you?

Perfect! Thank you so much.

Gonna have a good look at them to make sure i understand how you did it, may save me posting similar questions in future =).

[FDibbins]

what i did in searching forthe 2nd largest, was count how many "largest" you had - the =COUNTIF(B2:P2,LARGE(B2:P2,1) - part, and then added 1 to it, that would then give you the next largest number. ie if you had 3 "largest" numbers, then the 2nd largest number would be the "4th largest". so i then looked for the 4th largest number.

for the CF, i applied the same basic logic

glad to help, and thx for the star