[SOLVED]Conditional formatting , highlight top 3 values help

[Laur3l]

Hi guys

I`m kinda new to excel, and i need it for school+work. I have faced some problems that i didn't managed to solve them.
One problem is like this :
I have to highlight the names from a column, based on the 3 highest values from another column.
Any ideas how to do it?

[NBVC]

Refer to the other column with a formula like:

=$B2>=Large($B$2:$B$10,3)

So this checks if B2 is larger or equal to the 3rd largest value in range B2:B10...

[Laur3l]

it helped,thx a lot, fixed my problem. but here's another one i had :

i have to format a column(where are shown names and surnames) based on another column which has the most frequency of the year.
Don't know if I explained it pretty well but i`ll give an example:

Col A Col B
name1 2011
name2 2012
name3 2012
name4 2012
name5 2012
name6 2011
name7 2011

so what i have to highlight is the names who have the year 2012(name 2,name3,name4,name5) because 2012 appears more frequently than 2011.

also can u explain me why u used the ">" sign in the forumla =$B2>=Large($B$2:$B$10,3). what does it stand for?

[NBVC]

Try conditional formula like:

=$B2=MODE($B$2:$B$10)

[Laur3l]

Try conditional formula like:

=$B2=MODE($B$2:$B$10)

This formula does nothing appearently, however i managed to return the most frequent year with "=MODE.SNGL(YEAR(B2:B10))" formula, but I can't manage to format the other column with a black background and white font for the names which stand for the most frequent year. any ideas?

[NBVC]

If you want to highlight, say A2:A10, then the conditional formula would be:

=$B2=MODE.SNGL(YEAR($B$2:$B$10))

make sure you don't put quotes around the formula.

[Laur3l]

hm.. it doesn't work, i`ll attach the file here so u know what i`m talking about.

ex1.xlsx

the idea is to make the names from the "nume si prenume asigurat" column , to appear highlighted where the most frequest year is , in this case 2008. the date is at the "data asigurarii" column. i don't use quotes for formulas

[NBVC]

sorry you need the year around the first part too...

This worked for me:

=YEAR($E2)=MODE(YEAR($E$2:$E$26))

or, for your version

=YEAR($E2)=MODE.SNGL(YEAR($E$2:$E$26))

[Laur3l]

riiiight.. it worked perfectly , thanks for the fast answers i`ll have more questions soon i`m sure :P