Search code examples
excelsumifssumproduct

Excel: Rank.AVG With IF/CASE Statement

I have a dataset in Excel that looks like below:

A     B    
8    10
9     8
9     8
0     3
0     3
0     3
0     3

I want to use the RANK.AVG function where it is only taking the rank from values in column B where the value in column A does not equal 0.

For context, this dataset will have a different amount of rows depending on the month and I don't want to modify the formula every time to adjust for only 2 rows in this case.

The current output looks like below:

=IFERROR(RANK.AVG(B2,$B$2:$B$8,1),0)

A     B    C
8    10    7
9     8  5.5
9     8  5.5
0     3  2.5
0     3  2.5
0     3  2.5
0     3  2.5

The desired output (manual at the moment) would be something like this:

=IFERROR(RANK.AVG(B2,$B$2:$B$4,1),0)

A     B    C
8    10    3
9     8  1.5
9     8  1.5  
0     3    0
0     3    0
0     3    0
0     3    0

Is there a function to account for this where I can do something like rank.avg.ifs similar to sumifs? Assuming I can use a sumproduct function but not seeing it where it is like rank avg.

Solution

  • If you have the ability to add a helper column, then you can use the following formulas:

    for RANK - =COUNTIFS(A2:$A$8;">0";B2:$B$8;"<="&B2)

    for RANK.AVG - =AVERAGEIF($B$2:$B$8;B2;$D$2:$D$8)

    enter image description here