Mittelwert Wenn Spalte "XY"

[Felixk]

Hallo Clever-Excel-Forum,

folgende Ausgangslage: Ich habe ein Aufstellung monatlicher Renditen von 1994-2016. Die Renditen können in verschiedene "Asset-Klassen" unterteilt werden. In der Datei im Anhang möchte ich zwei verschiedene Arten von Mittelwerten für verschiedene Schemes (E3, Tabellenblatt Aufstellung Gesamt) gleichgewichtete (equally-weighted) und value-weighted im Tabellenblatt "Aufstellung gewünscht" erstellen.

a):

Für die Mittelwerte "gleichgewichtet" habe ich simple alle Mittelwerte der einzelnen Assets im jeweiligen Monat (es gibt circa 12 verschiedene Schemes). Einen ersten Anfang habe ich im Tabellenblatt "Aufstellung gewünscht" in D6:D14 gemacht.

Meine Frage hierzu ist: Kann ich die WENNDANN_Funktion durch eine andere Formel ersetzen welche variabler ist und welche sinnvolle Berechnungen durchführt auch wenn ich im ersten Tabellenblatt "Aufstellung Gesamt" die ein oder andere Zeile lösche, bzw. der Dateninput in Folge von Bereinigungen schrumpft? Generell frage ich mich ob es eine Möglichkeit gibt mit einem SVerweis die Spalte nach den Schemes durchzugucken und wenn z.B. Hedge Fund/Convertible Arbitrage "identifiziert" wird der Mittelwert aller Renditen für den jeweiligen Monat i (F:F), (G:G),.... gebildet werden kann?

b): 

Für die Wertgewichtete Mittelwerte (value-weighted) möchte ich die "Reported Date"-Zellinhalte  IZ5:SS102 verwenden um im ersten Schritt  Mittelwerte der einzelnen Assets zu gewichten. Dafür brauche ich denke ich die Spalten ST und SU, "Total NAV within Scheme" bzw. "Share NAV Asset within Scheme".

Total NAV within Scheme: Diese Spalte hat für jedes "Scheme"(Spalte E) den Mittelwert aller Werte IZ5:SS102 (Jan1995-Jan2016), welche z.b. dem Scheme "Hedge/Convertible Arbitrage" zugeordnet werden kann.

Share NAV Asset within Scheme: Diese Spalte soll den Mittelwert von Jan 95-Jan 2016 für alle Assets in Spalte B (z.B.: IZ5:SS5, IZ6:SS6, usw.)  bilden und simpel durch den Wert  der Spalte ST teilen.

Diese Mittelwerte sollen dann auch im Tabellenblatt "Aufstellung gewünscht" Z5:AT270 eingefügt werden. Ich bin mir sicher ich habe das Ausgangsproblem nicht detailliert erklärt. Daher werde ich versuchen schnellstmöglich zu antworten um Fragen zu meinem Anliegen zu beantworten.


Vielen Dank für erste Vorschläge oder Lösungsansätze,

Felix

[Peter]

Hallo,

In der Datei im Anhang

da ist kein Anhang zu sehen.

Hier findest du eine Anleitung zum Hochladen von Dateien:

http://www.clever-excel-forum.de/Thread-...ng-stellen

[Felixk]

Hallo,


da ist kein Anhang zu sehen.

Hier findest du eine Anleitung zum Hochladen von Dateien:

http://www.clever-excel-forum.de/Thread-...ng-stellen

Danke für den Hinweis. Ich musste nun einige Änderungen vornehmen, sodass die Dateigröße nicht zu groß ist. 

Gruß,

Felix

[schauan]

Hallöchen,
wenn Deine Datei keine Makros enthält, kannst Du sie auch als xlsx speichern. Es gibt einiger Anwender, die keine Dateien anschauen, die Makros enthalten können, was bei xlsb der Fall ist.
In Deiner Datei hab ich keine WENNDANN-Formel gefunden

Im Prinzip kannst Du es so lösen. In der rechten Spalte hab ich nur mal zum Vergleich die festen Werte eingetragen. Die Formel kannst Du nach rechts und unten ziehen.

Arbeitsblatt mit dem Namen 'Aufstellung gewünscht'
	C	D	E
5		Hedge/Convertible Arbitrage	Hedge/Credit Focus
6	Jan 95	-3,4974202	-3,4974202
7	Feb 95	1,719871731	1,719871731
8	Mrz 95	0,771752055	0,771752055
9	Apr 95	2,22951308	2,22951308
10	Mai 95	2,909391631	2,909391631
11	Jun 95	1,833742032	1,833742032
12	Jul 95	2,942611034	2,942611034
13	Aug 95	1,066491462	1,066491462
14	Sep 95	1,502938269	1,502938269
15	Okt 95

Zelle	Formel
D6	=MITTELWERTWENNS(INDEX('AUfstellung Gesamt'!$F:$IX;;ZEILE(D1));INDEX('AUfstellung Gesamt'!$F:$IX;;ZEILE(D1));"<>0";'AUfstellung Gesamt'!$E:$E;D$5)

[Felixk]

Hallöchen,
wenn Deine Datei keine Makros enthält, kannst Du sie auch als xlsx speichern. Es gibt einiger Anwender, die keine Dateien anschauen, die Makros enthalten können, was bei xlsb der Fall ist.
In Deiner Datei hab ich keine WENNDANN-Formel gefunden

Im Prinzip kannst Du es so lösen. In der rechten Spalte hab ich nur mal zum Vergleich die festen Werte eingetragen. Die Formel kannst Du nach rechts und unten ziehen.

Arbeitsblatt mit dem Namen 'Aufstellung gewünscht'
	C	D	E
5		Hedge/Convertible Arbitrage	Hedge/Credit Focus
6	Jan 95	-3,4974202	-3,4974202
7	Feb 95	1,719871731	1,719871731
8	Mrz 95	0,771752055	0,771752055
9	Apr 95	2,22951308	2,22951308
10	Mai 95	2,909391631	2,909391631
11	Jun 95	1,833742032	1,833742032
12	Jul 95	2,942611034	2,942611034
13	Aug 95	1,066491462	1,066491462
14	Sep 95	1,502938269	1,502938269
15	Okt 95

Zelle	Formel
D6	=MITTELWERTWENNS(INDEX('AUfstellung Gesamt'!$F:$IX;;ZEILE(D1));INDEX('AUfstellung Gesamt'!$F:$IX;;ZEILE(D1));"<>0";'AUfstellung Gesamt'!$E:$E;D$5)

Hallo André und hallo an den Rest!,

bitte im Anhang die aktuelle Datei beachten. Diesmal als xlsx- Ich habe die Dateigröße von ca. 50 MB auf 180 KB schrumpfen können indem ich bereits leere Zellen (beginnend ab der letzten Zeile der Matrix richtung "unten" "gelöscht" habe- warum auch immer dort Informationen enthalten waren?! :)

Ich habe deine Formel für meine File angewandt und es funktioniert perfekt. Ich wollte deine Formel nun leicht abgeändert als Summewenns Befehl für G9:Z28 im Tabellenblatt "Value-weighted PF_" anwenden. Die erste Reihe in Zeile 9 ist damit auch zu berechnen. Möchte ich aber die Reihen 10-28 ausfüllen geben mir die Zellen den Fehler "Bezug" aus. Ich muss auch ehrlich gestehen, dass ich die Formel aus deinem Lösungsvorschlag nur "fast" verstehe. Ich bin mir nicht sicher warum du die Zeilenfunktion als Eingabewert für die Spalte der Indexfunktion einsetzt. Ich denke, dass dort auch der Fehler für die Anwendung im Bereich G9:Z28 liegt, kann das sein?

In F30:Z60 sollen die monatlichen NAV-Werte (Reported Date IZ:SS im ersten Tabellenblatt) der einzelnen Assets(Zeilen) hergenommen werden. Ich habe mich hierzu einer Kombination aus WennDann und SVerweis bedient. Gibt es hierzu einen eleganteren Weg oder ist das die richtige Formel?

Der letzte Schritt: In F62:Z92 sollen die Verhältnisse gebildet werden (monatlich) aus z.B. G35/G9. Dieses Verhältnis soll dann mit der dazugehörigen monatlichen Rendite F5 im Tabellenblatt "AUfstellung Gesamt" multipliziert werden. Dazu könnte ich die Summenprodukt in Kombination mit der Indexfunktion hernehmen oder?

Einen schönen Sonntag wünsche ich euch,

Felix

[schauan]

Hallöchen,

das mit dem INDEX ist nötig, um aus der waagerechten Anordnung der Monate eine senkrechte zu machen. Da Du jetzt die Ausrichtung beibehälst, ist auch kein INDEX nötig. Ebenso brauchst DU keine SUMMEWENNS, da es nur noch eine Bedingung gibt. Hier reicht also einfach
=SUMMEWENN('AUfstellung Gesamt'!$E:$E;$F9;'AUfstellung Gesamt'!IZ:IZ)