Home/ Questions/Q 13266
Next
Answered
computing
  • 8
Asked: 2021-12-12T03:22:14+00:00 In:

Solved Excel Tracking Movement In Ranking From Month To Month

  • 8

Hi, I have a spreadsheet that tracks Hits and Player rankings based on the Hits. Each month is contained on a different worksheet.

I need a formula that tells me how many places up and down in the rankings a player fell or increased each month.

Is this even possible?

Share

    1 Answer

    1. Best Answer
      There are multiple ways to reference a cell to the left of a value.

      One method is to use OFFSET and MATCH:

      =OFFSET(Sheet1!$B$2,MATCH(Sheet2!B2,Sheet1!$B$2:$B$10,0)-1,-1)

      This formula will MATCH the value from Sheet2!B2 in the range SHEET1!$B$2:$B$10 and return position of the player number in that list. It will then use that number as the rows argument (after we subtract 1 from it) for the OFFSET function. -1 as the cols argument will reference the column to the left.

      Another method is SUMIF:

      =SUMIF(Sheet1!$B$2:$B$10, Sheet2!B2, Sheet1!$A$2:$A$10)

      Since there is only one occurrence of each player number in the range, the SUMIF will only return a single rank value for each player.

      You could subtract 2 SUMIF’s to get the change in Rank directly.

      =SUMIF(Sheet1!$B$2:$B$10,Sheet2!B2,Sheet1!$A$2:$A$10) –
      SUMIF(Sheet2!$B$2:$B$10,Sheet2!B2,Sheet2!$A$2:$A$10)

      Click Here Before Posting Data or VBA Code —> How To Post Data or Code.

      • 0