Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.04.2024 00:08:11
In Ordner nur das Speichern von Dateien erlauben
19.04.2024 21:26:15
mehrfache Texte finden
19.04.2024 19:41:34
Bei VBA Ausführung Anzeige einfrieren
19.04.2024 17:05:40
Zählen von Zeilen, in denen in bestimmten Bereich Daten sind
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1632to1636
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1632to1636
1632to1636
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Mittelwert () - Unterschied Verweis/Direkteingabe

Mittelwert () - Unterschied Verweis/Direkteingabe
05.07.2018 13:55:41
elm88
Hallo,
Excel bringt mich mal wieder an den Rand der Verzweiflung, diesmal ist's die Funktion Mittelwert.
Wenn ich mit der Funktion =MITTELWERT(A1;A2;A3) auf drei Zellen mit folgendem Inhalt verweise...
A1=10
A2=20
A3=KOKOLORES
...errechnet Excel mir korrekterweise den Mittelwert aus 10 und 20 (=15). Die Zelle, in der KOKOLORES steht, wird einfach ignoriert.
Jetzt kommt der Spaß!
Wenn ich folgende Formel nutze =MITTELWERT(10,20,"KOKOLORES"), beschwert sich Excel mit der Ausgabe in Form von #WERT.
Lange Rede, kurzer Sinn: Wie kann ich Excel über die Direkteingabe eine leere oder zu ignorierende Zelle vorgaukeln?
Vielen Dank für eure Hilfe!

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Mittelwert () - Unterschied Verweis/Direkteingabe
05.07.2018 14:27:19
Daniel
Hi
folgender Unterschied:
bei Verweis auf Zellen ignoriert Mittelwert Zellen mit Textinhalt.
das gilt auch für Zellen, die einen Text enthalten, der wie eine Zahl aussieht.
Das ist der Fall, wenn der Wert mit Hochkomma eingegeben wurde ('10), eine Formel das Ergebnis "10" ausgibt oder die Zelle bei Eingabe des Wertes mit dem Zahlenformat TEXT formatiert war.
Excel prüft dann nur den Inhaltstyp der Zelle und schaut nicht mehr auf den eigentlichen Inhalt.
bei einer Direkteingabe der Werte versucht Excel jedoch, einen Text, der wie eine Zahl aussieht, in eine Zahl zu wandeln.
Dh =Mittelwert("10";"20") wird dir das Ergebnis 15 ausgeben, während bei einem Verweis auf Zellen, die den Text "10" und "20" enthalten, das Ergebnis der Div/0-Fehler sein wird, weil es keine Zahlen gibt, die ausgewertet werden können.
das hat dann aber zur folge, dass bei Direkteingabe Texte, die nicht in Zahlen gewandelt werden können, einen Fehler erzeugt wird.
Die Formeln sind in Excel so geschrieben, dass bei einem Fehlerwert in den verwendeten Werten dieser Fehler als Formelergebnis ausgegeben wird.
Gruß Daniel
Anzeige
AW: Mittelwert () - Unterschied Verweis/Direkteingabe
05.07.2018 14:51:36
elm88
Danke für die Info! Kann man das irgendwie umgehen/austricksen?
Folgender Hintergrund: Ich muss eine Mittelwertsfunktion mit Wenn-Funktionen verschachteln. Da es aber nicht um Arrays geht, klappt MITTELWERTWENN(S) nicht. D.h. es wird zwingend mal vorkommen, dass in den Argumenten der Mittelwert-Funktion eine 0 oder "" oder was auch immer auftaucht, was dann allerdings berücksichtigt werden darf.
Freue mich auf kreative Ideen! ;-)
AW: Mittelwert () - Unterschied Verweis/Direkteingabe
05.07.2018 14:57:02
Daniel
sorry ich versteh dein Problem nicht ganz.
das Problem tritt doch nur auf, du die Einzelwerte direkt in die Formel schreibst und dann kannst du Texte wie Kokolores ja einfach weglassen.
sobald du auf Zellen verweist, werden Leerzellen und Texte ignoriert.
ansonsten wenn du sowieso schon mit WENN arbeitest (innerhalb von Mittelwert) dann kannst du auch ein Wenn(istzahl()) mit hinzunehmen.
Gruß Daniel
Anzeige
AW: Mittelwert () - Unterschied Verweis/Direkteingabe
05.07.2018 15:08:28
elm88
Genau, ich muss die Werte direkt in die Formel mitaufnehmen... schwer zu erklären!
Daher hier einfach mal als Beispiel-Datei:
https://www.herber.de/bbs/user/122490.xlsx
Stellt man eine Relevanz auf "nein", ist der Mittelwert verfälscht, da es wie eine null betrachtet wird. Jemand Ideen, wie man das hinbekommt? Die Datei ist nun nur ein kleines Beispiel, die Anwendung hier vor Ort dasselbe in grün, nur umfangreicher ;-)
=MITTELWERTWENN(B7:F7;"ja";B4:F4)
05.07.2018 15:23:25
WF
.
AW: =MITTELWERTWENN(B7:F7;"ja";B4:F4)
05.07.2018 15:32:18
elm88
Ich kann die Zellen C und F NICHT überspringen/inkludieren, da dort andere Werte stehen!
AW: =MITTELWERTWENN(B7:F7;"ja";B4:F4)
05.07.2018 15:49:46
Daniel
HI
dann mach doch eine 3 Zeile auf, kennzeichne dort die zu verwendenden Wert und nimm MittelwertWenns um eben diese zweite Bedingung mit hinzuzunehmen.
wenn das nicht möglich ist, dann musst du eben mit =Summe(Werte)/Summe(Anzahl) arbeiten.
dh du kannst in der Summe deine Wenns() einsetzen.
in Summe(Anzahl) ersetzt du den Verweis auf die Werte (B4, D4, F4) durch 1
Gruß Daniel
Anzeige
AW: =MITTELWERTWENN(B7:F7;"ja";B4:F4)
05.07.2018 16:20:21
elm88
Das hab ich leider nicht verstanden. Hatte mich in der letzten Nachricht auch vertippt, in Spalten C und E stehen andere Werte, sodass ein Array nicht möglich ist. Was soll in einer 3. Zeile genau stehen? Danke!
dann per Division
05.07.2018 16:31:19
WF
Hi,
=SUMMENPRODUKT((A7:G7="ja")*(REST(SPALTE(A1:G1);2)=0)*A4:G4)/SUMMENPRODUKT((A7:G7="ja") *(REST(SPALTE(A1:G1);2)=0))
Wenn in Spalte 4 auch Texte vorkommen können, dann die Arrayformel:
{=SUMME(WENN(A7:G7="ja";WENN(REST(SPALTE(A1:G1);2)=0;A4:G4)))/SUMMENPRODUKT((A7:G7="ja") *(REST(SPALTE(A1:G1);2)=0)) }
WF
AW: =MITTELWERTWENN(B7:F7;"ja";B4:F4)
05.07.2018 16:31:26
Daniel
Hi
naja, du nimmst die Zeile 8 und schreibst in die Zellen B8, D8 und F8 ein "x"
in den anderen Spalten lässt du die Zeile leer.
dh alle Spalten, die für diesen Mittelwert relevant sind, sind in Zeile 8 mit einem "x" gekennzeichnet
dann diese Formel: 
=MittelwertWenns(B4:F4;B7:F7;"ja";B8:B7;"x")

dh in den Mittelwert gehen dann nur die Werte ein, die in Zeile 7 ein "ja" und in Zeile 8 ein "x" haben.
damit kannst du die zwischenwerte ausschließen.
wenn das auch nicht geht: 
=(B4*(B7="ja")+D4*(D7="ja")+F4*(F7="ja")/((B7="ja")+(D7="ja")+(F7="ja"))

die Wenns kann man sich hier sparen, weil ein WAHR wie 1 und ein FALSCH wie 0 gewertet wird, wenn es in einer Berechnung mit +-*/ verwendet wird.
Gruß Daniel
Anzeige
Klammerfehler
05.07.2018 16:40:21
Daniel
so ist es richtig:
=(B4*(B7="ja")+D4*(D7="ja")+F4*(F7="ja"))/((B7="ja")+(D7="ja")+(F7="ja"))
oder so:
Hilfzeile einrichten (Zeile 8) mit der Formel für die drei Zellen =Wenn(B7="ja";B4;"")
und dann den Mittelwert in der Zeile 8 Bilden: =Mittelwert(B8;D8;F8)
Gruß Daniel
AW: Klammerfehler
06.07.2018 15:01:26
elm88
Danke für eure Hilfe, die letzte Variante sollte er richten! :-)
das verhält sich genauso wie SUMME und +
05.07.2018 14:40:15
WF
Hi,
=SUMME(A1:A3) funktioniert
=A1+A2+A3 nicht
WF
Nicht ganz, ist komplizierter, ...
05.07.2018 19:27:28
Luc:-?
…Walter & all;
als Bereich oder einzeln angegebene ZellBezüge wdn auf Text|Zahl geprüft, direkt angegebene Werte offensichtlich nur übernommen, wobei TextZahlen wie echte behandelt wdn und Texte einen Fehler verursachen. Gibt man aber alle DirektWerte zusammenfassend als MatrixKonstante an, hier also {10;20;"KOKOLORES"}, wdn die EinzelWerte wie Inhalte von ZellBezügen behandelt.
Das wird damit zusammenhängen, dass eine MatrixKonstante genauso wie ein ZellBereich in einer Schleife abgearbeitet und damit auch einer internen GleichBehandlung unterzogen wird.
Gruß, Luc :-?
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige