# Formula to calculate highest value etc.

Excel Formula:

I have a list of numbers in C7 to C14 that are associated with a team name.  I need a formula that will populate 20 for team with highest score and 10 for team with 2nd highest score.  Also both cells get colored green, if possible.

2018-09-13_10-29-08.pdf
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Finance AnalystCommented:
Can you upload an Excel file rather than the PDF?
Finance AnalystCommented:
In the meantime, the green highlighting is possible with Conditional Formatting and the Winnings is possible using the LARGE function.

Conditional Formatting

Select the range which you need to highlight (eg C7 to C14). On the Home tab select the Conditional Formatting icon and from the subsequent menu choose "Top/Bottom Rules" and then choose "Top 10 items". A simple dialogue will show with a list of numbers to scroll Up/Down, scroll down to 2 and the other drop-down shows various Formatting options, one of the default is Green fill with dark green text or there is a Custom format option at the bottom of the list.

Winnings
Based on the cell ranges in your PDF, use the following formula in cell C18 and then copy down and across as required:
=IF(C7=LARGE(C\$7:C\$14,1),20,IF(C7=LARGE(C\$7:C\$14,2),10,""))

See attached.
CF-and-LARGE.xlsx

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Director of OperationsCommented:
I used tables for the references, but same conditional formatting.
example.xlsx
Finance AnalystCommented:
Without Table references but overcoming the risk of the data being in different order, formula for Winnings; start in C18 and copy down and across:

=IF(VLOOKUP(\$B18,\$B\$6:\$F\$14,MATCH(C\$17,\$B\$6:\$F\$6,0),FALSE)=LARGE(C\$7:C\$14,1),20,IF(VLOOKUP(\$B18,\$B\$6:\$F\$14,MATCH(C\$17,\$B\$6:\$F\$6,0),FALSE)=LARGE(C\$7:C\$14,2),10,""))

Also just found that the Top 10 Conditional Format process needs to be applied to each column separately rather than grouped otherwise it highlights the top 2 for the whole area rather than each week. However, you can apply it to one column and then copy that column and use Paste Special > Formats to apply it to the other columns.
Author Commented:
Both of these look good.  Can it not display #NUM!  when there is no data in the cells C7 to C14?
2018-09-13_11-32-13.pdf
Author Commented:
I got it partly there.  But on the cells columns where there is data now I get a FALSE, but on empty cells it's blank which is good.
2018-09-13_11-42-19.pdf
Author Commented:
Finance AnalystCommented:
Need to close out the nested IF statement:

=IF(C7<>"",IF(C7=LARGE(C\$7:C\$14,1),20,IF(C7=LARGE(C\$7:C\$14,2),10,"")),"")

Previously the formula was doing:

If C7 isn't blank then Nested IF else Blank
Nested IF then was IF LARGEST (LARGE 1) then 20 Else IF 2nd LARGEST (LARGE 2) then 10 but no Else.

Attached updated file with the Conditional Formatting across all columns (used RANDBETWEEN to populate with dummy numbers) and the Winnings fixed across all weeks.
2018_Fantasy_Football_Standings.xlsx
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.