Link to home
Start Free TrialLog in
Avatar of elwayisgod
elwayisgodFlag for United States of America

asked on

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
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Can you upload an Excel file rather than the PDF?
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I used tables for the references, but same conditional formatting.
example.xlsx
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.
Avatar of elwayisgod

ASKER

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
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
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