HERBERS Excel-Forum - das Archiv
Mittelwert ohne kleinste + größte Zahl
Rainer

Hallo,
möchte einen Mittelwert ohne die kleinste und größte Zahl in der Matrix zu berücksichtigen.
Hatte zuerst an MITTELWERT(KKLEINSTE(M66:EL66;2);KGRÖSSTE(M66:EL66;2))
gedacht, klappt aber nicht, da auch doppelte Werte vorhanden sind.
Dabei wäre mir die Funktion incl. KKleinste bzw. KGrösste am liebsten, da es auch mal sein kann, dass ich noch die zweitgrößte Zahl ausschließen möchte.
Folglich liegt mein Problem im DOPPEL der Zahlen.
Gruß Rainer

AW: Mittelwert ohne kleinste + größte Zahl
Sepp

Hallo Rainer,
so?
Tabelle1

 ABC
115  
221  
316  
424  
522  
612  
75  
816  
917  
1010  
115  
1215  
1321  
1424  
1524  
169  
175  
1821  
1921  
2025  
2112  
226  
2324  
2420  
2525  
2613  
2713 17,0909091

Formeln der Tabelle
ZelleFormel
C27{=MITTELWERT(WENN((A1:A27>KKLEINSTE(A1:A27;1))*(A1:A27<KGRÖSSTE(A1:A27;1)); A1:A27))}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Sepp

Dein Ansatz wäre aber nicht der Mittelwert
WF

... der Zahlenfolge sondern nur der zwischen 2 Zahlen.
Das Mittel aus der zweitkleinsten vorkommenden Zahl und der zweitgrößten vorkommenden:
folgende Arrayformel:
{=(MIN(WENN(A1:A9>MIN(A1:A9);A1:A9))+MAX((WENN(A1:A19 WF

da wurde was verschluckt
WF

{=(MIN(WENN(A1:A9>MIN(A1:A9);A1:A9))+MAX((WENN(A1:A19<MAX(A1:A19);A1:A19))))/2}

AW: Mittelwert ohne kleinste + größte Zahl
Besserwisser

Hallo Rainer,
vielleicht hilft dir auch Gestutztmittel, beiliegende Formel schließt 2 Datenpunkte aus (den grössten und den kleinsten Datenpunkt), jedoch nur 1x. Kommt also die Kleinste Zahl 3x vor, wird Sie nur 1x ausgeschlossen und fließt noch 2x in die Mittelwertberechnung mit ein.
=GESTUTZTMITTEL(A1:A26;2/ANZAHL(A1:A26))
Gruß
Christian

Pardon, hatte unterbrechen müssen! Gruß owT
Luc:-?

:-?

AW: Pardon, hatte unterbrechen müssen! Gruß owT
Rainer

Hallo,
danke für Eure Mithilfe.
Soll kein mathematisches Ergebnis, sondern eher eine Entscheidungshilfe liefern, daher muss ich im praktischen Einsatz testen, welche Formal am sinnvollsten ist.
Da Werte von 7.500 bis 1.000.000 dabei sind und das Mittel ohne die "Ausreißer" bei 30.000 liegt wollte ich schauen, ob ich das per Formel erledigt bekomme.
Die Formel von WF hätte den Vorteil, dass ab der ersten Eingabe ein Ergebnis geliefert wird, was aber bei der Anzahl der Werte nicht ganz so relevant scheint.
Gruß Rainer

Mittel ist Mittel und ein Mittel ohne Ausreißer...
Luc:-?

…halt ein gestutztes Mittel, Rainer.
Luc :-?

Normalerweise wäre das GESTUTZTMITTEL,...
Luc:-?

…womit du bei Wahl des richtigen Arg2 das gleiche Ergebnis wie Sepp erhalten würdest, Rainer:
=GESTUTZTMITTEL(A1:A10;2/ANZAHL(A1:A10)) hier für ungleiche Werte in A1:A10
Dabei ist es egal, ob Werte doppelt vorkommen, es wdn stets soviele Werte weggelassen wie der %Satz in Arg2 angibt, gleichmäßig (nach Möglichkeit → nachlesen!) aufgeteilt auf untere und obere Extrema.
Bei doppelten Werten, in meinem Test doppelt auftretender MaximalWert, liefert Sepps Fml ein anderes Ergebnis, entfernt also mehr Werte als nur 2.
WFs Fml nähert sich in diesem Fall dem Ergebnis von GESTUTZTMITTEL an, ohne Doppelwerte ist es völlig anders.
GESTUTZTMITTEL geht hierbei analog folgender Fml vor:
=SUMME(A1:A10;-MIN(A1:A10);-MAX(A1:A10))/(ANZAHL(A1:A10)-2)
Willst du mehr entfernen, musst du mal mit anderen %Sätzen experimentieren und das Ergebnis mit einer auflösenden Fml wie meiner 2. vgln.
Gruß, Luc :-?
Besser informiert mit …

Ergänzung
Luc:-?

Ich gehe davon aus, dass du tatsächlich Mittelwerte über alle Werte ohne bestimmte Ausreißer bilden willst, Rainer;
das leistet WFs Fml nicht, denn sie bildet quasi den Mittelwert aus dem Minimum der Werte, die >Min aller Werte sind, und dem Max der Werte, die <Max aller Werte sind. Das ist etwas völlig anderes als du anscheinend beabsichtigst.
Wenn du tatsächlich alle Extrema entfernen willst, egal ob mehrfach vorhanden oder nicht, kannst du Sepps Fml (ggf nach Bedarf erweitert) verwenden. Willst du aber eine bestimmte Anzahl von Extrema entfernen, wobei ggf auch einige diesen entsprd Werte in der Berechnung verbleiben sollen, wäre im Prinzip ein gestutztes Mittel die richtige Wahl. Allerdings arbeitet die Fkt symmetrisch, d.h., es wdn immer gleichviel untere und obere Ausreißer entfernt.
Falls dir die symmetrische Arbeitsweise von GESTUTZTMITTEL nicht gefällt und ein asymmetrisches Arbeiten mathematisch-statistisch nicht völlig unsinnig wäre, hättest du mit folgender MatrixFml Wahlfreiheit:
{=SUMME(A1:A10;-ABRUNDEN(KKLEINSTE(KKLEINSTE(A1:A10;ZEILE(A1:A10))+(ZEILE(A1:A10)-1) /1000;ZEILE(1:1));2);
-ABRUNDEN(KGRÖSSTE(KGRÖSSTE(A1:A10;ZEILE(A1:A10))+(ZEILE(A1:A10)-1)/1000;ZEILE(1:2));2))/(ANZAHL(A1:A10)-3)}
Die schwarzen Adressen müssen auf deinen ZellBereich angepasst wdn. Die blauen je nach TestBedarf. Deren Voreinstellung geht von Zahlen mit 2 Dezimalen aus, denen zeitweise eine 3., eine ZeilenNr, hinzugefügt wird. Für jede ursprünglich vorhandene Dezimale mehr und jede Zeilen­Anzahl­Potenz mehr (hier 10, also 11, 101 usw) muss der Divisor 1000 um eine 0-Stelle verlängert wdn. Der Wiedergabe­Bereich des jeweils finalen KKLEINSTE und KGRÖSSTE kann nach Bedarf gewählt wdn, ZEILE(…), hier für 1 kleinsten und 2 größte Werte (die original auch gleich sein können → wdn durch den Zusatz der ZeilenNr ungleich!) vorgesehen. Die Gesamt­Anzahl dieser entfernten Werte muss dann noch im abschließenden Divisor berücksichtigt wdn (hier 3). Es muss natürlich zwischendurch auf die Original­Dezimalen­zahl abgerundet wdn (hier 2).
Luc :-?

AW: Ergänzung
Rainer

Hallo Luc:-?
vielen Dank für die ausführliche Darstellung.
Es ist tatsächlich so, dass ich u. U. Ausreißer in nur eine Richtung habe.
Da ich den Endwert jedoch noch Runde und etliche Werte in die Formel fallen, fällt dies im Ergebnis akutell nicht ins Gewicht.
Da die Formel aber bei vielen Auswertungen erforlderlich ist, ist u.U. auch mal eine Alternative erforderlich.
Gruß Rainer

Dann ist das jetzt wohl erledigt?! ;-) owT
Luc:-?

:-?

Mittelwert ohne kleinste + größte Zahl
Rainer

Hallo,
möchte einen Mittelwert ohne die kleinste und größte Zahl in der Matrix zu berücksichtigen.
Hatte zuerst an MITTELWERT(KKLEINSTE(M66:EL66;2);KGRÖSSTE(M66:EL66;2))
gedacht, klappt aber nicht, da auch doppelte Werte vorhanden sind.
Dabei wäre mir die Funktion incl. KKleinste bzw. KGrösste am liebsten, da es auch mal sein kann, dass ich noch die zweitgrößte Zahl ausschließen möchte.
Folglich liegt mein Problem im DOPPEL der Zahlen.
Gruß Rainer

AW: Mittelwert ohne kleinste + größte Zahl
Sepp

Hallo Rainer,
so?
Tabelle1

 ABC
115  
221  
316  
424  
522  
612  
75  
816  
917  
1010  
115  
1215  
1321  
1424  
1524  
169  
175  
1821  
1921  
2025  
2112  
226  
2324  
2420  
2525  
2613  
2713 17,0909091

Formeln der Tabelle
ZelleFormel
C27{=MITTELWERT(WENN((A1:A27>KKLEINSTE(A1:A27;1))*(A1:A27<KGRÖSSTE(A1:A27;1)); A1:A27))}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Sepp

Dein Ansatz wäre aber nicht der Mittelwert
WF

... der Zahlenfolge sondern nur der zwischen 2 Zahlen.
Das Mittel aus der zweitkleinsten vorkommenden Zahl und der zweitgrößten vorkommenden:
folgende Arrayformel:
{=(MIN(WENN(A1:A9>MIN(A1:A9);A1:A9))+MAX((WENN(A1:A19 WF

da wurde was verschluckt
WF

{=(MIN(WENN(A1:A9>MIN(A1:A9);A1:A9))+MAX((WENN(A1:A19<MAX(A1:A19);A1:A19))))/2}

AW: Mittelwert ohne kleinste + größte Zahl
Besserwisser

Hallo Rainer,
vielleicht hilft dir auch Gestutztmittel, beiliegende Formel schließt 2 Datenpunkte aus (den grössten und den kleinsten Datenpunkt), jedoch nur 1x. Kommt also die Kleinste Zahl 3x vor, wird Sie nur 1x ausgeschlossen und fließt noch 2x in die Mittelwertberechnung mit ein.
=GESTUTZTMITTEL(A1:A26;2/ANZAHL(A1:A26))
Gruß
Christian

Pardon, hatte unterbrechen müssen! Gruß owT
Luc:-?

:-?

AW: Pardon, hatte unterbrechen müssen! Gruß owT
Rainer

Hallo,
danke für Eure Mithilfe.
Soll kein mathematisches Ergebnis, sondern eher eine Entscheidungshilfe liefern, daher muss ich im praktischen Einsatz testen, welche Formal am sinnvollsten ist.
Da Werte von 7.500 bis 1.000.000 dabei sind und das Mittel ohne die "Ausreißer" bei 30.000 liegt wollte ich schauen, ob ich das per Formel erledigt bekomme.
Die Formel von WF hätte den Vorteil, dass ab der ersten Eingabe ein Ergebnis geliefert wird, was aber bei der Anzahl der Werte nicht ganz so relevant scheint.
Gruß Rainer

Mittel ist Mittel und ein Mittel ohne Ausreißer...
Luc:-?

…halt ein gestutztes Mittel, Rainer.
Luc :-?

Normalerweise wäre das GESTUTZTMITTEL,...
Luc:-?

…womit du bei Wahl des richtigen Arg2 das gleiche Ergebnis wie Sepp erhalten würdest, Rainer:
=GESTUTZTMITTEL(A1:A10;2/ANZAHL(A1:A10)) hier für ungleiche Werte in A1:A10
Dabei ist es egal, ob Werte doppelt vorkommen, es wdn stets soviele Werte weggelassen wie der %Satz in Arg2 angibt, gleichmäßig (nach Möglichkeit → nachlesen!) aufgeteilt auf untere und obere Extrema.
Bei doppelten Werten, in meinem Test doppelt auftretender MaximalWert, liefert Sepps Fml ein anderes Ergebnis, entfernt also mehr Werte als nur 2.
WFs Fml nähert sich in diesem Fall dem Ergebnis von GESTUTZTMITTEL an, ohne Doppelwerte ist es völlig anders.
GESTUTZTMITTEL geht hierbei analog folgender Fml vor:
=SUMME(A1:A10;-MIN(A1:A10);-MAX(A1:A10))/(ANZAHL(A1:A10)-2)
Willst du mehr entfernen, musst du mal mit anderen %Sätzen experimentieren und das Ergebnis mit einer auflösenden Fml wie meiner 2. vgln.
Gruß, Luc :-?
Besser informiert mit …

Ergänzung
Luc:-?

Ich gehe davon aus, dass du tatsächlich Mittelwerte über alle Werte ohne bestimmte Ausreißer bilden willst, Rainer;
das leistet WFs Fml nicht, denn sie bildet quasi den Mittelwert aus dem Minimum der Werte, die >Min aller Werte sind, und dem Max der Werte, die <Max aller Werte sind. Das ist etwas völlig anderes als du anscheinend beabsichtigst.
Wenn du tatsächlich alle Extrema entfernen willst, egal ob mehrfach vorhanden oder nicht, kannst du Sepps Fml (ggf nach Bedarf erweitert) verwenden. Willst du aber eine bestimmte Anzahl von Extrema entfernen, wobei ggf auch einige diesen entsprd Werte in der Berechnung verbleiben sollen, wäre im Prinzip ein gestutztes Mittel die richtige Wahl. Allerdings arbeitet die Fkt symmetrisch, d.h., es wdn immer gleichviel untere und obere Ausreißer entfernt.
Falls dir die symmetrische Arbeitsweise von GESTUTZTMITTEL nicht gefällt und ein asymmetrisches Arbeiten mathematisch-statistisch nicht völlig unsinnig wäre, hättest du mit folgender MatrixFml Wahlfreiheit:
{=SUMME(A1:A10;-ABRUNDEN(KKLEINSTE(KKLEINSTE(A1:A10;ZEILE(A1:A10))+(ZEILE(A1:A10)-1) /1000;ZEILE(1:1));2);
-ABRUNDEN(KGRÖSSTE(KGRÖSSTE(A1:A10;ZEILE(A1:A10))+(ZEILE(A1:A10)-1)/1000;ZEILE(1:2));2))/(ANZAHL(A1:A10)-3)}
Die schwarzen Adressen müssen auf deinen ZellBereich angepasst wdn. Die blauen je nach TestBedarf. Deren Voreinstellung geht von Zahlen mit 2 Dezimalen aus, denen zeitweise eine 3., eine ZeilenNr, hinzugefügt wird. Für jede ursprünglich vorhandene Dezimale mehr und jede Zeilen­Anzahl­Potenz mehr (hier 10, also 11, 101 usw) muss der Divisor 1000 um eine 0-Stelle verlängert wdn. Der Wiedergabe­Bereich des jeweils finalen KKLEINSTE und KGRÖSSTE kann nach Bedarf gewählt wdn, ZEILE(…), hier für 1 kleinsten und 2 größte Werte (die original auch gleich sein können → wdn durch den Zusatz der ZeilenNr ungleich!) vorgesehen. Die Gesamt­Anzahl dieser entfernten Werte muss dann noch im abschließenden Divisor berücksichtigt wdn (hier 3). Es muss natürlich zwischendurch auf die Original­Dezimalen­zahl abgerundet wdn (hier 2).
Luc :-?

AW: Ergänzung
Rainer

Hallo Luc:-?
vielen Dank für die ausführliche Darstellung.
Es ist tatsächlich so, dass ich u. U. Ausreißer in nur eine Richtung habe.
Da ich den Endwert jedoch noch Runde und etliche Werte in die Formel fallen, fällt dies im Ergebnis akutell nicht ins Gewicht.
Da die Formel aber bei vielen Auswertungen erforlderlich ist, ist u.U. auch mal eine Alternative erforderlich.
Gruß Rainer

Dann ist das jetzt wohl erledigt?! ;-) owT
Luc:-?

:-?

Bewerten Sie hier bitte das Excel-Portal