Excel: Mittelwert trotz leerer Zellen berechnen

Computer: Software & Programmierung Office Software
#1

Hallo! Ich habe ein Problem mit Excel. Und zwar habe ich eine Tabelle mit Bewertungs- Zahlen, von denen rechts daneben jeweils von den verschiedenen Zeilen der Mittelwert, sowie der Median für jede Zeile berechnet werden soll. Bei den Zeilen, in denen schon Zahlen stehen ist das auch alles kein Problem, allerdings sollen erst mit der Zeit nach und nach die restlichen Zeilen ausgefüllt werden, z.B. erst in 3 Wochen die nächste Zeile. Außerdem möchte ich jeweils von den berechneten Mittelwerten und Medianen wiederrum einen Mittelwert bilden. Dort erscheint aber nur #DIV/0! bzw. #ZAHL!, da ja noch einige Zeilen keine Zahlen enthalten. Die Tabelle sieht nun ungefähr wie folgt aus:

Frage 1 | Frage 2 | Frage 3 | Frage 4 || Mittelwert || Median

     4       |      4      |      3       |      4      ||     3,75     ||      4

     2       |      3      |      2       |      3      ||       2,5     ||     2,5

              |              |               |              ||   #DIV/0!  || #ZAHL!

              |              |               |              ||   #DIV/0!  || #ZAHL!
-----------------------------------------------=================
                                                           ||   #DIV/0!  || #ZAHL!

Die unterste Zeile ist der Mittelwert der Mittelwerte/ Mediane. Die 3. und 4. Zeile sind noch nicht ausgefüllt, werden es später aber mal.
Gibt es die Möglichkeit, dass Excel JETZT mit den nur zur Zeit vorhandenen Zahlen rechnet, aber SPÄTER, wenn die nächsten Zahlen eingetragen sind, diese Zahlen automatisch mitberücksichtigt und diese miteinberechnet werden?
Danke!
Julia

#2

Hallo Julia

Mit welcher Excel-Version arbeitest Du?

Du möchtest in E6 den Mittelwert von E2:E5 berechnen. Eine Formel dafür wäre:
=MITTELWERTWENN(E2:E5;"#div/0!")

In F6 möchtest Du den Median von F2:F5 berechnen. Eine Formel dafür:
=MEDIAN(WENN(ISTFEHLER(F2:F5);"";F2:F5)).
ACHTUNG: Diese Formel musst Du abschliessen nicht nur mit der Enter-Taste, sondern mit SHIFT-ENTER. Sie ist dann mit diesen Klammern „{ }“ abgeschlossen, die man aber nicht von Hand so eingeben darf.

Analog zu dieser Median-Formel kann man auch die Formel =MITTELWERT() in E6 benutzen.

Ich hoffe, die Formeln funken auch bei Dir und grüsse Dich
Niclaus

#3

Gibt es die Möglichkeit, dass Excel JETZT mit den nur zur Zeit
vorhandenen Zahlen rechnet, aber SPÄTER, wenn die nächsten
Zahlen eingetragen sind, diese Zahlen automatisch
mitberücksichtigt und diese miteinberechnet werden?

Hallo Julia,

Mittelwert ist doch Summe der Werte durch Anzahl an Werten. Rechne doch so.
So z.B.:
=Summe(A1:smiley:1)/Anzahl(A1:smiley:1)
von mir aus auch Anzahl2(A1:smiley:1) oder Zählenwenn A1:smiley:1 ungleich leer oder so.

Bei Median haste mich erwischt *lächel*, dachte das ist wie Mittelwert :frowning: Okay, scheinbar nicht aber evtl. kann man den ähnlich selbst berechnen.
Warte halt mal ab bis sich meine hiesigen Helferkollegen melden zu Mittelwert und median.

Gruß
Reinhard

#4

=MEDIAN(WENN(ISTFEHLER(F2:F5);"";F2:F5)).
ACHTUNG: Diese Formel musst Du abschliessen nicht nur mit der
Enter-Taste, sondern mit SHIFT-ENTER. Sie ist dann mit diesen
Klammern „{ }“ abgeschlossen, die man aber nicht von Hand so
eingeben darf.

Die Formel muss mit CTRL-SHIFT-ENTER abgeschlossen werden, nicht nur mit SHIFT-ENTER.

Entschuldigung Niclaus

#5

Haallo,
nicht probiert, aber was passiert, wenn in den leeren Zellen „kein Wert“ drinsteht no value, in excel uebersetzt =nv()
gruss Helmut

#6

Vielen, vielen Dank!! So hat es funktioniert!
Das mit dem abschließen war nicht so schlimm, da ich auch von den Medianen einen Mittelwert haben wollte und deshalb die andere Formel benutzt habe.
Super! :smile:

#7

Hallo Reinhard,

Bei Median haste mich erwischt *lächel*, dachte das ist wie
Mittelwert :frowning: Okay, scheinbar nicht aber evtl. kann man den
ähnlich selbst berechnen.

Wikipedia sagt zum Median:

_Der Median teilt eine Liste von Werten in zwei Hälften. Er kann auf folgende Weise bestimmt werden:

Alle Werte werden (aufsteigend) geordnet.
Wenn die Anzahl der Werte ungerade ist, ist die mittlere Zahl der Median.
Wenn die Anzahl der Werte gerade ist, wird der Median meist als arithmetisches Mittel der beiden mittleren Zahlen definiert, die dann Unter- und Obermedian heißen._

Mach da jetzt mal 'ne Formel draus.

Hab leider gerade nicht viel Zeit.

Gruß
Reinhard

Gruß, Andreas

#8

_Der Median teilt eine Liste von Werten in zwei Hälften. Er
kann auf folgende Weise bestimmt werden:

Alle Werte werden (aufsteigend) geordnet.
Wenn die Anzahl der Werte ungerade ist, ist die mittlere Zahl
der Median.
Wenn die Anzahl der Werte gerade ist, wird der Median meist
als arithmetisches Mittel der beiden mittleren Zahlen
definiert, die dann Unter- und Obermedian heißen._

Mach da jetzt mal 'ne Formel draus.
Hab leider gerade nicht viel Zeit.

Hallo Andreas
Da brauchst Du gar nicht viel Zeit, um die Formel daraus zu machen:
Angenommen, Du suchst den Median aller Zahlen in der Spalte A, dann heisst die Formel gemäss der obigen Definition und Beschreibung ganz einfach:

=MITTELWERT(KGRÖSSTE(A:A;ANZAHL(A:A)/2);KGRÖSSTE(A:A;ANZAHL(A:A)/2+ISTGERADE(ANZAHL(A:A))))

Oder eben noch viel einfacher = MEDIAN(A:A)

Viele Grüsse von einem pensionierten, deshalb etwas mehr Zeit habenden
Niclaus

#9

Hallo Niclaus,

danke für die Formel. Ich war heute den ganzen Tag unterwegs, aber die Sache hatte mich nicht mehr losgelassen. Und irgendwie hatte sich in meinem Kopf die Ahnung verdichtet, dass es was mit KGRÖSSTE und ANZAHL sein müsste. Du hast es mir jetzt sehr schön bestätigt.
Dass es die Funktion MEDIAN gibt, weiß ich. Aber Reinhard meinte ja, man könne es irgendwie ohne hinkriegen. qed.
Noch mal danke und einen schönen Abend.
Gruß, Andreas

#10

Aber Reinhard meinte ja, man könne es irgendwie ohne hinkriegen. qed.

Hallo Andreas,

ja, das hatte ich geschrieben aber dabei dein Definitionszitat von Median nur flüchtig überflogen, noch dazu mit Vba (eigene Funktion) als Option im Kopf.
Stutzig hat mich das „meist“ in diesem Satzteil:
„…wird der Median meist als arithmetisches Mittel…“

Aber ich glaube, nach meinen Internetrecherchen, das „meist“ hat keine Bedeutung. Oder übersehe/übersah ich da irgendwas?

Gruß
Reinhard

#11

Stutzig hat mich das „meist“ in diesem Satzteil:
„…wird der Median meist als arithmetisches Mittel…“
Aber ich glaube, nach meinen Internetrecherchen, das „meist“
hat keine Bedeutung. Oder übersehe/übersah ich da irgendwas?

Grüezi Reinhard

Es geht ja um die Frage, wie man vorzugehen hat bei einer geraden Anzahl von Werten. Ich habe in meinen vergilbten Statistikbüchern nachgeschaut. So wie ich es sehe, war man sich über das „meist“ nie einig.

Prof. Dr. Robert Ineichen schreibt in seinem Lehrbuch (erschienen 1973): „… den Durchschnitt der beiden Werte in der Mitte bei gerader Zahl der Stichprobenwerte“. Er hat also die Excel-Formel =MEDIAN() vorausgeahnt.

Dem gegenüber doziert Prof. Dr.-Ing. Hans Kellerer (1972): „Ist N eine gerade Zahl, so nehmen wir als Zentralwert den Merkmalswert des Elements mit der Rangnummer N/2 in der der Grösse nach geordneten Reihe.“ Nach ihm wäre also der Median (= Zentralwert) der Werte in Spalte A als Excel-Formel:
=KGRÖSSTE(A:A;ANZAHL(A:A)/2+ISTGERADE(ANZAHL(A:A)))

Für mich ist Kellerer „logischer“. Für mich - als Schweizer - ist das Standardbeispiel für den Median die Einkommensverteilung. Und da gilt: Man hat, was man hat. Man hat nicht die Hälfte vom eigenen Einkommen und die Hälfte des Einkommens eines andern.

So oder so gilt: „Traue nie einer Statistik, die Du nicht selber gefälscht hast.“

Viele Grüsse
Niclaus

#12

Grüezie Niclaus,

Stutzig hat mich das „meist“ in diesem Satzteil:
„…wird der Median meist als arithmetisches Mittel…“
Aber ich glaube, nach meinen Internetrecherchen, das „meist“
hat keine Bedeutung. Oder übersehe/übersah ich da irgendwas?

Für mich ist Kellerer „logischer“. Für mich - als Schweizer -
ist das Standardbeispiel für den Median die
Einkommensverteilung. Und da gilt: Man hat, was man hat. Man
hat nicht die Hälfte vom eigenen Einkommen und die Hälfte des
Einkommens eines andern.

aha, scheinbar habe ich nur neuere Definitionen vom Median gefunden und da steht nur noch bei gerader Anzahl:
der Median ist das Mittel der beiden mittleren Werte.

So oder so gilt: „Traue nie einer Statistik, die Du nicht
selber gefälscht hast.“

jepp :smile:

Grad gestern was gelesen. Weiß gar nicht mehr um was es ging, bestimmtes Obst, Medikament o.ä. Jedenfalls wirds beworben mit „30% weniger Herzinfarkttote“ bei benutzung/Gebrauch"

Die Wahrheit ist, es gab mal zwei Vergleichsgruppen (menschliche Laborratten), in der Gruppe mit dem medikament starben 7, in der Vergleichsgruppe 9. daraus wurden die 30% berechnet. Ist natürlich sehr in der Grauzone zur Fälschung.

Gruß
Reinhard