Mittelwert in Zellen mit Formeln

[Leini]

Hallo,

ich habe ein Problem:

Habe eine Tabelle mit etwa 1.000 Zeilen.

Alle Zeilen verweisen auf ein bestimmtes Tabellenblatt (=TabellenblattX!A14).

Dieses andere Tabellenblatt weist nur dann Zahlen auf, wenn bestimmte Parameter erfüllt sind.

Nun will ich in meiner Haupttabelle einen Mittelwert aus den Zellen bilden, bei denen eine Zahl angezeigt wird.

Funktion: =TEILERGEBNIS(1;A7:A1055)

Nun zählt die Teilergebnis-Formel jedoch sämtliche Zeilen von 1 bis 1055 mit und verfälscht somit den Mittelwert.

Ich müsste dem System also jetzt befehlen, "bilde den Mittelwert nur aus den Zellen, in denen Zahlen stehen, und lasse Zellen mit der Formel =TabellenblattX!A14 unberücksichtigt!"

Wie mache ich das?

[WillWissen]

Hallo Leini,

was zeigen den deine Zellen mit Formeln an? Keine Zahlenwerte? Oder Nullwerte? Oder gar nichts?
Hilfreich beim Helfen wäre eine .xlsx, die vom Aufbau (alle relevanten Blätter nebst Formeln) her deinem Original gleicht; sensible Daten bitte anonymisieren.
https://www.clever-excel-forum.de/thread-326.html

[Leini]

Die Zellen zeigen nichts an, weil die Zellen des Tabellenblattes, worauf sie sich beziehen, leer sind.

In den Zellen steht die Formel [=TabellenblattX!A14] und so weiter.

[WillWissen]

Hi,

lies doch bitte nochmals meinen Beitrag (#2) ab dem Wort "Hilfreich".

[schauan]

Hallöchen,

Das Teilergebnis hilft Dir hier nicht weiter, oder hast Du Deinen Tabellenbereich gefiltert? Bei mir mit 2016 wäre der MITTELWERT im Teilergebnis die 101 und nicht die 1

der MITTELWERT - auch im Teilergebnis - berücksichtigt doch schon nur Zahlen.

Bestimmte Zellen kannst Du aber auch mit MITTELWERTWENN ausschließen. Hier mal für leere Zellen, obwohl das ja nicht nötig ist. Texte werden ebenso nicht berücksichtigt. Du könntest damit aber z.B. 0-Werte ausnehmen. Etwas problematischer würde es, wenn Fehlerwerte drin stehen. Stehen welche drin?

Das andere Problem betrifft das Ergebnis Deiner Formel. Excel hat nun mal die Macke, dass in der Ergebniszelle solch einer Formel 0 steht, wenn die Zelle, auf die sich die Formel bezieht, leer ist. Das könntest Du z.B. mit einem =WENN(leer;"";A14) vermeiden.

Ansonsten beißt sich was in Deiner Aufgabenstellung. Ich zitiere mal zwei Sätze:

Alle Zeilen verweisen auf ein bestimmtes Tabellenblatt (=TabellenblattX!A14).
bilde den Mittelwert nur aus den Zellen, in denen Zahlen stehen, und lasse Zellen mit der Formel =TabellenblattX!A14 unberücksichtigt!"

Wenn in allen Zellen die Formel steht, dann brauchst Du keinen Mittelwert, wenn Du alle Zellen mit der Formel dabei nicht berücksichtigen willst...

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B
1	1	2,66666667
2		2,66666667
3	3
4	4

Zelle	Formel
B1	=MITTELWERTWENN(A1:A4;"<>";A1:A4)
B2	=MITTELWERT(A1:A4)

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

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

[Leini]

Ich hatte den "Hilfreich"-Satz durchaus registriert, aber gehofft, es wird auch ohne Muster gehen.

Aber jetzt habe ich doch eins erstellt.

Das Problem ist also, dass das erwünschte Ergebnis in der "Ist"-Tabelle € 1.000,00 lautet.

Das käme nämlich raus, wenn man 350 Eintragungen zu jeweils tausend Euro eben durch 350 teilt.

Offensichtlich teilt das System jedoch durch die Gesamtanzahl der vorhandenen Zellen.

[Hydronaut]

Hallo

Den Sinn des Blattes "IST" habe ich nicht verstanden. Es stehen dort die selben Infos wie auf Blatt "Rohmaterial".
Deshalb:

Code:

=MITTELWERT(Rohmaterial!A3:A1051)

[Leini]

Die beiden Tabellenblätter werden von verschiedenen Abteilungen ausgefüllt, und "Rohmaterial" wird noch für weitere Berechnungen herangezogen.

---

Noch ein Hinweis: Der Code oben klappt zwar - vielen Dank dafür -, aber ich muss mich auf mein aktueller Tabellenblatt beziehen können statt auf die Quelle, weil diese für bestimmte Zwecke noch mehr oder weniger Zahlen tragen wird.

[WillWissen]

Hallo,

Code:

=MITTELWERTWENN(A3:A1051;"<>0")

[Leini]

Das ist es. Tausendundeinendank!!!