0

I have a table in excel with the following data:

+-------------------+----------------------+----------+
| Contribution Type | % Contribution Match | % Salary |
+-------------------+----------------------+----------+
| Type 1            |                  0.5 |          |
| Type 1            |                  0.6 |          |
| Type 1            |                      |          |
| Type 2            |                      |     0.03 |
| Type 2            |                      |     0.04 |
| Type 2            |                      |        0 |
| Type 3            |                  0.7 |     0.05 |
| Type 3            |                  0.6 |     0.04 |
| Type 3            |                      |     0.05 |
| Type 1            |                  0.5 |          |
| Type 2            |                      |     0.04 |
| Type 3            |                 0.75 |      0.1 |
+-------------------+----------------------+----------+

I would like to use an array formula to calculate quartiles for each contribution type based on the additional condition (because contribution type is the first condition) that the relevant data values are not left blank or entered in as zero value.

Type 1 matches 100% of salary up to a certain percent (X) of the employees contribution:

{=PERCENTILE.EXC(IF(Contributions[Contribution Type]="Type 1",Contributions[% Contribution Match]),0.25)} (etc.for med, avg, and 75th)

Type 2 matches a limited percent (Y) of salary, with no limit on the employee's contribution:

{=PERCENTILE.EXC(IF(Contributions[Contribution Type]="Type 2",Contributions[% Salary]),0.25)}(etc.for med, avg, and 75th)

Type 3 has limits on both the employee contribution and the salary percents:

{=PERCENTILE.EXC(IF(Contributions[Contribution Type]="Type 3",Contributions[% Contribution Match]),0.25)} (etc.for med, avg, and 75th)

{=PERCENTILE.EXC(IF(Contributions[Contribution Type]="Type 3",Contributions[% Salary]),0.25)} (etc.for med, avg, and 75th)

The resulting table calculates quartiles including blank and zero values (not what I want):

+--------+-----------+--------+---------+------------+-----------+--------+---------+------------+
|         Percentage of Employee Contribution        |            Percentage of Salary           |
+--------+-----------+--------+---------+------------+-----------+--------+---------+------------+
|        | 25th %-ile| Median | Average | 75th %-ile | 25th %-ile| Median | Average | 75th %-ile |
| Type 1 | 12.50%    | 50.00% | 40.00%  | 57.50%     | 0.00%     | 0.00%  | 0.00%   | 0.00%      |
| Type 2 | 0.00%     | 0.00%  | 0.00%   | 0.00%      | 0.75%     | 3.50%  | 2.75%   | 4.00%      |
| Type 3 | 15.00%    | 65.00% | 51.25%  | 73.75%     | 4.25%     | 5.00%  | 6.00%   | 8.75%      |
+--------+-----------+--------+---------+------------+-----------+--------+---------+------------+

I have tried every combination of IF(AND IF(IF I can think of. I am relatively new to using array formulas, so any help would be greatly appreciated. I am open to using a different formula if there is a better one, but discouraged from reformatting the data table to presort values.

2 Answers 2

0

You may apply this Array Formula:

{=PERCENTILE(IF((($A$2:$A$100=$F$3)*($B$2:$B$100=$G$3)),$C$2:$C$100),0.5)}

N.B.

  1. In Cell F3 & G3 you have to store Criterion, which makes Formula Dynamic rather than Hard Core.
  2. Finish Formula with Ctrl+Shift+Enter.
  3. Adjust Cell references in Formula as needed.
2
  • Thank you so much! This worked perfectly! I wasn't sure how to store the criterion "<>0" in a separate cell, so I ended up hard coding them into the formulas. Do you know if there is, in fact, a way to store "not equal to zero" dynamically?
    – SuziLmrdo
    Sep 10, 2018 at 14:00
  • @SuziLmrdo, glad to help you ,,, put any value is not ZERO is the cell and use the Cell address <>$A$3. Keep asking ☺ Sep 11, 2018 at 3:45
0

Many thanks to Rajesh S for his assistance with my question! Here are the formulas I came up with, based on his advice:

Percentage of Employee Contribution - 25th Percentile (replace 0.25 with 0.75 to calculate 75th percentile)

{=IFERROR(PERCENTILE.EXC(IF(((Contributions[Contribution Type]="Type 1")(Contributions[% Contribution Match]<>0)),Contributions[% Contribution Match]),0.25),"N/A")} {=IFERROR(PERCENTILE.EXC(IF(((Contributions[Contribution Type]="Type 2")(Contributions[% Contribution Match]<>0)),Contributions[% Contribution Match]),0.25),"N/A")} {=IFERROR(PERCENTILE.EXC(IF(((Contributions[Contribution Type]="Type 3")*(Contributions[% Contribution Match]<>0)),Contributions[% Contribution Match]),0.25),"N/A")}

Percentage of Employee Contribution - Median (replace MEDIAN with AVERAGE to calculate average values)

{=IFERROR(MEDIAN(IF(((Contributions[Contribution Type]="Type 1")(Contributions[% Contribution Match]<>0)),Contributions[% Contribution Match])),"N/A")} {=IFERROR(MEDIAN(IF(((Contributions[Contribution Type]="Type 2")(Contributions[% Contribution Match]<>0)),Contributions[% Contribution Match])),"N/A")} {=IFERROR(MEDIAN(IF(((Contributions[Contribution Type]="Type 3")*(Contributions[% Contribution Match]<>0)),Contributions[% Contribution Match])),"N/A")}

To calculate Percentage of Salary - 25th, Median, Average, 75th - replace all instances of "% Contribution Match" in the above formulas with "% of Salary"

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .