Mittelwert nach SVerweis mit Fehler

[febus]

Hallo liebe Excel Freunde,

Ich laboriere schon seit einigen Stunden an einem Fehler und komme einfach nicht auf die Lösung.

Ich will einen MITTELWERT von Werten erstellen die ich über SVERWEIS ermittele. Das Problem ist, dass nicht immer ein Wert per SVERWEIS vorhanden ist, also an der Stelle ein Fehlerwert ermittelt wird den MITTELWERT nicht verarbeiten kann.

Ich habe versucht mit der WENNFEHLER Funktion um das Problem zu arbeiten, komme aber nicht zum gewünschten Ergebnis.

Der folgende Code ergibt den Fehler "#WERT!":

=MITTELWERT(WENNFEHLER(SVERWEIS($K177;$K$148:$P$155;5;FALSCH);"");WENNFEHLER(SVERWEIS($K177;$K$156:$P$160;5;FALSCH);""))

Das Problem scheint zu sein dass die zweite Zahl eben ein Fehler ist und MITTELWERT nicht mit dem Wert_falls_Fehler "" arbeiten kann. Das heißt wenn ich "" etwa durch "0" ersetze bekomme ich einen gültigen Wert. Allerdings soll der fehlende Wert ignoriert und nicht mit 0 ersetzt werden.

Kurios für mich ist dass mein MITTELWERT korrekt ausgespuckt wird wenn ich den Befehl oben in 3 Zellen unterteile:

=WENNFEHLER(SVERWEIS($K177;$K$148:$P$155;5;FALSCH);"")
=WENNFEHLER(SVERWEIS($K177;$K$156:$P$160;5;FALSCH);"")
=MITTELWERT(N177:N178)

Hat jemand eine Idee wie ich das gewünschte Ergebnis in einer einzigen Zelle ermitteln kann?

Vielen Dank für eure Hilfe,

Febus

[shift-del]

Hallo Febus

Code:

=AGGREGAT(1;6;N177:N178)

[Rabe]

Hi,

Kurios für mich ist dass mein MITTELWERT korrekt ausgespuckt wird wenn ich den Befehl oben in 3 Zellen unterteile:

=WENNFEHLER(SVERWEIS($K177;$K$148:$P$155;5;FALSCH);"")
=WENNFEHLER(SVERWEIS($K177;$K$156:$P$160;5;FALSCH);"")
=MITTELWERT(N177:N178)

Hat jemand eine Idee wie ich das gewünschte Ergebnis in einer einzigen Zelle ermitteln kann?

ich verstehe noch nicht, was Du erreichen willst: Willst Du einen Mittelwert aus zwei Bereichen ermitteln?

Warum machst Du nicht alles mit nur einem Bereich
=WENNFEHLER(SVERWEIS($K177;$K$148:$P$160;5;FALSCH);"")
?

Was ist das Problem an den drei Zellen? Die kannst Du doch ganz weit nach rechts schieben und noch ausblenden.
Dann die eigentliche Zelle mit der dritten Formel verwenden.

[neopa]

Code:

=AGGREGAT(1;6;N177:N178)

Hallo Detlef,

... AGGREGAT() hilft hier nicht, erstens weil febus das ja gern ohne N177:178 gelöst haben möchte, zweitens in N177:N178 keine Fehlerwerte stehen werden und drittens beide Formelzellwerte ="" (oder bei entsprechenden Daten in Spalte O es evtl. sogar auch Textwerte sein könnten)  und dann bedarf es ebenfalls ein WENNFEHLER() wie die Mittelwert-Formel  =WENNFEHLER(MITTELWERT(N177:N178);"")

@febus

ich sehe es auch wie Ralf, warum willst Du nicht bei Deinen zwei Hilfszellenformeln bleiben? In einer Formel alle Eventualitäten Deiner spez. Bedingungen (wie ich sie momentan nur aus Deinen Formeln interpretieren kann) zu berücksichtigen wird aufwändiger, denn es ist ja bis jetzt von Dir z.B. nicht ausgeschlossen, dass der Wert K177 in $K$148:$K$160 nicht mehr als max. zweimal vorkommen könnte.

[shift-del]

Hallo Werner

So wie ich es verstanden habe stehen in N177 und N178 jeweils ein SVERWEIS() mit dem möglichen Fehlerwert #NV.

[febus]

Vielen Dank für die Antworten!

Um ein wenig mehr Info zu geben:

Ich habe immer 4 Bereiche (ich habe das Beispiel nur mit 2 gemacht um es zu vereinfachen). In den Bereichen steht zu jedem Produkt x maximal ein Eintrag. Es kann aber sein dass das Produkt x nicht jedem Bereich vorkommt (weshalb SVERWEIS teilweise Fehler liefert).

Ich will den MITTELWERT aus den vorhandenen Werten pro Produkt herstellen. Also im Fall dass der Wert in allen 4 Bereichen vorhanden ist, habe ich kein Problem.

Das Ziel ist es, alles in einer einzigen Formel unterzubringen.

@shift-del: Ich habe das mit AGGREGAT versucht und die Paarung mit SVERWEIS scheint nicht zu klappen.
Also zB der Code bringt nix:
=AGGREGAT(1;6;SVERWEIS($K177;$K$148:$P$155;5;FALSCH);SVERWEIS($K177;$K$156:$P$160;5;FALSCH))

@Rabe: Wenn ich nur einen Bereich definiere wie Du vorschlägst könnte ich ja mit SVERWEIS nicht verschiedene Werte für das selbe Produkt auslesen. So wie ich das verstehe liest SVERWEIS immer den ersten Wert aus mit dem das Suchkriterium übereinstimmt.

@Rabe und Neopa: Ich werde euren Vorschlag umsetzen und mit den Hilfszellen arbeiten. Allerdings wäre es langfristig weniger umständlich wenn ich das in eine Zelle packen könnte. Also wenn ihr noch Ideen habt, dann versuche ich gerne weiter.

Danke nochmal an alle,

febus

[Jockel]

Hallo, kannst du mal (d)eine (Beispiel)Datei posten..?