Mittelwert von zellen errechnen aber " Null " und Buchstaben nicht mitzählen geschlossen 

Status
Für weitere Antworten geschlossen.

Gollom

Registered +
Registriert
Dez. 2006
Beiträge
601
Hi alle,

gibt es eine Excelformel, die einen Mittelwert von Zellen errechnet, wobei die Nullen und Buchstaben nicht mitberechnet werden?

Habe sie heraus bekommen

geschweifte Klammer=MITTELWERT(WENN(M7:M9>0;M7:M9))geschweifte Klammer

Aber wenn die Zellen M7,M8 und M9 nur "0" als Wert haben, dann soll er mir in Zelle M10 auch "0" anzeigen und nicht #DIV/0!

Wie können beide Varianten in eine Formel gepackt werden?



MfG


Gollom
 
Zuletzt bearbeitet von einem Moderator:
Hallo Gollom,

alle Varianten in einer Formel geht so:
b47ad3bab0d0788c5da1aa61f.jpg


{=MITTELWERT(WENN(A1:A1000<>0;A1:A1000))}
d.h. Mittelwert ohne Nullen, Textzellen und Leerzellen

Gruss Totsch

[edit] habe gerade deine Änderung gesehen. Wenn bei #DIV/0! auch eine Null erscheinen soll, dann so:
{=WENN(ISTFEHLER(MITTELWERT(WENN(A1:A1000<>0;A1:A1000)));0;MITTELWERT(WENN(A1:A1000<>0;A1:A1000)))}
 
Zuletzt bearbeitet von einem Moderator:
Hi totsch,

vielen Dank für Deine Hilfe, genau das war die Lösung


gibt es noch eine Möglichkeit in dieser Formel noch folgendes zu integrieren:

Wenn T7:T10 leer, dann schreibe in T11 nichts hinein und behandel T11 nicht als Null, sondern als ein leeres Feld, welches nicht mitberechnet bzw betrachtet wird.
Ich habe das mal so versucht

{=WENN(T7:T10<>"";T7:T11;"");WENN(ISTFEHLER(MITTEL WERT(WENN(T7:T10<>0;T7:T10)));0;MITTELWERT(T7:T10) )}

Soll eigentlich heißen, Wenn in den Zellen T7:T10 nichts steht, dann zeige in T11 auch nichts an , wenn in T11 alles 0 steht zeige alse Ergebnis in T11 Mittelwert der Zellen T7:T10 =0, wenn in Zellen T7:T10 Zahlen stehen dann Mittelwert aus Zellen T7:T10, wobei in den einzelnen Zellen auch 0 als Zahl steht.

Nun zeigt er mir in Zelle T11 aber "Wert" an.

Vielen Dank für Eure Mühe

MfG

Gollom
 
Zuletzt bearbeitet von einem Moderator:
wenn in T11 alles (???) 0 steht zeige alse Ergebnis in T11....
wenn ich das lese und mir deine Formel anschaue, dann frage ich mich erst einmal, in welcher Zelle steht denn überhaupt deine Formel, denn so wie ich es jetzt lese, ergibt deine Formel einen Zirkelbezug, denn Formel fragt etwas zu T11 ab und gleichzeitig soll in T11 etwas geschrieben/Ergebnis ausgegeben werden. Das funktioniert natürlich nicht!

also, in welcher Zelle steht denn überhaupt deine Formel?

-------------------------------------------------------------

WENN(Prüfung;dann Wert;sonst Wert)

=WENN(T7:T10<>"";T7:T11;"");WENN(ISTFEHLER(MITTELWERT(WENN(T7:T10<>0;T7:T10)));0;MITTELWERT(T7:T10))

1.) in deiner Formel steht bei "sonst Wert" kein Wert, sondern ein Bereich und dass kann auch nicht funktionieren! Es kann immer nur ein von dir vorgegebener Wert/Text bzw. einer Zelle ausgegeben werden aber niemals ein Bereich!
2.) deine Formel ist falsch zusammengestellt:
=Wenn(Prüfung;dann;sonst) und bei dir folgt dann ein Symikolon mit einer weiteren 4. WENN-Bedingung.
deine Formel müsste also (wenn überhaupt)so lauten
{=WENN(T7:T10<>"";T7:T11;WENN(ISTFEHLER(MITTELWERT(WENN(T7:T10<>0;T7:T10)));0;MITTELWERT(T7:T10)))}
was aber wie schon gesagt, wegen "sonst Bereich" anstatt "sonst Wert" ebenfalls wieder #WERT! anzeigen würde!

Funktionieren könnte also z.B. diese Formel in T11:
{=WENN(T7:T10<>"";WENN(ISTFEHLER(MITTELWERT(WENN(T7:T10<>0;T7:T10)));0;MITTELWERT(WENN(T7:T10<>0;T7:T10)));"")}
die dann auch deine Bedingungen erfüllt >>
a) Wenn in den Zellen T7:T10 nichts steht, dann zeige in T11 auch nichts an
b) wenn in T7:T10 überall eine 0 und/oder eine 0 und Leerzellen stehen zeige alse Ergebnis in T11 auch eine Null an
C) sonst bilde den Mittelwert aus T7:T10 ohne die Nullen/Text zu berücksichtigen.

Gruss Totsch
 
Zuletzt bearbeitet von einem Moderator:
Hi totsch,

erst mal vielen Dank für Deine Hilfe. Für mich ist das schon ziemlich kompliziert, deshalb wende ich mich ja an die Experten.

die Formel steht in Zelle T11.
Es müßten 3 Bedingungen erfüllt werden:

1. Wenn in den Zellen T7-T10 nichts steht, dann darf auch in Zelle T11 nichts stehen, auch nicht 0, die Zelle sollte für weitere Berechnungen nicht verfügbar sein.
2. steht in den Zellen T7-T10 überall 0, dann soll in der Zelle T11 auch 0 stehen und für weitere Berechnungen verfügbar sein.
3. Steht in den Zellen T7-T10 Zahlen, dann bilde daraus den Mittelwert, wobei z.B. in Zelle T7 auch eine 0 stehen kann und diese bei der Ermittlung des Mittelwertes mit berücksichtigt wird.

Deine Formel ist soweit OK, Bedingung 1 und 2 gehen, bei Bedingung 3 ermittelt er mir den Mittelwert der Zellen die>0 sind.
Sobald aber in einer der Zellen T7-T10 ein Wert >0 steht, soll er die Nullen, die in den anderen Zellen stehen bei dem MIttelwert mit berücksichtigen.

etwa so: T7 0
T8 3
T9 4
T10 0

Mittelwert: 1,75

Wenn das überhaupt geht.

Gruß Gollom
 
dein Thema war >> Mittelwert von zellen errechnen aber " Null " und Buchstaben nicht mitzählen
deshalb auch MITTELWERT(WENN(.....))

wenn nun doch KEIN Text und/oder Leerzellen in T7:T10 vorkommen und du nun doch die Null(en) bei der Mittelwertberechnung einbeziehen/berücksichtigen willst, dann reicht diese Formel völlig aus:
{=WENN(T7:T10<>"";MITTELWERT(T7:T10);"")}

Gruss Totsch
 
Hi Tosch,


danke für Deine Hilfe.
Zum besseren Verständnis, die Tabell hat fast 80 Spalten mit den genannten Bedingungen.

Das Problem ist, daß z.B. in den Zellen T7:T10 doch Leerzellen vorkommen können , also T7=leer T8= leer T9 = leer T10 leer, dann soll T11 auch leer sein
Es kann auch T7=0 T8=0 T9=0 T10=0 vorkommen, dann soll 0 in T11 stehen
Oder T7=1 T8=5 T9=0 T10=5.
Es können also allen 3 Varianten vorkommen.
Sind alle Zellen leer, dann schreibe in T11 auch nichts hinein und darf auch für die Weiterberechnung nicht verwendet werden!
Sind alle Zellen mit 0 belegt, dann schreibe 0 und nicht #DIV/0!
Als 3.Variante stehhen in den Zellen Zahlen, von 0 bis 30.
In allen 3 Fällen soll der MIttelwert errechnet werden, außer in der Variante wo in den Zellen nichts steht, da soll auch nichts berechtnet werden.

Gruß

Gollom
 
Hallo Gollom,

habe dir mal nun einige mögliche Varianten aufgezeigt >>

a59284c8463d9780fcd584c70.jpg

ich denke, dass du die Variante "Mittelwert MIT Nullen aber OHNE Leerzellen" (grüner Bereich) suchst. In Zeile 11 mit MITTELWERT-Funktion und in Zeile 12 mit rein logischer Formel für dem Mittelwert; also Summe geteilt durch Anzahl der Werte (einschl. Nullen).

---------------------------------------------------
Im blauen Bereich wird der Mittelwert gebildet OHNE Nullen und OHNE Leerzellen.
Code:
T11 =WENN(ISTFEHLER(MITTELWERT(T7:T10));"";MITTELWERT(T7:T10))
T12 =WENN(ISTFEHLER(1/ANZAHL(T7:T10));"";SUMME(T7:T10)/ANZAHL(T7:T10))
T14 =ANZAHL(T7:T10) 
T16 {=WENN(ZÄHLENWENN(T7:T10;0)=4;0;WENN(ISTFEHLER(MITTELWERT(WENN(T7:T10<>0;T7:T10)));"";MITTELWERT(WENN(T7:T10<>0;T7:T10))))} 
T17 =ZÄHLENWENN(T7:T10;">0")

Ich hoffe, es ist nun für dich i.O.

Gruss Totsch
 
Hi toschi,

nun ist es perfekt.
Für Deine Hilfe danke ich Dir sehr.

MfG

Gollom
 
Status
Für weitere Antworten geschlossen.
Zurück
Oben Unten