Percentrank

[Snehasandesh]

how to calculate percent rank for employee salary with designation and no of year experienc as condition

 

[ExcelElliott]

Apologies if the example is a bit weird, but the idea is the same for the formula.

Let me know if there are any questions or concerns.

	A	B	C	D	E	F	G
Designation	Years of Experience	Salary
Senior Director	20
Director	20
Senior Director	19
Vice President	17	Designation	Vice President
Senior Manager	18	Years of Experience
Director	18	Salary
Vice President	17	Percent Rank
Manager	17
Vice President	17
Senior Director	16
Vice President	17
Director	16
Senior Manager	14
Senior Director	14
Director	14
Manager	13

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: center"]ID[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]445533[/TD]

[TD="align: right"]$139,267.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]309440[/TD]

[TD="align: right"]$107,904.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]119136[/TD]

[TD="align: right"]$120,382.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]548464[/TD]

[TD="align: right"]$176,110.00 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]346154[/TD]

[TD="align: right"]$109,233.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"]17[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]482527[/TD]

[TD="align: right"]$110,771.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"]$175,000.00 [/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]677523[/TD]

[TD="align: right"]$189,551.00 [/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.597[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]747621[/TD]

[TD="align: right"]$95,595.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]744377[/TD]

[TD="align: right"]$170,735.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]858280[/TD]

[TD="align: right"]$144,234.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]362728[/TD]

[TD="align: right"]$119,485.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]225064[/TD]

[TD="align: right"]$117,874.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]242118[/TD]

[TD="align: right"]$95,048.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]159112[/TD]

[TD="align: right"]$140,049.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]464903[/TD]

[TD="align: right"]$116,064.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]959997[/TD]

[TD="align: right"]$92,020.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Salary Data (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G9[/TH]
[TD="align: left"]{=PERCENTRANK.INC(IF(Table134[Designation]=G6,IF(Table134[Years of Experience]=G7,Table134[Salary])),G8)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]