Durchschnitt bilden für Zeilen die Bedingungen erfüllen (ohne Hilfsspalte)

[exblow375]

Hallo zusammen,

seit einiger Zeit nutze ich Excel etwas intensiver und hatte gehofft das ich es zumindest ein wenig begriffen habe aber ich beiße mir seit ein paar Tagen die Zähne an folgendem Problem aus:

Ich habe in Spalte AA etwa 300 Zeilen mit Werten aus denen der Durchschnitt (arith. Mittel) gebildet werden soll, jedoch nich aus allen Werten sondern nur aus den Werten, die in anderen Spalten der jeweiligen Zeile entsprechende Bedingungen erfüllen. 
Das Durchschnittsergebnis soll also in einer einzelnen Zelle irgendwo auf dem Tabellenblatt ausgespuckt werden, ich möchte keine Hilfsspalte erstellen um den Mittelwert aus den Zellen der Hilfsspalte zu ermitteln. Dazu habe ich die folgende Formel bisher:


=WENN(UND((RUNDEN($AG:$AG+($AG:$AG*(1-WENNFEHLER(AI:AI/$AG:$AG;0)));0))=(RUNDEN($AH:$AH+($AH:$AH*(1-WENNFEHLER(BC:BC/$AH:$AH;0)));0));BW:BW<0);MITTELWERT($AA:$AA))


Also wenn die Bedingungen erfüllt sind, soll folgendes passieren: 
MITTELWERT($AA:$AA). 
Als Fehler bekomme ich "#WERT!", womöglich weil ich mich auf die gesamten Spalte beziehe!?

Auch mit einer Matrixformel habe ich experimentiert und keinen Erfolge gehabt. 

Ich hoffe, dass mein Anliegen klar geworden ist und mir jemand helfen kann. 

Ganz großen Dank im Voraus   :21:

 

[steve1da]

Hola,

für den Mittelwert mit Bedingungen gibt es
Mittelwertwenns()
Da du nichts zu den Bedingungen sagst, sondern uns raten lässt, kann man nicht mehr sagen. Außer dass deine "Bedingungen" in der Form wie du es getan hat, nicht gebildet werden können.

Gruß,
steve1da

[Oldiefan]

Hallo!

=WENN(UND((RUNDEN($AG:$AG+($AG:$AG*(1-WENNFEHLER(AI:AI/$AG:$AG;0)));0))=(RUNDEN($AH:$AH+($AH:$AH*(1-WENNFEHLER(BC:BC/$AH:$AH;0)));0));BW:BW<0);MITTELWERT($AA:$AA))

Hmmm, du kannst da nicht einfach + und = wahllos einstreuen. Ich würde dir mal die Funktion Mittelwertwenns() empfehlen. Mit der brauchst du auch keine Matrixformel. Sie funktioniert analog zu Summewenns(). Dein $AA:$AA ist der Bereich, den Rest musst du als Kriterien bzw. Kriterienbereich aufschlüsseln.

LG Alex

[exblow375]

Erstmal danke euch beiden!

MITTELWERTWENNS klingt vielversprechend, allerdings bin ich mir unsicher wie ich die folgende Bedingung in diese Formel implementieren soll (wenn also mehrere Bereiche gleichzeitig angesprochen werden):


(RUNDEN($AG:$AG+($AG:$AG*(1-WENNFEHLER(AI:AI/$AG:$AG;0)));0)) = 
(RUNDEN($AH:$AH+($AH:$AH*(1-WENNFEHLER(BC:BC/$AH:$AH;0)));0))


Was ist denn mit wahllos gemeint? Der Syntax an sich ist doch prinzipiell korrekt, jedenfalls habe ich dies in einer einfachen Formel erfolgreich umsetzen können.

Gruß

[steve1da]

Hola,

erklär doch mal in Worten was deine Bedingungen darstellen sollen.

Gruß,
steve1da

[exblow375]

Ich beschreibe erstmal nur den ersten Teil VOR dem Gleichheitszeichen (ist aber analog anzuwenden): 

Der Quotient aus AI/AG wird von dem Wert 1 subtrahiert um die negative oder positive prozentuale Veränderung zu erhalten und umzukehren. 
Wenn also z.B. AI/AG = 1,2 von 1 subtrahiert wird, erhalte -0,2.
Wenn z.B. AI/AG = 0,8 subtrahiert wird dann erhalte ich 0,2.

Dies soll dann mit AG multipliziert werden um das Produkt schließlich mit AG zu addieren. 
Um dem zweiten Beispiel (0,2) zu folgen:
Angenommen AG = 1,3 dann wäre die Rechnung
AG*0,2= 0,26  
AG + 0,26 = 1,56

Durch das Runden auf 0 Stellen würde ich dann 2 erhalten.

Diese Beschreibung kann analog auf den zweiten Teil hinter dem Gleichheitszeichen angewendet werden.

Die eigentlich Bedingung die dann erfüllt sein muss, ist dass beide gerundeten Werte = sind. 

Also in dem Beispiel im ersten Teil habe ich 2 erhalten. Die Bedigung ist dann erfüllt wenn hinter dem Gleichheitszeichen auch eine 2 ausgespuckt wird.

VG

[schauan]

Hallöchen,

Ich beschreibe erstmal nur den ersten Teil VOR dem Gleichheitszeichen

wenn Du diesen Ansatz auch mal zum Testen anwendest kommst Du an der einen oder anderen Stelle sicher weiter. Du könntest weitgehend jeden Bestandteil Deiner komplexen Formel erst mal auf korrekte Teilergebnisse prüfen. Ggf. musst Du beim Test direkte Bezüge verwenden und wenn das klappt schauen, ob die in einer Matrix analog wirken.

[exblow375]

Das hilft mir nicht weiter. Wenn ich nicht schon mit zig Szenarien herumexperimentiert hätte, hätte ich mir glaube ich nicht die Mühe gemacht hier zu posten.

[Fred11]

Hallo exblow375,

1.
Es ist schwierig, zu beurteilen, warum deine Formel nicht funktioniert, ohne deine Tabelle zu kennen.
Spricht etwas dagegen, eine Beispieldatei hier hochzuladen ? Abgespeckt und evtl. mit Dummy-Daten (die Struktur sollte aber der Originaltabelle entsprechen !)
Das würde die Chance auf Hilfe deutlich erhöhen.

2.
Warum willst du das unbedingt ohne Hilfsspalten lösen ?
Mit ein oder zwei Hilfsspalten wäre das wahrscheinlich recht einfach zu lösen.
Die Hilfsspalten kannst du ja einfach ausblenden.

3.
Zu deiner Formel:

Als Fehler bekomme ich "#WERT!", womöglich weil ich mich auf die gesamten Spalte beziehe!?

Meiner Meinung nach ja.
Wenn deine Tabelle eine Kopfzeile mit Text hat (was eine ordentliche Tabelle haben sollte…) und du dich auf die ganze Spalte(n) beziehst, ergibt das einen Fehler, weil du ja in deinen Bedingungen u.a. Divisionen durchführst.
Du solltest dich in deiner Formel also auf jeden Fall nicht die auf die ganze Spalte beziehen, sondern auf den eigentlichen Datenbereich beschränken.

Ausserdem würde deine Formel so wie du sie geschrieben hast meiner Meinung nach kein sinnvolles Ergebnis liefern:
Wenn deine (sehr komplexen) Bedingungen wahr sind, errechnest du den Mittelwert der gesamten Spalte AA.
Wenn die Bedingungen Falsch sind, wird gar nichts gerechnet und du erhältst ein schnödes FALSCH

4.
Probier mal folgende Formel:

=MITTELWERT(WENN(UND(Bedingung1; Bedingung2);AA2:AA300)

als Matrixformel mit Strg+Shift+Enter
(Zellbezüge entsprechend anpassen...)

Gruß
Fred

[Fred11]

Probier mal folgende Formel:

=MITTELWERT(WENN(UND(Bedingung1; Bedingung2);AA2:AA300)

als Matrixformel mit Strg+Shift+Enter

besser so:
=MITTELWERT(WENN((Bedingung1)*(Bedingung2);AA2:AA300))

da UND irgendwie nicht so recht eine Matrix verarbeiten mag.
(und hinten hat auch noch eine Klammer gefehlt)

Gruß
Fred