CODEWITHSUNDEEP
excelindexingexcel-formulamatchrank
woiya
woiya

Reputation: 59

Rank subgroup within dynamic range

I've got a ROI comparison table like this:

Month | ROI | 1  | 2  | 3  |
Jul   | A   | 1% | 
Jul   | B   | 3% |
Jul   | C   | 2% |
Jun   | A   | 4% | 6% |
Jun   | B   | 5% | 7% |
Jun   | C   | 6% | 8% |
May   | A   | 2% | 4% | 7% |
May   | C   | 3% | 5% | 6% |

I would like to rank each product's ROI (A, B and C) each month - so only rank the most right percentage for a given month.

But the range need to be dynamic as each product may have a different launch date. For example, the month of May doesn't have product B.

So the output will be:

   Month  | ROI | 1  | 2  | 3  | Rank
    Jul   | A   | 1% |    |    | 3
    Jul   | B   | 3% |    |    | 1
    Jul   | C   | 2% |    |    | 2
    Jun   | A   | 4% | 6% |    | 3
    Jun   | B   | 5% | 7% |    | 2
    Jun   | C   | 6% | 8% |    | 1
    May   | A   | 2% | 4% | 7% | 1
    May   | C   | 3% | 5% | 6% | 2

I'm thinking of using the Index/Match function:

=RANK(INDEX(G3:S3,MATCH(9^99,G3:S3,1)),G3:G5,0)

But I'm not sure how to make the G3:G5 dynamic.

Upvotes: 1

Views: 1515

Answers (2)

user4039065
user4039065

Reputation:

Start with a simple subgrouped ranking using the COUNTIFS function.

=COUNTIFS(A:A, A2, C:C, "<="&C2)

rank_latest

Modify the range of percentages to catch the right-most number from that row using INDEX on a multiple column range with an approximate MATCH.

=COUNTIFS(A:A, A2, INDEX(A:F, 0, MATCH(1E+99, A2:F2)), ">="&INDEX(A:F, ROW(2:2), MATCH(1E+99, A2:F2)))

rank_latest_two

Fill down as necessary.

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40204

I think I managed to get it working with the following formula in the rank column:

=RANK(OFFSET(G3,,SUM(--ISNUMBER(G3:S3))-1),
      OFFSET(G3,COUNTIF(E3:$E$999,E3)-COUNTIF($E$1:$E$999,E3),
                SUM(--ISNUMBER(G3:S3))-1,COUNTIF($E$1:$E$999,E3)))

(assuming your months are in E1:E999).

What this does is looks at G3 and shifts to the rightmost non-empty (ISNUMBER) column using OFFSET and considers that value to be ranked in the OFFSET array where the month matches the current row's month.

Let's break apart the second OFFSET formula (assuming we are considering a Jul row):

  • Start at G3.
  • Row offset: count of Jul rows below current row (inclusive) - total count of Jul rows.
  • Column offset: count of cells containing numbers in current row - 1
  • Height: total count of Jul rows.

This is an array formula, so it must be entered using Ctrl+Shift+Enter.

Note: The formula assumes that the rightmost values are the in same column if the month is the same. This holds in your given example, but I don't know whether it holds for your full data set. If this is not true in general, you can create a helper column using the first OFFSET piece of the formula

=OFFSET(G3,,SUM(--ISNUMBER(G3:S3))-1)

to get your rightmost number in each row and then do your ranking on that helper column.

Upvotes: 0

Related Questions