Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1864to1868
1864to1868
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

MITTELWERTWENN ohne Null

Forumthread: MITTELWERTWENN ohne Null

MITTELWERTWENN ohne Null
20.01.2022 09:12:15
Juergen
Hallo,
ich bin auf der Suche nach einer Lösung auf einen Beitrag im archiv dieses Forums gestossen. Dieser Beitrag löst mein Problem, bis auf eine Kleinigkeit. Im Moment wird der Mittelwert nur aus Tabelle 1 ermittelt 

=MITTELWERTWENN(B1:Q1;">="&DATUM(JAHR(HEUTE());1;1);B11:Q11)
, sollte aber aus Tabelle 1 und Tabelle 2 sein. Bitte seid gnädig, ich bin Ü60 und brauche einfach Hilfe ; -)
MfG,
Juergen
Anzeige

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: MITTELWERTWENN ohne Null
20.01.2022 09:15:48
SF
Hola,
ungestet:

=MITTELWERT(MITTELWERTWENN(B1:Q1;">="&DATUM(JAHR(HEUTE());1;1);B11:Q11); MITTELWERTWENN(Tabelle2!B1:Q1;">="&DATUM(JAHR(HEUTE());1;1);Tabelle2!B11:Q11))
Gruß,
steve1da
AW: MITTELWERTWENN ohne Null
20.01.2022 09:23:16
Juergen
Hallo Steve,
vielen Dank für deine Antwort. Bei mir funtioniert es leider nicht, ich bekomme dann die Anzeige 

'WERT!
Ich lade mal die arbeitmappe hoch...

https://www.herber.de/bbs/user/150527.xlsx

Anzeige
AW: MITTELWERTWENN ohne Null
20.01.2022 09:28:52
Juergen
Ich habe übersehen, jeweils 

Tabelle...zu löschen
. Der Wert stimmt aber leider nict. Ich vermute, es fliessen jetzt die Nullen aus Tabelle 2 mit ein? Das richtige Ergebnis wäre 9,65.
AW: MITTELWERTWENN ohne Null
20.01.2022 10:05:42
SF
Ich komme auf eine andere Zahl.

=MITTELWERT(MITTELWERTWENNS(B11:Q11;B11:Q11;">0";B1:Q1;">="&DATUM(JAHR(HEUTE());1;1)); MITTELWERTWENNS(B24:Q24;B24:Q24;">0";B14:Q14;">="&DATUM(JAHR(HEUTE());1;1)))

Anzeige
AW: MITTELWERTWENN ohne Null
20.01.2022 10:28:49
Juergen
Hallo Steve,
mein Ergebnis 9,65 setzt sich zusammen aus: B11 - Q11 (11+11+10+14+11+10+12+10+12+11+8+10+9+9+6+13=167) plus B24 - P24 (10+7+6+3=26), ergibt eine Summe von 193, geteilt durch 20 Tage = 9,65 (gerundet 10). Die Tage mit einem Tagesergebnis 0, werden nicht berücksichtigt.
Liebe Grüße,
Juergen
Anzeige
AW: MITTELWERTWENN ohne Null
20.01.2022 10:30:44
Juergen
Hallo Steve,
ich benutze diese Formel: 

=MITTELWERT(MITTELWERTWENNS(B11:Q11;B11:Q11;">0";B1:Q1;">="&DATUM(JAHR(HEUTE());1;1));  MITTELWERTWENNS(B24:P24;B24:P24;">0";B14:P14;">="&DATUM(JAHR(HEUTE());1;1)))
Liebe Grüße,
Juergen
Anzeige
AW: MITTELWERTWENN ohne Null
20.01.2022 10:47:12
SF 

=SUMMEWENN(B1:Q1;">="&A29;B11:Q11)+SUMMEWENN(B14:Q14;">="&A29;B24:Q24))/(ZÄHLENWENNS(B1:Q1;">="&A29;B11:Q11;">0")+ZÄHLENWENNS(B14:Q14;">="&A29;B24:Q24;">0"))
In A29 habe ich das Datum geschrieben, welches du mit DATUM ermittelst.
Anzeige
AW: einfacher und besser wäres es, ...
20.01.2022 11:43:52
neopa
Hallo Jürgen,
... wenn Du Deine Datenerfassung ändern würdest. Also die Datumswerte untereinander z.B.. in Spalte A und sie anderen Angaben in die Nachbarspalten, so dass Du die "Einheiten/Tag" in Spalte J ermitteln kannst und diese beiden Spalten dann sehr einfach und flexibel mit Pivot auswertest.
Oder zumindest die Formel für "Einheiten/Tag" so änderst, dass anstelle 0 ein ="" ermittelt wird und Du Deine Formel aus Q24 in eine andere Zelle verschiebst. In dem Fall reicht: {=MITTELWERT(WENN(A11:A24=A11;B11:Q24))}
In der zuletzt von SF eingestellte Formel, fehlt da eine führende Klammer am Beginn und die Zellbezüge auf Q24 müssen zwar nicht, aber sollten geändert werden auf P24.
Gruß Werner
.. , - ...
Anzeige
AW: einfacher und besser wäres es, ...
20.01.2022 12:18:36
Juergen
Hallo Werner,
vielen Dank für deine Anregung. Es geht um medizinische Werte, die ich ab und zu mit zum Doc nehmen muss. Ich glaube, das wird dann etwas unübersichtlich, wenn das alles in einer Spalte ist. Ich werde es aber auf jeden Fall mal ausprobieren.
Liebe Grüße,
Jürgen
AW: einfacher und besser wäres es, ...
20.01.2022 14:12:23
Juergen
Ich habe es jetzt umgestellt und bekomme es mit dieser Formel hin: 

=MITTELWERTWENN(K2:K32;"0")
. Ist noch gewöhnungsbedürftig, aber das wird schon ;-)
Vielen Dank!
Anzeige
AW: bitteschön owT
20.01.2022 14:27:13
neopa
Gruß Werner
.. , - ...
;

Forumthreads zu verwandten Themen

Durchschnitt Berechnen ohne Zahlenwerte
esMicha

Hallo.
Ich habe eine Frage und ich hoffe jemand kann mir helfen:
Ich habe eine Tabelle mit Werten (Zahlen und Buchstaben gemischt), denen je eine Zahl zugeordnet werden kann.
r = 1
+ = 2
1 = 3
2 = 4
a = 5
b = 6
m = 7
3 = 8
4 = 9
5 = 10
Wie ka...
27.11.2009 12:27:12
Thread-ID: 1120501
10 Beiträge
Mittelwert ohne null
Dietmar

Hi Leute,kann mit jemand eine Formel verraten, mit der ich Mittelwerte ohne Einbeziehen der 0 erstellen kann.1
2
0
3Mittelwert: 2 (Summe / Anzahl ohne 0)Das Problem dabei: die Werte stehen nicht untereinander, sondern sind wild verteilt.Wäre super, wenn ihr mir einen Tipp hättet.Dietma...
24.04.2002 16:45:03
Thread-ID: 116023
10 Beiträge
VBA-Funktion für Mittelwert ohne Null-Werte
Sören Wiechert

Hallo,wie heisst die VBA-Funktion für eine Mittelwertsberechnung eines Range? Und wie kann man wenn möglich Nullwerte aus der Berechnung ausschliessen?Gruß,
Sören
19.06.2002 11:13:14
Thread-ID: 129798
4 Beiträge
Mittelwert ohne Null
Roberto

Hallo,
ich benötige eine Formel die bei 3 oder mehr Zellen den Mittelwert ausgibt, aber die Zellen mit Null nicht berücksichtigt.
Danke für die Hilfe
Gruß Roberto
04.02.2013 10:53:13
Thread-ID: 1297455
3 Beiträge
Durchschnitt berechnen, aber ohne die zahl "null"
Marc

Leider habe ich bis jetzt keine möglichkeit gefunden,
so eine formel funktionsfähig zu machen.Die Tabelle enthält mehrere Ortschaften, mit den entsprechenden werten,
die Zahlen stehen für eine Umfrage.Beispiel:Basel 6 1
Zürich 0 5
Basel 2 0
Genf 3 1Nun sollte von diesen Zahlen...
15.07.2002 09:57:00
Thread-ID: 136546
4 Beiträge
Zählenwenn nicht null und leere zellen
Katharina

Guten Morgen,
ich möchte in Excel die Anzahl berechnen. Nun habe ich jedoch auch eine 0 mit drinnen und leere Zellen, die ich nicht zählen möchte. Hinzu kommt, dass ich minuszahlen zählen möchte.
Nun lautet meine Funktion wie folgt
=ZÄHLENWENN(M12:M137;">0")+ZÄHLENWENN(M139:M205;...
09.09.2014 10:37:27
Thread-ID: 1380574
2 Beiträge
Mittelwert Spalten ohne Null (inkl. Wenn?)
Lydi

Hallo an Alle,
da ich letztes mal wirklich hilfreiche Tipps zur Lösung hatte (nochmals vielen Dank dafür) so brauche ich jetzt in diesem Fall auch wieder Hilfe. Die Datei zu meinen Fragen findet ihr hier: https://www.herber.de/bbs/user/96647.xlsx
Frage 1:
Ich möchte in H6 den gewic...
26.03.2015 13:26:46
Thread-ID: 1417286
4 Beiträge
Jahresmittelwert wenn Jahr nicht Kalenderjahr
Frank

Hallo liebe Excel-Gemeinde,
ich habe folgendes Problem
Daten: Spalte A Arbeitstage (= unterschiedliche Anzahl von Werten je Jahr und Monatserster und -letzter sind nicht immer enthalten), Spalte B Zahlenwerte (Wechselkurse mit 4 Nachkommastellen)
Ziel: Berechnung von Jahresmittelwer...
21.05.2015 10:00:38
Thread-ID: 1427078
8 Beiträge
Ganze Zeile einfärben wenn Wert größer 0
Michael

Hallo,
ich möchte wenn im Bereich von M3 bis Ende der Tabelle ein Wert über 0 vorkommt die ganze Zeile innerhalb der Tabelle Rot einfärben. Folgender Code funktioniert nicht:
Private Sub Zeilen einfärben Dim Target As Range If Cells(Target.Row, 4).Value >0 Then Target.EntireRow.Inter...
22.05.2015 12:48:24
Thread-ID: 1427327
5 Beiträge
Mittelwert ohne Null aus String
Andre´

Hallo alle zusammen,
ich möchte gern den Mittelwert bilden ohne Null.
für den Zellbereich funktioniert es z.B. wie folgt:
MsgBox WorksheetFunction.Sum(Range("B6:I6")) / Application.WorksheetFunction.CountIf(Range("B6: _ I6"), "0") Sub Mittelwert_o_Null() Dim a As String, b As Strin...
18.06.2015 14:48:11
Thread-ID: 1431717
5 Beiträge
Anzahl ohne Duplikate, leere Zellen ignorieren
Bonduca01

Hallo,
Der Code unten gibt mir in Tabelle 1 Zelle (3,8) die Anzahl der Zellen ohne Duplikate aus.
Leider zählt der Code auch die leeren Zellen mit.
Gibt es eine Möglichkeit, dass er die leeren Zellen nicht zählt? Sub TT() Worksheets("Tabelle1").Activate Cells(3, 8) = Anzahl End Su...
14.07.2015 10:39:10
Thread-ID: 1436244
4 Beiträge
Mittelwert berechnen ohne direkten Bezug
Paul

Hi Zusammen,
habe folgendes Problem und keine Lösung.
Habe eine Zelle in der eine Artikelnummer steht.
In einer anderen Tabelle habe ich den täglichen Abverkauf, hier gibt es auch eine Spalte Kategorie.
Nun will dass die Formel mir schaut welche Produkte sonst noch in der Kateg...
15.07.2015 11:06:44
Thread-ID: 1436458
3 Beiträge
Anzeige

Infobox / Tutorial

MittelwertWENN ohne Null in Excel

Schritt-für-Schritt-Anleitung

Um den Mittelwert in Excel zu berechnen und dabei Nullen zu ignorieren, kannst du die Funktion MITTELWERTWENNS verwenden. Hier ist eine einfache Anleitung:

  1. Öffne deine Excel-Datei und gehe zu der Zelle, in der du das Ergebnis anzeigen möchtest.

  2. Verwende die folgende Formel, um den Mittelwert ohne Nullen zu berechnen:

    =MITTELWERTWENNS(B11:Q11; B11:Q11; ">0"; B1:Q1; ">=" & DATUM(JAHR(HEUTE()); 1; 1))

    Diese Formel berechnet den Mittelwert für alle Werte in dem Bereich B11:Q11, die größer als 0 sind und deren Datumswerte in B1:Q1 aktuell sind.

  3. Für mehrere Tabellen kannst du die Formel anpassen, indem du die Werte aus verschiedenen Tabellen zusammenführst:

    =MITTELWERT(MITTELWERTWENNS(B11:Q11; B11:Q11; ">0"; B1:Q1; ">=" & DATUM(JAHR(HEUTE()); 1; 1)); MITTELWERTWENNS(Tabelle2!B11:Q11; Tabelle2!B11:Q11; ">0"; Tabelle2!B1:Q1; ">=" & DATUM(JAHR(HEUTE()); 1; 1)))

Häufige Fehler und Lösungen

  1. Fehler: #WERT!

    • Lösung: Überprüfe, ob du die richtigen Zellbezüge verwendest. Achte darauf, dass die Bereiche in der Formel korrekt sind und keine unerwarteten Werte enthalten.

  2. Fehler: Unbeabsichtigte Nullen werden einbezogen

    • Lösung: Stelle sicher, dass du die Bedingung ">0" korrekt in deiner Formel verwendet hast, um nur die Werte größer als Null zu berücksichtigen.

Alternative Methoden

Falls die oben genannten Methoden nicht funktionieren, gibt es alternative Ansätze:

  • SUMMEWENN und ZÄHLENWENN kombinieren:

    =(SUMMEWENN(B1:Q1; ">=" & A29; B11:Q11) + SUMMEWENN(B14:Q14; ">=" & A29; B24:Q24)) / (ZÄHLENWENNS(B1:Q1; ">=" & A29; B11:Q11; ">0") + ZÄHLENWENNS(B14:Q14; ">=" & A29; B24:Q24; ">0"))

  • Matrixformel für komplexe Berechnungen:

    {=MITTELWERT(WENN(A11:A24=A11;B11:Q24))}

    Diese Formel erfordert das Drücken von Strg + Shift + Enter.

Praktische Beispiele

  1. Berechnung des Mittelwerts ohne Nullen:

    =MITTELWERTWENNS(B11:Q11; B11:Q11; ">0")

    Dies ermittelt den Mittelwert für die Zeile B11 bis Q11, wobei Nullen ignoriert werden.

  2. Erweiterte Berechnung über zwei Tabellen:

    =MITTELWERT(MITTELWERTWENNS(B11:Q11; B11:Q11; ">0"); MITTELWERTWENNS(Tabelle2!B11:Q11; Tabelle2!B11:Q11; ">0"))

Tipps für Profis

  • Nutze MITTELWERTWENNS, um mehrere Kriterien zu kombinieren. Dies hilft dir, den Mittelwert nur für bestimmte Zeiträume oder Bedingungen zu berechnen.
  • Experimentiere mit Matrixformeln, um komplexere Berechnungen durchzuführen. Diese können dir helfen, noch präzisere Ergebnisse zu erzielen.
  • Verwende benannte Bereiche, um deine Formeln lesbarer zu machen.

FAQ: Häufige Fragen

1. Wie kann ich den Mittelwert in Excel berechnen, ohne Nullen zu berücksichtigen? Um den Mittelwert ohne Nullen zu berechnen, kannst du die Funktion MITTELWERTWENNS verwenden und die Bedingung ">0" in deine Formel einfügen.

2. Gibt es eine Möglichkeit, den Mittelwert ohne leere Zellen zu berechnen? Ja, du kannst die Formel so anpassen, dass sie sowohl Nullen als auch leere Zellen ignoriert. Nutze dazu die Funktion MITTELWERTWENNS mit entsprechenden Bedingungen.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige