Durchschnitt der mittleren 80% berechnen

[Ludwig.sp]

Hallo Leute,  :32:

ich habe ein Problem mit einer großen Datenmenge. 
Ich versuche zurzeit den Durchschnitt der mittleren 80% von einzelnen Spalten zu berechnen. Dadurch, dass ich so viele Spalten habe, kann ich die Zellen nicht durch die "Filtern"-Anwendung in eine Reihenfolge bringen.

Hier mal ein Beispiel (in Spalte A sind Namen und in Spalte B sind Punktzahlen) ich möchte hierbei die obersten 10% und die untersten 10% der Punktzahlen in der Durchschnittsberechnung nicht berücksichtigen, da diese Aussreißer sind:

      A        B      
1    a        5
2    b        3
3    c        9
4    d       17
5    e       1100
6    f        0
7    g       19
8    h       11
9    i        13
10  j        4


Vielen Dank im Voraus!

LG
Ludwig

[WillWissen]

Hi Ludwig,

Fragen zu Excel bitte auch im Excelforum stellen – das Unterforum 2019 ist für unser Treffen in diesem Jahr reserviert. Ich hab's verschoben.

[Ludwig.sp]

Hi,

tut mir leid.
Habe ich wohl übersehen.
Danke auf jeden Fall fürs Verschieben.

LG
Ludwig

[LCohen]

Nehmen wir mal die 80%:

Welche Werte sollen dann (statt bei 10) bei 9 oder 11 Werten eingeschlossen sein, ggflls anteilig?

[Fennek]

Hallo,

nur als Idee (also ohne Nachbau und ausprobieren)

Mit dem Autofilter to Topt10% und Bottom10% filtern. Dann mit "Aggregate(Mittelwert) nur Sichtbare" rechnen.

mfg

[WillWissen]

Hi Ludwig,

@Fennek: der TE schrieb schon, dass Autofilter nicht möglich sei.

Hier mal eine Lösung mit 4 (ausblendbaren) Hilfsspalten:

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B	C	D	E	F	G	H
1	Namen	Pkte	HS1	HS2			HS3	HS4
2	Name1	710	3	#NV	29,2222222		3312	0
3	Name2	3	#NV	2			1100	3
4	Name3	9	#NV	8			710	4
5	Name4	17	#NV	#NV			424	5
6	Name5	1100	2	#NV			330	6
7	Name6	0	#NV	1			321	7
8	Name7	19	#NV	#NV			220	8
9	Name8	11	#NV	9			122	9
10	Name9	13	#NV	#NV			112	11
11	Name10	14	#NV	#NV			110	12
12	Name11	12	#NV	10
13	Name12	424	4	#NV
14	Name13	24	#NV	#NV
15	Name14	4	#NV	3
16	Name15	5	#NV	4
17	Name16	6	#NV	5
18	Name17	7	#NV	6
19	Name18	8	#NV	7
20	Name19	55	#NV	#NV
21	Name20	66	#NV	#NV
22	Name21	33	#NV	#NV
23	Name22	22	#NV	#NV
24	Name23	12	#NV	10
25	Name24	122	8	#NV
26	Name25	321	6	#NV
27	Name26	112	9	#NV
28	Name27	110	10	#NV
29	Name28	220	7	#NV
30	Name29	330	5	#NV
31	Name30	3312	1	#NV

Zelle	Formel
C2	{=VERGLEICH(B2;$G$2:$G$11;0)}
D2	=VERGLEICH(B2;$H$2:$H$11;0)
E2	=MITTELWERTWENNS($B$2:$B$31;$C$2:$C$31;#NV;$D$2:$D$31;#NV)
G2	=KGRÖSSTE($B$2:$B$31;ZEILE(A1))
H2	=KKLEINSTE($B$2:$B$31;ZEILE(A1))

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Der Matrixabschluss ist nicht nötig, war von anderen Versuchen übrig.

[Cadmus]

Wäre da nicht die Funktion "Gestutztmittel" möglich?
https://support.office.com/de-de/article...d988f511d3

[WillWissen]

Hi Cadmus,

ich hab' mir mal die Fkt. angesehen und ausprobiert (bislang noch nicht benötigt), bin mir aber nicht sicher, ob das Ergebnis wirklich korrekt ist. Hier mal der Vergleich:

Arbeitsblatt mit dem Namen 'Tabelle2'
	E	F
2	165,16	163,23

Zelle	Formel
E2	=RUNDEN(MITTELWERTWENNS($B$2:$B$101;$C$2:$C$101;#NV;$D$2:$D$101;#NV);2)
F2	=RUNDEN(GESTUTZTMITTEL($B$2:$B$101;0,2);2)

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Ich habe, wie vom TE angegeben, 100 Zahlen genommen und entsprechend der Beschreibung von MS 0,2 (20%) eingesetzt. Das Ergebnis siehst du oben, wobei ich der Meinung bin, dass ersteres  das genauere ist.

[Ego]

Hallo Günter,

warum sollte eine Excel-Funktion nicht das tun, was in der Beschreibung steht.

In deinen Beispielzahlen im Beitrag #6 kommt der 10tkleinste Wert zweimal vor. Es werden also 21 Werte (statt 20) nicht berücksichtigt.

[WillWissen]

Hallo Helmut,

ich habe tatsächlich übersehen, dass doppelte Werte vorhanden waren - jetzt passt natürlich alles und der TE müsste glücklich sein.