Re: How to determine second (and then third) highest value in a list

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Look up the "Rank" function in the help files. That can get you what you
want.

HTH

Alan P.

"Scott M. Lyon" <scott.RED.lyon.WHITE@xxxxxxxxxxxxxxxxx> wrote in message
news:%23ECMD38tFHA.256@xxxxxxxxxxxxxxxxxxxxxxx
> I've got an excel spreadsheet full of data, and I wanted to create a
> column that would tell me what the second highest value was, given a list
> of cells.
>
>
> For example, I've got 8 columns (columns K through R), with headers "Team
> 1" through "Team 8".
>
> Below that, on the following MANY rows, are numbers, associated with
> scores for the team in that column.
>
>
> I'd like to have a column that tells me what score was 1st place, which
> was 2nd place, and which was 3rd place.
>
> Finding the 1st place one is easy, using the =MAX(K4:R4).
>
> But I'm at a loss to determine the 2nd place, and then the 3rd place one.
>
>
>
> Then, once I determine that, can I use that value to do a lookup (for a
> new column) and return the header?
>
>
> For example, let's say we have the following row:
>
> Team1 Team2 Team3 Team4 Team5 Team6
> Team7 Team8
> 100 200 300 400 250 350
> 150 450
>
>
> I'd like to have a "1st place score" column that would report 450 (for
> this row)
> I'd like a "1st place team" column that would report "Team8"
> I'd like a "2nd place score" column that would report 400
> I'd like a "2nd place team" column that would report "Team4"
> I'd like a "3rd place score" column that would report 350
> I'd like a "3rd place team" column that would report "Team6"
>
>
> Then I'd be able to reproduce those formulae for every row through the
> entire list of data.
>
>
> Is there an easy way to do this?
>
>
> In fact, now that I think about it, if there's a way to get the team names
> without the scores, that would be acceptable too, but I figured I'd
> probably need the scores first.
>
>
>
> Thanks!
> -Scott
>


.

Relevant Pages

  • Re: On Time Delivery Query
    ... Microsoft Access MVP ... The totals for each group should be done on the report footers, ... like ShipStatus. ...
    (microsoft.public.access.reports)
  • Re: Pass Parameters to Record Source Query
    ... You started defining the problem from inside and then positing limited ... At the highest level, WHY are you trying to do this thing? ... >> In situations like you describe, I run the report from the form. ... >> HTH ...
    (microsoft.public.access.modulesdaovba)
  • Re: counting based ona number of variables.
    ... The formula spreadsheet functions as a report. ... I need to adapt or create formula that will look for ... >> HTH ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Missing pie slice
    ... Alan ... >> source is transparent to Access when creating a form, report, or chart. ... >>> the same data and query in an Access database or the same data in ...
    (microsoft.public.powerpoint)
  • Re: "Time issues"
    ... during parsing and writiting into excel script is taking to much cpu ... I am doing something similar: I am generating some text reports in separate directories, then using File::Find to find each report, reading each report and extracting some data from the first page, then writing the data to one row per file in a spreadsheet using Spreadsheet::WriteExcel. ... I am currently up to 51 reports extracted and summarized in an Excel spreadsheet. ... The whole process takes about 3 seconds, most of which is probably reading the files. ...
    (perl.beginners)