Excel-Funktion =Mittelwert(B2:B30) erweitern?

W_D_mland · 26. Februar 2012 um 18:55

Hallo,
unter MS Office Excel 2003 (11.8342.8341) SP3 ist folgendes Problem zu lösen:

Aus den Zellen B2 bis B30 sollen nur diejenigen Zellen zur Berechnung des Mittelwertes herangezogen werden, die weder leer sind noch lediglich eine 0 (Null) enthalten. Habt Ihr dazu eine Idee, ich habe in Exel nur marginale Kenntnisse.
mfg Wilhelm

Reinhard_fb8cb7 · 26. Februar 2012 um 19:47

Aus den Zellen B2 bis B30 sollen nur diejenigen Zellen zur
Berechnung des Mittelwertes herangezogen werden, die weder
leer sind noch lediglich eine 0 (Null) enthalten. Habt Ihr
dazu eine Idee, ich habe in Exel nur marginale Kenntnisse.

Hallo Wilhelm,

=SUMME(B2:B30)/ZÄHLENWENN(B2:B30;">0")

Gruß
Reinhard

N_N_a8952a · 27. Februar 2012 um 02:40

Hallo Reinhard,

=SUMME(B2:B30)/ZÄHLENWENN(B2:B30;">0")

exakt auf die gleiche Idee bin ich heute am frühen Abend auch schon gekommen und hatte diese Formel auch in meiner Antwort genannt. Allerdings sind mir später 2 Fehler aufgefallen, deswegen hatte ich meine Antwort wieder gelöscht.

Der erste „Fehler“

Diese Formel erzeugt einen #DIV/0! Fehler wenn in den Zellen B2 bis B30 keine Werte stehen, da die Formel dann versucht Null durch Null zu teilen.

Diese Fehlermeldung habe ich dann mit der folgenden Formel umgehen können.

=WENN(SUMME(B2:B30)>0;SUMME(B2:B30)/ZÄHLENWENN(B2:B30;">0");"")

Nun besteht aber immer noch der zweite „Fehler“:

Es werden nur positive Werte richtig berücksichtigt. Bei negativen Werten stimmt der Durchschnitt nicht mehr.

Bisher ist mir noch keine Formel eingefallen die beide „Fehler“ beseitigt. Vielleicht fällt dir dazu etwas ein.

Gruß
N.N

Thomas_Ramel · 27. Februar 2012 um 13:46

Grüezi NN

Nun besteht aber immer noch der zweite „Fehler“:

Es werden nur positive Werte richtig berücksichtigt. Bei
negativen Werten stimmt der Durchschnitt nicht mehr.

Dann nimm halt statt „>0“ den folgenden Vergleich: "0"

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

N_N_a8952a · 27. Februar 2012 um 15:03

Hallo Thomas,

Dann nimm halt statt „>0“ den folgenden Vergleich: "0"

auf die Idee bin ich auch schon gekommen

Ich kann mir zwar auch nicht erklären wie so, aber bei "0"
werden die leeren Zellen (ohne Werte) mit gezählt, was dann natürlich den Durchschnitt falsch beeinflusst.

Gruß
N.N

Thomas_Ramel · 27. Februar 2012 um 15:34

Grüezi NN

Dann nimm halt statt „>0“ den folgenden Vergleich: "0"

auf die Idee bin ich auch schon gekommen

Ich kann mir zwar auch nicht erklären wie so, aber bei "0"
werden die leeren Zellen (ohne Werte) mit gezählt, was dann
natürlich den Durchschnitt falsch beeinflusst.

…Wo Du Recht hast, hast Du Recht

Dann nimm die folgende Formel die auch korrekt rechnet wenn die Summe negativer und positiver Werte sich mal gerade aufheben sollte:

**=WENN(ANZAHL(B2:B30)=0;"";SUMME(B2:B30)/(ZÄHLENWENN(B2:B30;">0")+ZÄHLENWENN(B2:B30;"

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -**

Reinhard_fb8cb7 · 27. Februar 2012 um 15:54

Mittelwert leere Zellen Nullwerte, negative Zahlen

Dann nimm halt statt „>0“ den folgenden Vergleich: "0"

auf die Idee bin ich auch schon gekommen

Ich kann mir zwar auch nicht erklären wie so, aber bei "0"
werden die leeren Zellen (ohne Werte) mit gezählt, was dann
natürlich den Durchschnitt falsch beeinflusst.

Hallo N.N, Thomas,

wenn ihr magt, schaut mal bitte hier:

http://www.excelformeln.de/formeln.html?welcher=165

Grad auch das was unten bei Erläuterung zu =ZÄHLENWENN(A:A;„0“) steht.
Speziell die angebliche seltsame Reaktion auf Löschen von
Zellinhalten konnte ich bislang nicht nachvollziehen.
Viele Gründe möglich, muß das noch genauer prüfen ob ich es hinkriege daß das Löschen einer Zelle (Inhalt) beim Formelergebnis nix ändert.

Ich kann das mit XL 2000 und XL 2007 selbst austesten.
Wäre mir lieb ihr „übernehmt“ die anderen Versionen

Gruß
Reinhard

Reinhard_fb8cb7 · 27. Februar 2012 um 16:05

Dann nimm die folgende Formel die auch korrekt rechnet wenn
die Summe negativer und positiver Werte sich mal gerade
aufheben sollte:

=WENN(ANZAHL(B2:B30)=0;"";SUMME(B2:B30)/(ZÄHLENWENN(B2:B30;">0"
)+ZÄHLENWENN(B2:B30;"

Grüezi Thomas,

weia, kann doch nicht sein daß man für so ein bißchen Mittelwert im worst case so ein Formelchen basteln muss:

{=INDEX(INDIREKT("’"&INDEX({„EGZ 2011“;„EGZ 2012“;„EGZ Projekt 50plus
2011“;„EGZ Projekt 50plus 2012“;„16e Fälle“};VERGLEICH(1;
(ZÄHLENWENN(INDIREKT("’"&{„EGZ 2011“;„EGZ 2012“;„EGZ Projekt 50plus
2011“;„EGZ Projekt 50plus 2012“;„16e Fälle“}&"’!B:B");
F12)>0)+0;0))&"’!A:A"); VERGLEICH(F12; INDIREKT("’"&INDEX({„EGZ
2011“;„EGZ 2012“;„EGZ Projekt 50plus 2011“;„EGZ Projekt 50plus
2012“;„16e Fälle“};VERGLEICH(1;(ZÄHLENWENN(INDIREKT("’"&{„EGZ
2011“;„EGZ 2012“;„EGZ Projekt 50plus 2011“;„EGZ Projekt 50plus
2012“;„16e Fälle“}&"’!B:B"); F12)>0)+0;0))&"’!B:B"); 0))}

Denn deine Formel versagt wenn Formeln in B2:b30 stehen

Wie einfach wäre die Excel-Formel-Welt wenn die Programmierer
Anzahl(), Zählenwenn() usw. einen parameter mehr gegönnt hätten *seufz*

Gruß
Reinhard

N_N_a8952a · 27. Februar 2012 um 16:20

Hallo Thomas,
hallo Reinhard.

Ich glaube dass ist dann doch zu hoch für mich. Ich hatte ja nur versucht „Wilhelm“ (W. Dömland) zu helfen. Aber dass geht dann doch eindeutig über meine Kenntnisse hinaus. Ich klinke mich dann jetzt hier aus.

Trotzdem vielen Dank

Gruß
N.N

W_D_mland · 27. Februar 2012 um 20:59

Dank u. Frage
Hi,
Dank an alle, die sich meines Problems - mit den Erweiterungen - angenommen haben. Mit Deiner Funktion, Reinhard, war mein Problem bereits gelöst. Frage: Warum aber funktioniert die Funktion mit =Summe…? Ist Summe nicht dass Ergebnis einer Addition, nicht der Berechnung eines Durchschnitts?
mfg Wilhelm

Reinhard_fb8cb7 · 27. Februar 2012 um 21:53

Warum aber
funktioniert die Funktion mit =Summe…? Ist Summe nicht dass
Ergebnis einer Addition, nicht der Berechnung eines
Durchschnitts?

Hallo Wilhelm,

du hast Recht und ich benutze ja die Funktion Summe() nur zur Addition,
Der „normale“ Mittelwert ist dann dies Summe geteilt durch die Anzahl der Summanden.
So sieht man das vielleicht deutlicher:

= SUMME(B2:B30) / ZÄHLENWENN(B2:B30;"\>0")

Gruß
Reinhard

W_D_mland · 27. Februar 2012 um 22:23

Hallo Reinhard,
jetzt ist alles klar! Wie schon gebeichtet: Marginale Kenntnisse.
mfg Wilhelm

N_N_a8952a · 28. Februar 2012 um 03:50

Hallo Reinhard,

du hattest Thomas geantwortet:

Denn deine Formel versagt wenn Formeln in B2:b30 stehen

Eigentlich wollte ich mich ja aus diesem „Artikelbaum“ ausgeklinkt haben. Aber dann habe ich doch noch die Formel von Thomas getestet.

=WENN(ANZAHL(B2:B30)=0;"";SUMME(B2:B30)/(ZÄHLENWENN(B2:B30;">0")+ZÄHLENWENN(B2:B30;"Auch wen Formeln in B2 bis B30 stehen

Zur Kontrolle habe ich die Testtabelle mal hier hoch geladen:

http://www.uploadagent.de/show-182791-1330396831.html

Gruß
N.N

Thomas_Ramel · 28. Februar 2012 um 06:13

Grüezi Reinhard

=WENN(ANZAHL(B2:B30)=0;"";SUMME(B2:B30)/(ZÄHLENWENN(B2:B30;">0"
)+ZÄHLENWENN(B2:B30;"

weia, kann doch nicht sein daß man für so ein bißchen
Mittelwert im worst case so ein Formelchen basteln muss:

{=INDEX(INDIREKT("’"&INDEX({„EGZ 2011“;„EGZ 2012“;„EGZ Projekt
50plus
2011“;„EGZ Projekt 50plus 2012“;„16e Fälle“};VERGLEICH(1;
(ZÄHLENWENN(INDIREKT("’"&{„EGZ 2011“;„EGZ 2012“;„EGZ Projekt
50plus
2011“;„EGZ Projekt 50plus 2012“;„16e Fälle“}&"’!B:B");
F12)>0)+0;0))&"’!A:A"); VERGLEICH(F12;
INDIREKT("’"&INDEX({„EGZ
2011“;„EGZ 2012“;„EGZ Projekt 50plus 2011“;„EGZ Projekt 50plus
2012“;„16e Fälle“};VERGLEICH(1;(ZÄHLENWENN(INDIREKT("’"&{„EGZ
2011“;„EGZ 2012“;„EGZ Projekt 50plus 2011“;„EGZ Projekt 50plus
2012“;„16e Fälle“}&"’!B:B"); F12)>0)+0;0))&"’!B:B"); 0))}

Denn deine Formel versagt wenn Formeln in B2:b30 stehen

Keine Ahnung, Reinhard, welchen Spezialfall Du das konstruiert hast.
Aber mit der eigentlichen Frage hat diese Formel ja wohl nicht mehr viel gemeinsam, oder?
Mir scheint eher, dass damit aus einem Datenstamm ein Wert berechnet werden soll, der abhängig von noch einigen anderen Kriterien ist.

Dafür wäre dann wohl die Pivot-Tabelle besser geeignet…

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Thomas_Ramel · 28. Februar 2012 um 06:16

Grüezi Raihard

Dann nimm halt statt „>0“ den folgenden Vergleich: "0"

Das war ein (leider) ungetesteter Schnellschus, der sich rasch im Sand verlaufen hat…

http://www.excelformeln.de/formeln.html?welcher=165

Grad auch das was unten bei Erläuterung zu
=ZÄHLENWENN(A:A;„0“) steht.
Speziell die angebliche seltsame Reaktion auf Löschen von
Zellinhalten konnte ich bislang nicht nachvollziehen.
Viele Gründe möglich, muß das noch genauer prüfen ob ich es
hinkriege daß das Löschen einer Zelle (Inhalt) beim
Formelergebnis nix ändert.

Dieses eigenartige Verhalten von ZÄHLENWENN() ist schon länger bekannt und ich habs mit xl2003 nachgestellt - es passiert in der Tat nix, wenn Zelleninhalte gelöscht werden.
Die Aussagen bei excelformeln.de sind ernst zu nehmen.

…aber ich habe ja noch eine funktionierende Lösung nachgereicht…

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Reinhard_fb8cb7 · 29. Februar 2012 um 19:38

Verzeiht mir
Hallo N.N und Thomas,

die lange Formel die ich zeigte hat nicht das geringste
mit dieser Beitragsfolge zu tun.
Im Nachhinhein wäre es von mir bedeutend klüger gewesen ich hätte das so formuliert:
„Kann doch wohl nicht sein daß man zur Mittelwertberechnung eine
Formel mit 500 Zeichen braucht“
Nur das wollte ich mit der Formel, die ich kurz zuvor andernorts las als Lösung einer dortigen Anfrage, demonstrieren.

Denn deine Formel versagt wenn Formeln in B2:b30 stehen

Eigentlich wollte ich mich ja aus diesem „Artikelbaum“
ausgeklinkt haben. Aber dann habe ich doch noch die Formel von
Thomas getestet.

Ja, das was ich sagte bezog sich auf „0“.

Mittelwert der Summe von B2 bis B30
Ohne Berücksichtigung von Null Werten
Ohne Berücksichtigung von leeren Zellen
Mit positiven Werten
Mit negativen Werten
Auch wen Formeln in B2 bis B30 stehen
http://www.uploadagent.de/show-182791-1330396831.html

Danke, daß du das getestet hast mit deiner Version.
Das hatte ich mir erhofft.

Gruß
Reinhard