Ecel-Ausertung: TOP x %

[Andii87]

Hallo,

ich habe folgendes Excel-Problem:

Ich habe ca. 30 Reiter mit jeweils einer unterschiedlichen Anzahl an Zeilen. In jeder Zeile steht eine Zahl.
Nun möchte ich für jeden Reiter den Mittelwert der oberen 20% der Zahlen berechnen (sowie auch der unteren 40% und mittleren 40%).

Beispiel:
2
4
1
4
5
3
6
7
4
3

-> top 20%: 6+7 -> Mittelwert 6,5.
->mittlere 40%: 4+4+4+5 -> 4,25
-> untere 40%: 1+2+3+3 -> 2,25

In einem anderen Reiter sind dann aber z.b. nicht 10 sondern 280 Zeilen, sodass die Formel KGRÖSSTE nicht funktioniert.
 

Wie berechne ich diese Mittelwerte über eine Formel?

Vielen Dank für die Hilfe!
Andi

[WillWissen]

Hi Andi,

ob dein Problem besser mit VBA lösbar wäre, kann ich nicht beurteilen.

Hier mal ein Ansatz mit Formeln - zumindest für die TOP 20% (die Formel berücksichtigt auch mehrfach auftretende Werte). Die Hilfsspalte einfach ausblenden (formatiert als Zahl ohne Dezimalstelle); in B1 gibst du deine gewünschten Prozentzahlen ein (Nur Ziffern - Rest ist Format!!)


Tabelle1 (2)

	A	B	C	D
1	Zahlen	20%	Hilfsspalte	Mittelwert
2	2	6	2	6,5
3	4	7	1
4	1
5	4
6	5
7	3
8	6
9	7
10	4
11	3

Formeln der Tabelle

Zelle Formel B2 =WENNFEHLER(KGRÖSSTE(A:A;C2)/0,00001/100000;"") C2 =ANZAHL(A:A)*$B$1 D2 =MITTELWERT(B2:B500) B3 =WENNFEHLER(KGRÖSSTE(A:A;C3)/0,00001/100000;"") C3 =WENNFEHLER(WENN(C2-1<=0;"";WENN(C2>1;C2-1;""));"")

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[WillWissen]

Hi Andi,

hab' mal weiter getüftelt. Hier die Lösung für TOP 20% und Untere 40%

Tabelle1 (2)

	A	B	C	D	E	F	G
1	Zahlen	TOP 20%	untere 40%	Hilfsspalte	Hilfsspalte	Mittelwert TOP 20%	Mittelwert untere 40%
2	2	6	3	2	4	6,5	2,25
3	4	7	3	1	3
4	1		2		2
5	4		1		1
6	5
7	3
8	6
9	7
10	4
11	3

Formeln der Tabelle

Zelle Formel B2 =WENNFEHLER(KGRÖSSTE(A:A;D2)/0,00001/100000;"") C2 =WENNFEHLER(KKLEINSTE(A:A;E2)/0,00001/100000;"") D2 =ANZAHL(A:A)*$B$1 E2 =ANZAHL(A:A)*$C$1 F2 =MITTELWERT(B2:B500) G2 =MITTELWERT(C2:C500) B3 =WENNFEHLER(KGRÖSSTE(A:A;D3)/0,00001/100000;"") C3 =WENNFEHLER(KKLEINSTE(A:A;E3)/0,00001/100000;"") D3 =WENNFEHLER(WENN(D2-1<=0;"";WENN(D2>1;D2-1;""));"") E3 =WENNFEHLER(WENN(E2-1<=0;"";WENN(E2>1;E2-1;""));"")

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[BoskoBiati]

Hallo,

es gibt doch MITTELWERTWENN(S):

Tabelle1

	A	B
1	2	6,5
2	4	4,25
3	1	2,25
4	4
5	5
6	3
7	6
8	7
9	4
10	3

Formeln der Tabelle

Zelle Formel B1 =MITTELWERTWENN($A$1:$A$10;">"&KGRÖSSTE($A$1:$A$10;KÜRZEN(0,2*ANZAHL(A:A);)+1);$A$1:$A$10) B2 =MITTELWERTWENNS(A:A;A:A;"<"&KGRÖSSTE(A:A;KÜRZEN(0,2*ANZAHL(A:A);));A:A;">="&KGRÖSSTE(A:A;KÜRZEN(0,6*ANZAHL(A:A);))) B3 =MITTELWERTWENN($A$1:$A$10;"<="&KGRÖSSTE($A$1:$A$10;KÜRZEN(0,6*ANZAHL(A:A);)+1);$A$1:$A$10)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[Andii87]

Hallo!

Super, vielen Dank! Ihr habt mir echt den Tag/das Wochenende gerettet :)

[shift-del]

Hallo

Überprüft eure Lösungen doch mal mit 11 Werten statt 10 Werten.

[shift-del]

Tab2HTML will nicht. Also mal mit Jeanie.

Tabelle4

	A	B	C	D	E	F	G
1				11	20%	40%	40%
2				1	3	7	12
3
4					6,5	4,5	2,6
5
6	Werte		Sortierte Werte	Zeilen	20%	40%	40%
7	2		7	1	WAHR	FALSCH	FALSCH
8	4		6	2	WAHR	FALSCH	FALSCH
9	1		5	3	FALSCH	WAHR	FALSCH
10	4		5	4	FALSCH	WAHR	FALSCH
11	5		4	5	FALSCH	WAHR	FALSCH
12	3		4	6	FALSCH	WAHR	FALSCH
13	6		4	7	FALSCH	FALSCH	WAHR
14	7		3	8	FALSCH	FALSCH	WAHR
15	4		3	9	FALSCH	FALSCH	WAHR
16	3		2	10	FALSCH	FALSCH	WAHR
17	5		1	11	FALSCH	FALSCH	WAHR
18				12	FALSCH	FALSCH	FALSCH
19				13	FALSCH	FALSCH	FALSCH
20				14	FALSCH	FALSCH	FALSCH
21				15	FALSCH	FALSCH	FALSCH
22				16	FALSCH	FALSCH	FALSCH

Formeln der Tabelle

Zelle Formel D1 =ANZAHL(C:C) E2 =D2+KÜRZEN($D1*E1;0) F2 =E2+KÜRZEN($D1*F1;0) G2 =D1+1 E4 =MITTELWERTWENNS($C7:$C22;E7:E22;WAHR) F4 =MITTELWERTWENNS($C7:$C22;F7:F22;WAHR) G4 =MITTELWERTWENNS($C7:$C22;G7:G22;WAHR) C7 =WENNFEHLER(KGRÖSSTE(A:A;D7);"") D7 =ZEILEN(D$7:D7) E7 =UND($D7>=D$2;$D7<E$2) F7 =UND($D7>=E$2;$D7<F$2) G7 =UND($D7>=F$2;$D7<G$2)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8