Multiple condition formula

Hi, can anyone here help me?... I'm trying to apply multiple conditions to a formula and have attached a screen shot for reference.

Capture.JPG

The spreadsheet displays scores from a surf contest... The first thing I want to do is highlight the top 2 scores in a range eg. D8 to O8. In this particular range the second highest score appears twice and therefore 3 cells are highlighted... I'd like to highlight a max of 2 cells in that range and only count the duplicate score which appeared first in the row. However if the top score is duplicated, then both and only those cells need to highlighted. see row 32.

Another thing I'd like to do is with reference to row 34... If "INT" is present in a row the only the top 1 score is to be highlighted.

Lastly... I cell B6 I'd like to display the average of only the green coloured cells in the range D7 to O9.

Any help would be hugely appreciated.

Hi Glenn, Thanks for responding. I'll have a go at attaching the sample.

You can get the total in P7 using this formula:

=LARGE(D7:O7,1)+LARGE(D7:O7,2)*(COUNTIF(D7:O7,"int")=0)

Copy down as required.

Hope this helps.

Pete

You can get the average in B6 using this formula:

=SUM(P7:P9)/(6-COUNTIF(D7:L9,"int"))

Copy into B11 etc.

Hope this helps.

Pete

Hi
You can format with conditional formatting from D7 to O14 using this formula

Formula:

Please Login or Register  to view this content.

See the file

Wow!

Thanks so much to all of you for your help... My spreadsheet is performing how I want it to now.

Thanks again.

You're welcome, and thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete