Varianz mit IF-Funktion verknüpfen

[Lor4x]

Hallo liebes Forum,

ich würde gerne die Varianz aller Werte die größer als ein festgelegter Wert sind berechnen.
Hat jemand eine gute Idee wie man das machen könnte?

Vielen Dank für die Hilfe!
Tim

[Ego]

Hallo Tim,

da die Varianz-Funktion in Excel Texte und Wahrheitswerte ignoriert muss man den Vektor so bearbeiten, dass:

a) Werte die grösser als der Grenzwert sind beibehalten werden und
b) Werte die kleiner gleich dem Grenzwert sind in Texte gewandelt werden.

zB.

Code:

=VAR.P(WENNFEHLER((A4:E4*1/(A4:E4>G4));"N"))

mit STRG+Shift+RETURN abschliessen (Matrixfunktion).

[Lor4x]

Ganz vielen Dank für die schnelle Antwort.

Nochmal eine Frage dazu als Anfänger:

der Ausdruck

Code:

(A4:E4*1/(A4:E4>G4)

Was passiert, wenn A4:E4> G4? Wird das es dann zu einem String umgewandelt , was einen Fehler produziert?

Wäre super hilfreich für mein Verständnis.
VG

[Ego]

Hallo Tim,

wie Teile einer Formel von Excel bearbeitet werden, kannst du selbst herausfinden, indem du in der Berarbeitungszeile diesen Teil markierst und F9 drückst. Dann wird dieser Teil gesondert ausgewertet und in der Formel wird das Ergebnis dieser Auswertung eingetragen.
Aber nicht vergessen die Bearbeitungszeile mit ESC zu verlassen, da sonst die ursprüngliche Formel ersetzt wird.

Diese Funktionalität ist auch hilfreich zum Testen von Teilen einer Formel, falls sie nicht das gewünschte Ergebnis liefert.



Ich versuche aber trotzdem einmal die Formel von Innen nach Aussen zu beschreiben, damit du diese Technik auf die Entwicklung andere Formeln anwenden kannst.

a) Wesentlich ist der Abschluss mit STRG+Shift+RETURN (=Matrix-Formel). Hierbei wird inerhalb der Formel mit Matritzen und Vektoren gerechnet.

b)A4:E4> G4 : Für jede Zelle des Bereiches A4:E4 wird ein Wahrheitswert ermittelt und die Werte werden in einem Vektor abgelegt.
Für die Werte, die ich in der äusseren Funktion (hier Varianz) weiterbearbeiten möchte, möchte in den Wert WAHR erhalten.

c)(A4:E4> G4):  Die Klammer ist sehr wichtig. Sie ändert zwar nichts direkt an dem Vektor, sorgt aber dafür, dass ich mit ihm weiterrechnen kann. Bei einer Berechnung wird WAHR durch 1 und FALSCH durch 0 ersetzt. (Manchmal sieht man in Formeln anstatt der Klammer auch ein doppeltes "-" vor einem Wahrheitswert. Ein Minus ist dafür da, dass mit dem Wahrheitswert gerechnet werden kann und das andere als zusätzlicher Rechnungsoperator um das erste Minus auszugleichen. "--Wahrheitswert" bedeutet dann "+0" oder "+1" )

d)1/(A4:E4> G4): Das ist ein häufig angewandter Trick bei Formeln. Aus einem Vektor mit Nullen und Einsen wird jetzt ein Vektor mit Fehlerwerten (Div0) und Einsen. Viele Formeln wie zB die Verweise ignorieren Fehlerwerte und ich werte anschliessend mit den Formeln nur die von mir gewünschten Werte aus.

e)A4:E4*1/(A4:E4> G4): Jetzt werden die ursprünglichen Werte mit Fehlerwerten oder Einsen multipliziert. Die gewünschten Werte werden beibehalten und die nicht gewünschten Werte werden zu Fehlerwerten.

f)In vielen Fällen, in denen die Funktion Fehlerwerte ignoriert kann man auf diesen Vektor schon die Funktion anwenden.  Da die Varianz aber nur Texte und Wahrheitswerte und keine Fehlerwerte ignoriert, muste ich eine Funktion finden, die Fehlerwerte zu Texte oder Wahrhzeitswerte wandelt: WENNFEHLER(A4:E4*1/(A4:E4>G4);"N"). Ich habe jetzt einen Vektor in dem die nicht gewünschten Werte durch den Text "N" (beliebig) ersetzt wurden.

g) Auf diesen Vektor kann ich dann die Varianz anwenden. VAR.P(WENNFEHLER(A4:E4*1/(A4:E4>G4);"N"))


ps. In meiner ersten Antwort ist ein unnötiges Klammerpaar.

[BoskoBiati]

Hallo,

da reicht das vollkommen:

Code:

{=VAR.P(WENN(A4:E4>G4;A4:E4))}

Selbst das würde schon gehen:

Code:

{=VAR.P(WENNFEHLER(A4:E4/(A4:E4>G4);""))}

das: (A4:E4> G4) bewirkt, dass dieser Term bei der Division als Ganzes betrachtet wird. Ohne die Klammern hätte die Division Vorrang vor dem >-Zeichen! (Beide Varianten mal mit der Formelauswertung nachstellen!)

*1 ist hier überflüssig, da ja eine Rechenoperation folgt, die automatisch aus Wahr/Falsch 1/0 macht.

-- ist gleich mit *1 oder N(...), damit wird ein als Text vorliegender Zahlenwert in eine Zahl umgewandelt, wobei N noch dazu aus Wahr eine 1 und aus Falsch oder Texten eine 0 macht.

statt "N" reicht in der Formel "" aus, da "" auch ein Text ist.

Die Klammern kann man sich zum Teil auch sparen. A4:E4 wird als Range auch ohne Klammern akzeptiert.