Minrali

Q: What is the title of the highest rated item from 2016?

I have titles (Table 1::B) that are labeled by year (Table 1::A) and have a percent value (Table 1::E) assigned to them.

 

In Table 2 I have used an AVERAGEIF formula to determine the average percent of anything with the 2016 label. I would also like column B of Table 2 to find the actual title name (Table 1::B) with the highest percent value (Table 1::E) for the corresponding year between (Table 2::A) and (Table 1::A). Essentially, the question being answered is "What is the Title of the highest rated (percent) item from 2016?"

 

Additionally, could I then further apply this same concept to get the average value of anything from a particular decade and also list the top title for each decade? The tricky part here is I can't simply use a direct IF statement because the decades are listed as "2010s", "2000s", etc. which don't show up in the original Table 1. Any ideas?

 

Thanks everybody for the help. This is just a fun side project that I sometimes like to do and then I often learn new concepts that I can use to assess data in my job as a teacher.

Screen Shot 2016-09-05 at 12.01.38 .png

iMac (21.5-inch Mid 2011), OS X El Capitan (10.11.6)

Posted on Sep 5, 2016 12:14 PM

Close

Q: What is the title of the highest rated item from 2016?

  • All replies
  • Helpful answers

  • by t quinn,

    t quinn t quinn Sep 5, 2016 6:39 PM in response to Minrali
    Level 5 (4,930 points)
    Mac OS X
    Sep 5, 2016 6:39 PM in response to Minrali

    Hi Minrali,

     

    Here is one approach. I started with 2 additional columns in your source table.

    Screen Shot 2016-09-05 at 7.21.51 PM.png

    F2= MAX(OFFSET($A$1,MATCH(A2,A,0)−1,4,COUNTIF(A,A2),columns))

    If your % column is not the 4th column from A you need ot change the "4" in this formula.

    This is filled down

    Screen Shot 2016-09-05 at 7.28.29 PM.png

    G2= INDEX(B,MATCH(F2,E,0),column-index,area-index)

    Filled down.

    If you want to eliminate column F the formula would be

    INDEX(B,MATCH(MAX(OFFSET($A$1,MATCH(A2,A,0)−1,4,COUNTIF(A,A2),columns)),E,0),col umn-index,area-index)

    This combines the two.

    Screen Shot 2016-09-05 at 7.31.16 PM.png

    And done. I would filter the table so years with no values are hidden. Or you could wrap this one in IFERROR() if you don't want to see the error flags.

     

    quinn

  • by t quinn,

    t quinn t quinn Sep 5, 2016 8:51 PM in response to t quinn
    Level 5 (4,930 points)
    Mac OS X
    Sep 5, 2016 8:51 PM in response to t quinn

    Hi Minrali,

     

    Here is a version without any extra columns.

    Screen Shot 2016-09-05 at 9.47.47 PM.png

    B2= INDEX(Table 1::B,MATCH(MAX(OFFSET(Table 1::$A$1,MATCH(A2,Table 1::A,0)−1,4,COUNTIF(Table 1::A,A2),columns)),Table 1::E,0))

     

    quinn