Friday, August 16, 2013

Find the value in Column A corresponding to the max number of a range

Question:

( by Surcam )

I have a spreadsheet set up to tally the scores of online college football games dynasty year. 

Each row shows points scored for each team (Tulsa, UCLA, Wake Forest, Colorado, Kentucky)
Each column shows points scored against for each team.

Year 1TUUCLAWFCUUK
TUNO GAME31501721
UCLA17NO GAME38328
WF07NO GAME2828
CU14624NO GAME14
UK1732117NO GAME


I am attempting to list the teams with the highest and lowest point totals for a given year.  I can easily run =Max(B2:F6) to find the highest, but how do I grab the data in column A so I know which team did it?   My google fu has been unable to find the answer thus far even though I know it must be simple.

Thanks!


Screenshot of Sheet "Scores":



Solution:

Try any of the following formulas:

=filter('Scores'!A2:A6;('Scores'!B2:B6=max('Scores'!B2:F6))+('Scores'!C2:C6=max('Scores'!B2:F6))+('Scores'!D2:D6=max('Scores'!B2:F6))+('Scores'!E2:E6=max('Scores'!B2:F6))+('Scores'!F2:F6=max('Scores'!B2:F6)))

OR

=query('Scores'!A2:F6;"select A where B="&max('Scores'!B2:F6)&" or C="&max('Scores'!B2:F6)&" or D="&max('Scores'!B2:F6)&" or E="&max('Scores'!B2:F6)&" or F="&max('Scores'!B2:F6))


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

2 comments:

  1. I very much appreciate your time on this. When I plug in the filter command, it gives me "17" which isn't the team name. The correct answer for year 1 would be "TU" with 50 points scored.

    When I try the query command I get an error.

    I'll read up on both filter & query and see if I can't figure out how to make it happen.

    Thank you again!!!

    ReplyDelete
    Replies
    1. For me both solutions are working,
      Can you share your spreaadsheet ?

      Delete