How to take percentage rank of specific rows on conditional?

Question

I want to convert some data into percentage ranks however how can I do this so that it only takes percentage ranks of rows when the value of another column is in that same row?

My data:

821073  4086    woodland
1367182 8483    woodland
774315  3908    greenspace
3155757 9229    greenspace
2298996 9195    greenspace
1133531 5386    greenspace
1839304 6111    woodland
642198  3109    artificial_forest
642198  3109    artificial_forest
9727658 26260   artificial_forest
517463  2776    artificial_forest
3082246 8345    forest
1043860 6873    forest
1851161 5790    forest
762000  3633    greenarea
1575599 5980    greenarea
534234  2795    greenarea
1783711 5446    greenarea

In this example I want to calculate the average percentage rank of Area and Perimtr for those values of Artificial_forest and green_area, I have tried:

=AVERAGE(PERCENTRANK.INC($F$8:$F$11, $F$15:$F$18,F8, F15))

I end up getting repeating values for woodland, greenspace, forest, how can I get it so that it only takes the average percentage rank of artificial_forest and greenarea, where it returns NA or 0 elsewhere?

Answer 1

REVISED SOLN

Screenshots/here refer:

Given Office 365 available (per comment @Stackbeans, t-19hrs), a cleaner way to incorporate both percentranks within an array is as follows (screenshot, here refer):

=SORT(IFERROR(PERCENTRANK.INC(FILTER(B4:C21,$D$4:$D$21=$G$4),FILTER(B4:C21,$D$4:$D$21=$G$4)),""),,1)

---

Averages can be determined directly without reproducing this 'array/spill-type' equation as follows:

Area

=AVERAGE(INDEX(SORT(IFERROR(PERCENTRANK.INC(FILTER(B4:C21,$D$4:$D$21=$G$4),FILTER(B4:C21,$D$4:$D$21=$G$4)),""),,1),0,1))

Perimeter

=AVERAGE(INDEX(SORT(IFERROR(PERCENTRANK.INC(FILTER(B4:C21,$D$4:$D$21=$G$4),FILTER(B4:C21,$D$4:$D$21=$G$4)),""),,1),0,2))

---

OR if the array is to be reproduced and displayed within excel, then this simplifies formula for the average somewhat:

=AVERAGE(INDEX(R3#,0,2))

(use average(index(r3#,0,1)) for area)

---

---

BONUS

To include validation list drop-down (screenshot below refers)

1. Create lookup list e.g. =UNIQUE(D4:D21) in cell N3
2. Select Name Manager in main ribbon, Formulas section:

3. In the pop-up GUI, select 'new', enter a name (here, 'Name_Lookup') and a reference equation (here, = $N$3#) noting the final # which makes this a dynamic range applicable to 'spill-type' ranges/arrays...

4. Hit 'Close' to return to first window of pop-up, and check the range of the resulting named range by clicking the small upward arrow on the right/bottom near the equation bar in the Gui:

5. Create a validation list: cell G5: type alt + d + L (sequentially, releasing one button before depressing next). see here for more info re validation lists...

6. First window of pop-up GUI, select list from drop down menu and enter corresponding reference formula: = Name_Lookup, like so:

7. Highlight cell different colour (not essential, but makes sheet more user-friendly for others)...

---

---

ORIGINAL SOLN

Screenshot/here refer.

I consider 'area' alone - this can easily be extended to perimtr in the same fashion.

To return the array of values corresponding to the percentrank for each level of the filtered result, use:

=SORT(IFERROR(PERCENTRANK.INC(IF($D$4:$D$21=$G$4,B4:B21,""),IF($D$4:$D$21=$G$4,B4:B21,"")),""),,1)

To calculate the average percentrank for values in this array, use:

=AVERAGE(IFERROR(PERCENTRANK.INC(IF($D$4:$D$21=$G$4,B4:B21,""),IF($D$4:$D$21=$G$4,B4:B21,"")),""))

---

Notes

• Lookup feature requires Office 365 compatible version of Excel. Not an essential feature (was not requested) so assuming this can be ignored if no access to aforementioned Excel version...
• To retrieve individual values within array, use =INDEX(SORT(IFERROR(PERCENTRANK.INC(IF($D$4:$D$21=$G$4,B4:B21,""),IF($D$4:$D$21=$G$4,B4:B21,"")),""),,1),I1) where the final component (I1 = 1,2,..) represents the (ordered) rank of said indexation..

---