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.

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.
  • 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 '18 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 ☺ – Rajesh S Sep 11 '18 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"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.