Mittelwert, Extreme & Gewichtung

[fritzfridolin]

Hallo zusammen,

für eine Auswertung muss ich folgendes Szenario darstellen und würde mich sehr über Hilfestellungen oder einen Schubs in die richtige Richtung freuen:

Ich habe eine Anzahl von 30 Werten, die sich in zwei Gruppen unterteilen (10 und 20 Werte). Aus diesen muss ein Mittelwert gebildet werden, und zwar mit folgenden Einschränkungen:

- die Extreme (Minimal- und Maximalwert) sollen vorab eliminiert werden
- pro Gruppe soll ein eigener Mittelwert als Zwischenwert ermittelt werden
- anschließend soll das Gesamtmittel festgestellt werden; hierbei sollen beide Gruppen unabhängig von der Anzahl der darin enthaltenen Werte jeweils zu 50% gewichtet werden

Die Funktion SUMMENPRODUKT ist mir bekannt; allerdings fehlt mir im Kopf irgendwie der Schritt zwischen der Extremeliminierung und der Berechnung des Mittelwerts auf Gruppenbasis. Wenn mich jemand in die richtige Richtung schubsen könnte, wäre das enorm hilfreich!

[LCohen]

B1: =MITTELWERT(KKLEINSTE(A01:A10;ZEILE(02:09)))
B2: =MITTELWERT(KKLEINSTE(A11:A30;ZEILE(02:19)))
B3: =MITTELWERT(B1:B2)

Matrixformeln kennst Du.

[Fred11]

Hallo fritzfridolin,

oder mit der Funktion MITTELWERTWENNS.
In Kombination mit MIN und MAX kann man damit den Mittelwert berechnen, ohne den größten und den kleinsten Wert zu berücksichtigen

=MITTELWERTWENNS(Bereich;Bereich;"<"&MAX(Bereich);Bereich;">"&MIN(Bereich))

siehe hier:
https://www.youtube.com/watch?v=Lo7l6ltWT5Q

Gruß
Fred

[LCohen]

Das entspricht aber dann nicht der Eliminierung à la Skispringer-Wertung. Denn wenn MAX oder MIN mehrfach vorhanden ist, unterscheiden sich unsere Ergebnisse. TE muss sich entscheiden.

Du beziehst Dich übrigens 5 Mal auf Bereich, ich nur 2 Mal (da ZEILE damit zusammenhängt).

[Fred11]

@LCohen,
ist halt ohne Matrixformel...
Deine Formel finde ich echt clever, und sie ist natürlich deutlich kürzer als die mit MITTELWERWENNS.
Und wenn die Extremwerte doppelt vorhanden sein können, muss man entscheiden, wie man damit umgehen will...

[LCohen]

Matrixformel: Ist out. Und wo noch "in", dann ohne: 

Ranke ... INDEX(bisher;) ... um den Teil, der die Matrix darstellt. Ich kann es nicht mehr testen, da 365 sowieso alles als Matrixformel umsetzt, wo nötig.

Daher habe ich schon vergessen, ob

=MITTELWERT(KKLEINSTE(INDEX(A01:A10;);ZEILE(02:09)))
oder
=MITTELWERT(INDEX(KKLEINSTE(A01:A10;ZEILE(02:09));))

das richtige Umranken ist.

Bei Dir ergibt z.B. 1-1-1-1-1-1-2-2-2-2 einen Fehler (keine Werte), bei mir 1,375 (statt 1,4 ohne Randausschluss der 2 Extremwerte).

[RPP63]

Außer Konkurrenz eine Lösung für ein aktuelles Excel:
=MITTELWERT(INDEX(SORTIEREN(A1:A10);SEQUENZ(8;;2)))

[Fred11]

@LCohen

ja, da INDEX prima eine Matrix handeln kann, funktioniert es damit (im "alten" Excel), auch ohne die Formel als Matrixformel einzugeben.

Die Formel sieht dann so aus (wenn die Zahlen, die ausgewertet sollen in A1:A10 stehen):
=MITTELWERT(INDEX(KKLEINSTE(A1:A10;ZEILE(2:9));0))

Bei Dir ergibt z.B. 1-1-1-1-1-1-2-2-2-2 einen Fehler (keine Werte), bei mir 1,375 (statt 1,4 ohne Randausschluss der 2 Extremwerte).

na ja, das ist jetzt auch ein Extrembeispiel.
Aber stimmt, deine Formel liefert selbst da noch ein Ergebnis
(wobei da aber die Frage wäre, inwiefern dieses Ergebnis von 1,375 dann noch als "richtig"/sinnvoll bezeichnet werden kann ….)


Gruß
Fred

[Cadmus]

Hallo,
zur Rausrechnung der Extremwerte ginge auch

=GESTUTZTMITTEL(A1:A10;0,2)

Ergibt ebenfalls 1,375

[LCohen]

Stimmt, hätte mir auch einfallen müssen:

=GESTUTZTMITTEL(A1:A10;2/ZEILEN(A1:A10)) ist dann gleich die allgemeingültige Variante dazu. Oder:

=GESTUTZTMITTEL(A1:INDEX(A:A;ANZAHL(A:A));2/ANZAHL(A:A)), oder

=GESTUTZTMITTEL(A1:INDEX(A:A;X1);2/X1), wenn in X1 die Anzahl der eingehenden Werte steht, oder

=GESTUTZTMITTEL(A1#;2/ANZAHL(A1#)) beim dynamischen Array.