Mittelwert mit mehreren Bedingungen und auslassen von Zellen

[Tebo]

Hallo zusammen, 

ich habe folgendes Problem. 
Ich habe eine Tabelle, die in 3 Bereiche unterteilt ist. In jedem dieser Bereiche gibt es mindestens 3 Zeilen mit einem Dropdown-Menü.

Folgendes kann ausgewählt werden, was dann auch nur in der Zelle steht.
ja
nein 
k.A.
ja* 

Hinter jeder Auswahl steht ein Wert:
ja = 100%
nein = 0%
k.A. = k.A. (leer, wird bei der Berechnung nicht berücksichtigt)
ja* = 125%

Ziel ist es, dass die Zellen alle ausgefüllt werden und sich am Ende aus allen 3 Bereichen ein Mittelwert errechnen lässt. 

Ein zusätzliches Problem ist, dass zwischen den Bereichen noch ein Ergebnis berechnet wird und somit der Zellenbereich unterbrochen ist.

Ich habe schon vieles probiert, aber ich komme einfach nicht auf die Formel, die alles berücksichtigt. 

Ich hoffe jemand hat eine Idee.

Grüße

[shift-del]

Hallo

Code:

=MITTELWERT(C2:C15)

[Tebo]

Ganz so einfach ist es wohl dann doch nicht. 

Die Werte in der Spalte C sind nur Beispielwerte. Das was in B steht ist entscheidend. Doch die Werte in Zeile 5, 10 und 15 dürfen nicht einbezogen werden, da es ja schon ein Ergebnis ist. 

Ich brauche ein Ergebnis für B2:B4, B7:B9; B12:B14 unter der Berücksichtigung, dass verschiedene Bedingungen berücksichtigt werden müssen. 

Ich hoffe ich konnte es etwas genauer beschreiben.

VG

[shift-del]

Dann eben so:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D
1	Bereich 1		Beispiel
2	Thema 1		k.A.
3	Thema 2	nein	0%	0,5
4	Thema 3	ja	100%	1
5	Ergebnis	XX%	XX%
6	Bereich 2
7	Thema 4		k.A.
8	Thema 5		k.A.
9	Thema 6		k.A.
10	Ergebnis	XX%	XX%
11	Bereich 3
12	Thema 7	ja	100%	1
13	Thema 8	nein	0%	0,5
14	Thema 9	ja*	125%	1
15	Ergebnis	XX%	XX%
16
17	Mittelwert	0,8

Zelle	Formel
D2	=WENNFEHLER(SVERWEIS(B2;$I$2:$J$5;2;FALSCH);"")
B17	=MITTELWERT(D2:D15)

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

[Tebo]

Danke! Das ist schon mal super.

Jetzt habe ich nur noch eine Kleinigkeit und zwar bräuchte ich das ohne Hilfsspalte, da die Tabelle noch weitere Spalten (bis zu 20) haben kann, die alle nach dem gleichen Prinzip berechnet werden sollen. 

Eine Formel, die alles vereint wäre perfekt.

Hast du da auch noch eine Lösung?

[shift-del]

Na, dann hättest du eben 20 Hilfsspalten. Das ist gerade mal 1 Promille aller Spalten.
Die Hilfsspalte ermöglicht dir auch eine schnelle Kontrolle.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D
1	Bereich 1		Beispiel
2	Thema 1		k.A.
3	Thema 2	nein	0%	0,5
4	Thema 3	ja*	100%	1
5	Ergebnis	XX%	XX%
6	Bereich 2
7	Thema 4		k.A.
8	Thema 5		k.A.
9	Thema 6		k.A.
10	Ergebnis	XX%	XX%
11	Bereich 3
12	Thema 7	ja	100%	1
13	Thema 8	nein	0%	0,5
14	Thema 9	ja*	125%	1
15	Ergebnis	XX%	XX%
16
17	Mittelwert	0,8
18		0,8

Zelle	Formel
B18	{=MITTELWERT(WENNFEHLER(SVERWEIS(T(WENN({1};B2:B15));$I$2:$J$5;2;FALSCH);""))}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

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

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

[Tebo]

Jetzt habe ich noch eine Frage:

Was ich nicht ganz verstehe ist, warum in Zeile D14 eine 1 steht, obwohl es doch 125% (1,25) sein müssten?


Woran liegt das?

[shift-del]

Das liegt daran dass das Zeichen * ein Wildcard-Zeichen ist.
Und damit findet der SVERWEIS() zuerst das 'ja' in J2.

Pro-Tipp: Verwende statt * besser +.

[shift-del]

Tebo hat private Nachrichten deaktiviert. Du kannst diesem Benutzer keine Nachricht schicken.

Das macht Sinn.
Ich mache sowieso keinen Support via PN.

[Tebo]

Das mit der Formel scheint zu klappen, vielen Dank schon mal. Ich muss das jetzt nur noch auf die gesamte Tabelle ausrollen.

Jetzt stehe ich nur noch vorder Herausforderung, dass ich die Ergebnisse in einem TOP und FLOP Ranking darstellen will. Ausgehend von der gleichen Beispieltabelle (Spalte C). In dieser Spalte wird aus 20 Zellen pro Zeile ein Zeilenergebnis errechnet. Jetzt war meine Überlegung, einfach ein Ranking für die Spalte C zu erstellen, aber dann habe ich immer die Zwischenergebnisse mit im Ranking.

Das Ranking soll wie folgt aufgebaut sein:
TOP
1: Thema 1 125%
2: Thema 2 100%
2: Thema 2 100%
4: Thema 3 80%
5: usw.

Die Herausforderung dabei ist zum einen, dass doppelte Werte berücksichtigt werden müssen und das die Texte korrekt zugeordnet werden. 

Können Sie mir hierzu vielleicht einen Tipp geben?