Averageif DIV/0 Fehler wenn ein Wert nicht vorhanden ist , Teil wert aber benötigt

[Joern]

Moin zusammen,

Ich arbeite weiter an meine liste und habe nun das nächste problem.
Ich benutze folgende Formel:
=AVERAGEIFS('100 Tank Deck'!C:C,'100 Tank Deck'!A:A,"*phone*",'100 Tank Deck'!A:A,"*com*")

und bekomme einen DIV/0 Fehler da auf dem Tank deck A:A das wort com nicht vorkommt.
nun brauche ich aber trotzdem die daten angezeigt für das wort phone.

Kenn einer die Lösung das trotz nicht vorhanden Wort die werte der vorhandenen Wörter ausgegeben werden denn später würde die Formel wie folgt aussehen
(dies unten ist die größte mögliche Formel mit Kreterin)

=AVERAGEIFS('100 Tank Deck'!C:C,'100 Tank Deck'!A:A,"*NVX*",'100 Tank Deck'!A:A,"*ATV*",'100 Tank Deck'!A:A,"*PDU*",'100 Tank Deck'!A:A,"*TV55*",'100 Tank Deck'!A:A,"*TV65*",'100 Tank Deck'!A:A,"*HDMI*",'100 Tank Deck'!A:A,"*TV32*",'100 Tank Deck'!A:A,"*TV80*",'100 Tank Deck'!A:A,"*MON-32*",'100 Tank Deck'!A:A,"*MCP4k*",'100 Tank Deck'!A:A,"*TV85*",'100 Tank Deck'!A:A,"*CHR*",'100 Tank Deck'!A:A,"*lift*")


Vielen dank für die Hilfe schonmal

[Ego]

Hallo  J...,

die Bedingungen in SUMMEWENNS werden mit einem logischen UND verknüpft.
Ich kann mir nicht vorstellen, dass all deine gesuchten Zeichenketten in der gleichen Zelle sein sollen.

Für ein logisches ODER kannst du 
1 prüfen ob die einzelne Zeichenkette vorhanden ist,
2 diese Werte für alle Zeilen  addieren und
3 prüfen ob die Summe >0 ist.

für den Mittelwert ginge dann:

=SUMME(C2:C10*(WENNFEHLER(FINDEN("phone ";A2:A10);0)+WENNFEHLER(FINDEN("com";A2:A10);0)>0))/SUMME(1*(WENNFEHLER(FINDEN("phone";A2:A10);0)+WENNFEHLER(FINDEN("com";A2:A10);0)>0))

[Oldiefan]

Hallo!

Ja, sobald da irgendwo 0 als Ergebnis rauskommt, dividierst du durch 0, was ja mathematisch nicht möglich ist. Darum musst du wohl alle Abfragen mit Wennfehler absichern und dann so den Mittelwert bilden. Beim Mittelwert hast du ja 255 Argumente, die möglich sind.

Liebe Grüße
Alex

[Ego]

Sorry,

ab Office 21 natürlich mit LET

=LET(SollIch;1*(WENNFEHLER(FINDEN("phone";A2:A10);0)+WENNFEHLER(FINDEN("com";A2:A10);0)>0);SUMME(C2:C10*SollIch)/SUMME(SollIch))

[Joern]

Hallo  Ego und Oldiefan,

Ich habe beide Formeln Ausprobiert jedoch funktioniert keine so wie ich es bräuchte entweder bei der alten Formel:
=SUMME(C2:C10*(WENNFEHLER(FINDEN("phone ";A2:A10);0)+WENNFEHLER(FINDEN("com";A2:A10);0)>0))/SUMME(1*(WENNFEHLER(FINDEN("phone";A2:A10);0)+WENNFEHLER(FINDEN("com";A2:A10);0)>0))
Ich habe die Auswahlbereiche angepasst bekomme ich ein Value Fehler.
und bei der neuen Formeln einen name Fehler.

Zusatz Informationen:

Die zu suchenden begriffe sind alle in der spalte A von 2 bis open end.
Die Daten die ich benötige sind in Zeile C

---

Also mir wurde eben eine Formel weiter geschickt die fast funktioniert.

Einziger Fehler ist das suchende Wort muss exakt gleich sein wie das existierende wort sprich in der Formeln unten werden nur Wörter mit Phone oder com gefunden aber nicht Phones oder coms.
=AVERAGE(IF(('100 Tank Deck'!A:A="*phone*")+('100 Tank Deck'!A:A="*com*"),'100 Tank Deck'!C:C))

[Ego]

Hallo J...,

wenn die Texte sowohl mit Groß - als auch mit Kleinbuchstaben vorhanden sind, must du SUCHEN anstelle von FINDEN nutzen.

ps.
a) In der ersten Formel ist hinter dem "phone" ein Leerzeichen zuviel.

b) Wenn die zweiten Formel einen Name-Fehler bekommst, hast du nicht wie angegeben Office 365.

c) Der Vergleichsoperator "=" kennt keine wildcards.

[EA1950]

Hallo,

da du Excel365 hast, müsste Folgendes klappen:

=LET(k;$A$2:$A$20;  w;$C$2:$C$20;  tk;{"phone"."com"};
f;NACHZEILE(k;LAMBDA(z;SUMME(WENNFEHLER(SUCHEN(tk;z);0))));
MITTELWERT(WENN(f;w;FALSCH)))

Der Kriterienbereich k ist in $A$2:$A$20.
Der Wertebereich w ist in $C$2:$C$20.
Die Testwerte tk befinden sich im Zeilen-Array {"phone"."com"}. Diese überprüfen, ob ein Wert aus w in der MITTELWERT-Funktion berücksichtigt wird. Mit dem tk werden in k auch "tphones" oder "coms" gefunden.

Ich habe im obigen Beispiel die Bereiche k und w auf die 19 Zellen in den Zeilen $2:$20 beschränkt und nicht auf die ganzen Spalten A bzw. C. Eine solche Begrenzung nur auf die nötigen Zeilen würde ich auch in deinem Echt-Problem anraten. Der Geschwindigkeitsvorteil ist doch beträchtlich. Am besten wäre es, wenn du die beiden Spalten in eine strukturierte (=intelligente) Tabelle gibst - dann ist das Problem, wie groß die Bereiche zu wählen sind, vom Tisch.

Die Formel von vorhin gilt natürlich nur für ein deutsches Excel365. Für ein englisches sind die Funktionen und die Interpunktionszeichen entsprechend anzupassen - sonst gibt es einen NAME?-Fehler oder sonst einen Fehler.

Zum Abschluss noch eine kurze Formel, die aber nur für ein aktuelles Excel365-Insider funktioniert:
=MITTELWERT(WENN(REGEXTEST($A$2:$A$20;"phone|com");$C$2:$C$20;FALSCH))

[Joern]

Vielen dank,
Es hat geklappt