Formel anpassen

Forumthread: Formel anpassen

Formel anpassen

08.08.2014 10:43:36

Thms

Hallo Forum,
ich möchte eine Reihe summieren, in der unter anderem der Eintrag #N/A vorkommen kann. Somit ergibt die Summe momentan immer #N/A. Wie kann ich das umgehen? Die Formel müsste so angepasst werden, dass eben nur summiert wird, wenn ein Zahlenwert vorhanden ist. Sofern in der Reihe nur #N/A vorhanden ist, soll eine Null als Summe herauskommen.
Ähnlich möchte ich den Durchschnitt einer Reihe bilden, in dem der Eintrag #N/A oder auch k.A. vorkommen kann. Sofern nur diese Einträge vorkommen, soll Null herauskommen, ansonsten bis zum letzten Zahlenwert summiert bzw. der Durchschnitt gebildet werden.
Kann mir jemand helfen?
Beste Grüße

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Formel anpassen

08.08.2014 10:49:09

Daniel

Hi =Summe(Wenn(IstZahl(A1:A10);A1:A10))
und das ganze als Matrixformel eingeben, dh Eingabe mit STRG+SHIFT+ENTER abschließen.
für den Mittelwert dann analog.
Gruß Daniel

AW: Formel anpassen

08.08.2014 12:13:22

{Boris}

Hi,
für die SUMME gibt es auch eine arrayfreie Variante:
=SUMMEWENN(A:A;"<1e+307")
VG, Boris

ergänzend ...

08.08.2014 12:47:40

neopa

Hallo Boris,
... wobei auch


=SUMMEWENN(A:A;"

;-) reichen dürfte.
Und für den Mittelwert dann


=SUMMEWENN(A:A;"

Gruß Werner
.. , - ...
oT: sorry, wenn ich ich hier: https://www.herber.de/forum/messages/1375200.html für einen kurzen Moment angenommen hatte, das Du das sein könntest.

AW: ergänzend ...

08.08.2014 12:59:53

Daniel

Hi
anstelle von

=SUMMEWENN(A:A;"

verwendet man dann konsequenterweise doch lieber:

=MITTELWERTWENN(A:A;"

Gruß Daniel

wieder ergänzend ...

08.08.2014 16:21:52

neopa

Hallo Daniel,
... mit Deinem Hinweis auf MITTELWERTWENN() hast Du natürlich Recht. Diese gilt jedoch nicht für ältere XL-Versionen. Und ich fand & finde die spez. Wirkungsweise von ANZAHL(A:A) schon zusätzlich erwähnenswert.
Gruß Werner
.. , - ...

AW: wieder ergänzend ...

08.08.2014 16:55:56

Daniel

Hi
was ist denn die spezielle Wirkungsweise von Anzahl()?
du hast da nichts zu erwähnt, was an der Funktion so speziell sein soll.
Sollte man nicht, wenn man schon den Mittelwert mit bedingung berechnet, dafür Sorge tragen, dass für die Ermittlung von Summe und Anzahl auch die gleichen Bedingungen verwendet werden? Sonst könnte es ja zu falschen Ergebnissen kommen.

=SummeWenn(A:A;"

Gruß Daniel

ANZAHL() ignoriert Texte und Fehlerwerte ...

10.08.2014 17:00:29

neopa

Hallo Daniel,
... sorry, ich hatte nicht vermutet, dass Du das nicht wirklich wüsstest.
Insbesondere das Ignorieren von Fehlerwerten in den Argumenten kommt bei Excelfunktionen eher nicht so oft vor, spontan fällt mir da momentan nur noch VERWEIS() ein.
Für diese Funktionen gilt diese Wirkungsweise in in der originären Anwendung. Mit dem vom {Boris} genutzten "Trick" in der Angabe des Vergleichsparameter, werden andere Funktionen in in einen ähnliche Wirkungsweise erst versetzt.
Allerdings ist in Deiner zuletzt angegebenen Formel das Argument 9^9 für beide Funktionen dafür nicht ausreichend, wie man sich leicht überzeugen kann.
Tabelle1

	A	B	C	D	E
1	Werte		4		5,5
2	4		4		5,5
3	#N/A		1		0
4	Text				#DIV/0!
5	5
6	#NV
7	#WERT!
8	6
9	7
10

Formeln der Tabelle

Zelle	Formel
C1	=ANZAHL(A:A)
E1	=SUMMEWENN(A:A;"<9e+99")/ANZAHL(A:A)
C2	=ZÄHLENWENN(A:A;"<9e+99")
E2	=MITTELWERTWENN(A:A;"<9e+99")
C3	=ZÄHLENWENN(A:A;"<9^9")
E3	=SUMMEWENN(A:A;"<9^9")/ZÄHLENWENN(A:A;"<9^9")
E4	=MITTELWERTWENN(A:A;"<9^9")

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

AW: ANZAHL() ignoriert Texte und Fehlerwerte ...

11.08.2014 15:12:20

Daniel

kannst du das mal die Datei hochladen?
da kommt #N/A und #NV als Ergebnis vor.
Sind das jetzt Texte oder Fehlerwerte?
kann mir irgendwie nicht vorstellen, dass du innerhalb der selben Exceldatei zwei verschiedene Sprachversionen zur Anwendung bringen kannst.
mit dem Argument "9e+99" funktioniert es ja auch mit MittelwertWenn!
Gruß Daniel

hab natürlich keine mehrsprachige Excelversion ...

11.08.2014 17:54:11

neopa

Hallo Daniel,
(ich spreche Dich auch weiterhin direkt an, wie es mir der gute Umgangston gebietet, auch wenn Du, aus welchen Gründen auch immer, stets vermeidest mich - wie allerdings viele andere auch- anzusprechen.
Damit bist Du als Excelprofi mE diesbzgl. nicht gerade ein Vorbild für Gelegenheitsfrager und -Antworter und minderst mE damit auch Deine unbestrittenen Excelfähigkeiten)
... Die heute nun von Dir abgeforderte Datei (wo ich davon ausgehe, dass Du diese Dir sicher selbst hättest erstellen können) habe ich 1:1 nicht mehr.
Ich hatte Dir gestern zunächst Deine Frage vom Freitag beantwortet und mit der dortigen Tabelle Deine unkorrekte letzte Formel vom Freitag widerlegt. Wenn Du damit ein Problem haben solltest, dass Dir auch mal ein Fehler unterlaufen war, dann solltest Du dies einfach als passiert akzeptieren.
In meiner deutschsprachigen Excel-Version war und ist das "#N/A" in der Tabelle natürlich ein Text. Die anderen gelisteten Fehlerwerte waren und sind Fehlerwerte, wie ich diese jetzt (etwas erweitert) für Mitleser noch mal in A6:A12 inklusive ihrer erzeugenden Beispielnonsensformeln aufzeige, damit auch ersichtlich ist, dass es sich dabei nicht um Text handelt.
Die Formel in D1:E2 sind meine Formelvorschläge von gestern nochmal dargestellt und in C1 Deine vom Freitag.
Tabelle1

	A	B	C	D	E
1	Werte		0	4	5,5
2	4			4	5,5
3	#N/A
4	Text
5	5
6
7	#NV
8	#WERT!
9	#DIV/0!
10	#NAME?
11	#BEZUG!
12	#BEZUG!
13	6
14	7
15	Ende
16

Formeln der Tabelle

Zelle	Formel
C1	=SUMMEWENN(A:A;"<9^9")/ZÄHLENWENN(A:A;"<9^9")
D1	=ANZAHL(A:A)
E1	=SUMMEWENN(A:A;"<9e+99")/ANZAHL(A:A)
D2	=ZÄHLENWENN(A:A;"<9e+99")
E2	=MITTELWERTWENN(A:A;"<9e+99")
A7	=VERGLEICH(9;B:B;)
A8	="a"+9
A9	=9/0
A10	=a+b
A11	=INDEX(A1:B9;11;9)
A12	=B1-#BEZUG!

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Deine letzten Aussage: mit dem Argument "9e+99" funktioniert es ja auch mit MittelwertWenn!
steht etwas losgelöst aus dem Zusammenhang. Natürlich ist diese richtig, aber Du hattest in Deinem letzten Beitrag am Freitag eben was ganz anders aufgezeigt und um das ging es mir gestern.
Was auch immer Du mir jetzt antworteten möchtest, willst oder tust, bitte ich Dich nur, doch mir den gleichen -nennen wir es Respekt - zu erweisen, wie ich diesen Dir bisher immer entgegengebracht habe (auch in Office-Lösungen) und erbringe, auch wenn ich mich excelmäßig im Gegenteil zu Dir "nur" VBA frei "betätige".
So nun ist aber meinerseits das wichtigste gesagt. mIch wünsche noch einen schönen Abend, ich gehe jetzt erst einmal offline.
Gruß Werner
.. , - ...

AW: Formel anpassen

08.08.2014 15:02:10

Thms

danke für die Antworten! :)

Beliebteste Forumthreads (12 Monate)

Forumthread: Formel anpassen

10
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Formel anpassen

Forumthread: Formel anpassen

10 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

10
Beiträge zum Forumthread

Beiträge zu diesem Forumthread