Microsoft Excel

Herbers Excel/VBA-Archiv

Formel anpassen

Betrifft: Formel anpassen von: Thms Drng
Geschrieben am: 08.08.2014 10:43:36

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

  

Betrifft: AW: Formel anpassen von: Daniel
Geschrieben am: 08.08.2014 10:49:09

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


  

Betrifft: AW: Formel anpassen von: {Boris}
Geschrieben am: 08.08.2014 12:13:22

Hi,

für die SUMME gibt es auch eine arrayfreie Variante:

=SUMMEWENN(A:A;"<1e+307")

VG, Boris


  

Betrifft: ergänzend ... von: neopa C (paneo)
Geschrieben am: 08.08.2014 12:47:40

Hallo Boris,

... wobei auch

=SUMMEWENN(A:A;"<9e+99")
;-) reichen dürfte.

Und für den Mittelwert dann
=SUMMEWENN(A:A;"<9e+99")/ANZAHL(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.


  

Betrifft: AW: ergänzend ... von: Daniel
Geschrieben am: 08.08.2014 12:59:53

Hi
anstelle von

=SUMMEWENN(A:A;"<9e+99")/ANZAHL(A:A)

verwendet man dann konsequenterweise doch lieber:
=MITTELWERTWENN(A:A;"<1e+307")

Gruß Daniel


  

Betrifft: wieder ergänzend ... von: neopa C (paneo)
Geschrieben am: 08.08.2014 16:21:52

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
.. , - ...


  

Betrifft: AW: wieder ergänzend ... von: Daniel
Geschrieben am: 08.08.2014 16:55:56

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;"<9^9")/ZählenWenn(A:A;"<9^9")

Gruß Daniel


  

Betrifft: ANZAHL() ignoriert Texte und Fehlerwerte ... von: neopa C (paneo)
Geschrieben am: 10.08.2014 17:00:29

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

 ABCDE
1Werte 4 5,5
24 4 5,5
3#N/A 1 0
4Text   #DIV/0!
55    
6#NV    
7#WERT!    
86    
97    
10     

Formeln der Tabelle
ZelleFormel
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
.. , - ...





  

Betrifft: AW: ANZAHL() ignoriert Texte und Fehlerwerte ... von: Daniel
Geschrieben am: 11.08.2014 15:12:20

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


  

Betrifft: hab natürlich keine mehrsprachige Excelversion ... von: neopa C (paneo)
Geschrieben am: 11.08.2014 17:54:11

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

 ABCDE
1Werte 045,5
24  45,5
3#N/A    
4Text    
55    
6     
7#NV    
8#WERT!    
9#DIV/0!    
10#NAME?    
11#BEZUG!    
12#BEZUG!    
136    
147    
15Ende    
16     

Formeln der Tabelle
ZelleFormel
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
.. , - ...


  

Betrifft: AW: Formel anpassen von: Thms Drng
Geschrieben am: 08.08.2014 15:02:10

danke für die Antworten! :)


 

Beiträge aus den Excel-Beispielen zum Thema "Formel anpassen"