Using Percentile formula with conditions?

[Jezzzza]

I have a project list with multiple service lines, departments, included. I am trying to create a formula that will essentially flag each row with either yes or no, finding if the project listed in that row is in the 90th percentile, or top 10% of the highest budgeted projects in the list. Ideally I would like it to exclude any line that has a zero for the budget column, and I want to be able to use the specific row's assigned service line to calculate the percentile of all projects in that same service line, from the main list.

This is my current formula:

=IF(AND(([@TransBudget]>=0),([@[Project stage]]="Active"),([@TransBudget]>=PERCENTILE.INC(H:H,0.9))),"YES","NO")

but I am having trouble adding the condition of it looking at only other items in the master list that have the same service line listed as the row it is looking at.

Thanks!

 

[JamesCanale]

MacOS is hopefully the same.

MrExcelPlayground18.xlsx
	A	B	C	D
1	Project Stage	Transbudget	Service Line	Top 10% Budget in Service Line
2	Active	$ 4,774.00	1	No
3	Active	$ 9,089.00	1	No
4	Active	$ 155.00	1	No
5	Dead	$ 8,786.00	1	No
6	Active	$ 6,409.00	2	No
7	Active	$ 9,077.00	2	Yes
8	Active	$ 6,062.00	2	No
9	Dead	$ 8,855.00	3	Yes
10	Active	$ 4,556.00	3	No
11	Active	$ 3,265.00	3	No
12	Active	$ 4,338.00	3	No
13	Active	$ 344.00	3	No
14	Dead	$ 4,782.00	3	No
15	Active	$ 6,438.00	3	No
16	Active	$ 5,309.00	4	No
17	Active	$ 9,302.00	4	Yes
18	Active	$ 5,774.00	4	No
19	Dead	$ 2,726.00	1	No
20	Active	$ 4,076.00	1	No
21	Active	$ 6,946.00	1	No
22	Active	$ 7,995.00	1	No
23	Active	$ 7,029.00	2	No
24	Dead	$ 3,023.00	2	No
25	Active	$ 4,714.00	2	No
26	Active	$ 4,012.00	3	No
27	Active	$ 6,628.00	4	No
28	Active	$ 4,973.00	3	No
29	Active	$ 3,936.00	3	No
30	Active	$ 1,228.00	3	No
31	Active	$ 6,207.00	3	No
32	Active	$ 7,320.00	3	No
33	Active	$ 105.00	4	No
34	Active	$ 438.00	4	No
35	Active	$ 8,319.00	4	Yes
36	Active	$ 6,685.00	4	No
37	Active	$ 4,340.00	4	No
38	Dead	$ 1,386.00	1	No
39	Active	$ 2,513.00	1	No
40	Active	$ 9,334.00	1	No
41	Active	$ 236.00	1	No
42	Active	$ 9,275.00	1	No
43	Active	$ 9,825.00	1	Yes
44	Active	$ 3,611.00	1	No
45	Active	$ 7,662.00	1	No
46	Dead	$ 7,657.00	1	No
47	Active	$ 4,839.00	2	No
48	Active	$ 2,024.00	2	No
49	Active	$ 5,343.00	2	No
50	Dead	$ 162.00	3	No
51	Active	$ 4,699.00	3	No
52	Active	$ 5,879.00	3	No
53	Active	$ 5,865.00	3	No
54	Active	$ 8,632.00	3	No
55	Dead	$ 1,519.00	3	No
56	Active	$ 9,103.00	3	Yes
57	Active	$ 6,864.00	4	No
58	Active	$ 2,171.00	4	No
59	Active	$ 709.00	4	No
60	Active	$ 5,756.00	4	No
61	Active	$ 2,257.00	4	No
62	Active	$ 7,320.00	3	No
63	Active	$ 105.00	4	No
64	Active	$ 438.00	4	No
65	Active	$ 8,319.00	4	Yes
66	Active	$ 6,685.00	4	No
67	Active	$ 4,340.00	4	No
68	Dead	$ 1,386.00	1	No
69	Active	$ 2,513.00	1	No
70	Active	$ 9,334.00	1	No
71	Active	$ 236.00	1	No
72	Active	$ 9,275.00	1	No
73	Active	$ 9,825.00	1	Yes
74	Active	$ 3,611.00	1	No
75	Active	$ 7,662.00	1	No
76	Dead	$ 7,657.00	1	No
77	Active	$ 4,839.00	2	No
78	Active	$ 2,024.00	2	No
79	Active	$ 5,343.00	2	No
80	Dead	$ 162.00	3	No
81	Active	$ 4,699.00	3	No
82	Active	$ 5,879.00	3	No
83	Active	$ 5,865.00	3	No
84	Active	$ 8,632.00	3	No
85	Dead	$ 1,519.00	3	No
86	Active	$ 9,103.00	3	Yes
87	Active	$ 6,864.00	4	No
88	Active	$ 2,171.00	4	No
89	Active	$ 709.00	4	No
90	Active	$ 5,756.00	4	No
91	Active	$ 2,257.00	4	No
Sheet16

Cell Formulas
Range	Formula
D2:D91	D2	=IF([@Transbudget]>PERCENTILE.INC(FILTER([Transbudget],([@Transbudget]>0)*([Project Stage]="Active")*([Service Line]=[@[Service Line]])),0.9),"Yes","No")

 

[Jezzzza]

Thanks!