elwayisgod
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
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
Can you upload an Excel file rather than the PDF?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I used tables for the references, but same conditional formatting.
example.xlsx
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$1 4,MATCH(C$ 17,$B$6:$F $6,0),FALS E)=LARGE(C $7:C$14,1) ,20,IF(VLO OKUP($B18, $B$6:$F$14 ,MATCH(C$1 7,$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.
=IF(VLOOKUP($B18,$B$6:$F$1
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.
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
2018-09-13_11-32-13.pdf
ASKER
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
2018-09-13_11-42-19.pdf
ASKER
Here's file
2018_Fantasy_Football_Standings.xlsx
2018_Fantasy_Football_Standings.xlsx
Need to close out the nested IF statement:
=IF(C7<>"",IF(C7=LARGE(C$7 :C$14,1),2 0,IF(C7=LA RGE(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
=IF(C7<>"",IF(C7=LARGE(C$7
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