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
elwayisgodAsked:
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.

Rob HensonFinance AnalystCommented:
Can you upload an Excel file rather than the PDF?
0
Rob HensonFinance 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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
Dustin SaundersDirector of OperationsCommented:
I used tables for the references, but same conditional formatting.
example.xlsx
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rob HensonFinance 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.
0
elwayisgodAuthor 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
0
elwayisgodAuthor 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
0
elwayisgodAuthor Commented:
0
Rob HensonFinance 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
0
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.