Wie verwende ich ein Bedingungsfeld mit mehr als einer Bedingung, um Perzentile zu berechnen?

398
SuziLmrdo

Ich habe eine Tabelle in Excel mit folgenden Daten: 

+-------------------+----------------------+----------+ | 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 | +-------------------+----------------------+----------+

Ich möchte eine Matrixformel verwenden, um Quartile für jede Beitragsart basierend auf der zusätzlichen Bedingung zu berechnen (da Beitragsart die erste Bedingung ist), dass die relevanten Datenwerte nicht leer gelassen oder als Nullwert eingegeben werden.

Typ 1 entspricht 100% des Gehalts bis zu einem bestimmten Prozentsatz (X) des Arbeitnehmerbeitrags:

{= PERCENTILE.EXC (IF (Beiträge [Beitragstyp] = "Typ 1", Beiträge [% Beitragsabgleich]), 0,25)} (usw.für med, avg und 75.)

Typ 2 entspricht einem begrenzten Prozentsatz (Y) des Gehalts, wobei der Beitrag des Arbeitnehmers nicht begrenzt ist:

{= PERCENTILE.EXC (IF (Beiträge [Beitragstyp] = "Typ 2", Beiträge [% Gehalt]), 0,25)} (usw.für med, avg und 75.)

Für Typ 3 gelten sowohl für den Arbeitnehmerbeitrag als auch für die Gehaltsprozente Grenzen:

{= PERCENTILE.EXC (IF (Beiträge [Beitragstyp] = "Typ 3", Beiträge [% Beitragsabgleich]), 0,25)} (usw.für med, avg und 75.)

{= PERCENTILE.EXC (IF (Beiträge [Beitragstyp] = "Typ 3", Beiträge [% Gehalt]), 0,25)} (usw.für med, avg und 75.)

Die sich ergebende Tabelle berechnet Quartile einschließlich Leer- und Nullwerten (nicht was ich will):

+--------+-----------+--------+---------+------------+-----------+--------+---------+------------+ | 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% | +--------+-----------+--------+---------+------------+-----------+--------+---------+------------+

Ich habe jede Kombination von IF ausprobiert (UND IF (Wenn ich mir vorstellen kann. Ich bin relativ neu mit Array-Formeln, daher wäre jede Hilfe sehr dankbar. Ich bin offen für die Verwendung einer anderen Formel, wenn es eine bessere gibt, aber entmutigt, die Datentabelle nicht neu zu formatieren, um Werte vorzugeben.

0

2 Antworten auf die Frage

0
Rajesh S

Sie können diese Array-Formel anwenden:

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

NB

  1. In Cell F3& müssen G3Sie Criterion speichern, was die Formel dynamischer als den Hardcore macht.
  2. Beenden Sie die Formel mit Ctrl+Shift+Enter.
  3. Passen Sie die Zellverweise in der Formel nach Bedarf an.
Ich danke dir sehr! Das hat perfekt funktioniert! Ich war mir nicht sicher, wie ich das Kriterium "<> 0" in einer separaten Zelle speichern sollte, also endete ich hart in die Formeln. Wissen Sie, ob es tatsächlich eine Möglichkeit gibt, "ungleich Null" dynamisch zu speichern? SuziLmrdo vor 5 Jahren 0
@SuziLmrdo, ich bin froh, Ihnen helfen zu können. Geben Sie einen beliebigen Wert ein, der nicht NULL ist, und verwenden Sie die Zellenadresse <> $ A $ 3. Fragen Sie weiter ☺ Rajesh S vor 5 Jahren 0
0
SuziLmrdo

Vielen Dank an Rajesh S für seine Hilfe bei meiner Frage! Hier sind die Formeln, die ich aufgrund seines Rates gefunden habe:

Prozentsatz des Arbeitnehmerbeitrags - 25. Perzentil (ersetzen Sie 0,25 durch 0,75, um das 75. Perzentil zu berechnen)

{= IFERROR (PERCENTILE.EXC (IF (((Beiträge [Art des Beitrags] = "Typ 1" (Beiträge [% Contribution] <> 0)), Beiträge [% Contribution Match]), 0,25), "N / A ")} {= IFERROR (PERCENTILE.EXC (IF (((Beiträge [Beitragstyp] =" Typ 2 ") (Beiträge [% Contribution Match] <> 0)), Beiträge [% Contribution Match], 0,25), "Nicht zutreffend")}} {= IFERROR (PERCENTILE.EXC (IF (((Beiträge [Beitragstyp] = "Typ 3") * (Beiträge [% Beitragsabgleich] <> 0)), Beiträge [% Beitragsabgleich ]), 0,25), "N / A")}

Prozentsatz des Mitarbeiterbeitrags - Median (MEDIAN durch AVERAGE ersetzen, um Durchschnittswerte zu berechnen)

{= IFERROR (MEDIAN (IF (((Beiträge [Beitragsart] = "Typ 1") (Beiträge [% Beitragsabgleich] <> 0)), Beiträge [% Beitragsabgleich])), "N / A")} {= IFERROR (MEDIAN (IF (((Beitrag [Beitragsart] = "Typ 2") (Beiträge [% Beitragsabgleich] <> 0)), Beiträge [% Beitragsabgleich])), "N / A")} {= IFERROR (MEDIAN (IF (((Beiträge [Beitragstyp] = "Typ 3") * (Beiträge [% Beitragsabgleich] <> 0)), Beiträge [% Beitragsabgleich])), "N / A") }

Um den Prozentsatz des Gehalts zu berechnen - 25., Median, Durchschnitt, 75. - Ersetzen Sie alle Fälle von "% Contribution Match" in den obigen Formeln durch "% of Salary".

Verwandte Probleme