Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1648to1652
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Mittelwertwenn nur mit höchstem Wert eines Kriteri

Mittelwertwenn nur mit höchstem Wert eines Kriteri
06.10.2018 17:46:21
Steffen
Hallo in die Runde,
ich hoffe, ihr könnt mir bei meinem Problem mit der Berechnung eines Mittelwertes helfen.
Hier einmal das Beispiel:
https://www.herber.de/bbs/user/124459.xlsx
Mein Ziel: Den individuellen Mittelwert für einen Zeitraum (z.B. "2016 FY") unter der Bedingung berechnen, dass nur EPS-Werte verwendet werden, die vor der Veröffentlichung des zu betrachtenden Analysten schon vorhanden waren. Ich nehme immer nur den letzten Wert eines jeweiligen Zeitraums. Deswegen die Extra-Spalte "Letztes Update im Jahr".
Dies habe ich bereits in der Spalte "Avg" gemacht.
Nun möchte ich den Mittelwert berechnen unter der gleichen Bedingung, nur dass von den anderen Analysten nicht alle Werte berücksichtigt werden sondern lediglich der jüngste (entspricht dem größten Datum).
Beispiel zur Tabelle:
Für den Zeitraum "2016 FY" wird für Analyst "B" unter "Avg" der Mittelwert aller EPS-Werte gebildet, die vor dem 10.1.2017 vorhanden waren. Also sind auch alle 4 Werte des Analysten C enthalten, dessen letzte Veröffentlichung am 13.7.2016 war.
Ich möchte jedoch nur den EPS-Wert vom 13.7.2016 haben und nicht die anderen. Und das ganze natürlich bei allen Analysten.
Der Grund für das weitere Kriterium "Unternehmen" bei der Mittelwertwenn-Formel liegt daran, dass ich hier nur ein Unternehmen einer langen Liste entnommen habe.
Vielen Dank für eure Hilfe!
Beste Grüße
Steffen

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: ermittele dazu den MAX-(Datums)Wert ...
06.10.2018 18:05:21
neopa
Hallo Steffen,
... in der Version 2010 gibt es noch kein MAXWENNS() deshalb nutzte ich dafür
MAX(INDEX(F$2:F$999*(C$2:C$999=C2);)) in nachfolgender Formel.
Die würde damit in Zeile 2 so lauten:

=MITTELWERTWENNS(H:H;C:C;C2;A:A;A2;F:F;"="&MAX(INDEX(F$2:F$999*(C$2:C$999=C2);)))

Gruß Werner
.. , - ...
AW: ermittele dazu den MAX-(Datums)Wert ...
06.10.2018 19:33:56
Steffen
Hallo Werner,
vielen Dank für Deine Antwort. Jedoch suche ich etwas anderes.
Mit Deinen Formeln berechne ich den Durchschnitt aller EPS am letzten Veröffentlichungstag in der jeweiligen Periode. Somit habe ich insgesamt 9 verschiedene Mittelwerte, da 9 Perioden vorhanden sind.
Ich möchte jedoch einen individuellen Mittelwert für jeden Analysten: Dieser soll vom Tag vor dem letzten Veröffentlichungstag des Analysten den Durchschnitt aller EPS bilden (so wie ich es schon mit der Mittelwertwenn-Formel gemacht habe) unter der weiteren Bedingung, dass von den anderen Analysten lediglich der letzte Wert berücksichtigt wird, sofern er zeitlich vor dem zuvor beschriebenen Tag liegt.
Bsp:
-In 2016 ist der letzte Tag, an dem Analyst B veröffentlicht der 10.1.2017
-Von Analyst C möchte ich daher lediglich den EPS-Wert vom 13.7.2016 nehmen, da dies sein aktuellster Wert in der Periode ist und auch zeitlich vor dem 10.1.2017 liegt.
-Von Analyst Z hingegen möchte ich lediglich den EPS-Wert vom 31.5.2016 berücksichtigen. Dies ist aus Sicht von B der aktuellste Wert. Das ist der jüngste Wert von Z der vor dem 10.1.2017 veröffentlicht wurde. Der EPS-Wert vom 12.1.2017 soll somit nicht berücksichtigt werden.
Ich hoffe, es ist verständlich geworden.
Anzeige
AW: hmm, nachgefragt ...
06.10.2018 20:28:38
neopa
Hallo Steffen,
... so wie Du schreibst, willst Du Mittelwerte je Analyst und Periode ermitteln, die sich aus allen EPS-Werten ergeben, die vor dem letzten Veröffentlichungsdatum des auszuwerten Analysten liegen inklusive der EPS-Daten der anderen Analysten, die ebenfalls vor dem jeweiligen bereits benannten Datum liegen, aber nur die von dessen jüngsten Datum? Oder?
Den Sinn solcher "Mittelwerte" verstehe ich momentan auch nicht, muss ich auch nicht. Es wäre jedoch hilfreich, Du würdest für Deine Beispieldatei mal mind. zwei verschiedene Zielergebniswerte (von "Hand" ermittelt) angeben.
Du schreibst auch: "Von Analyst Z hingegen möchte ich lediglich den EPS-Wert vom 31.5.2016 berücksichtigen". In Deiner Datei finde ich jedoch keinen entsprechenden Datensatz!?
Wie viele Daten sind denn max auszuwerten?
Heute gehe ich jedoch sicherlich bald offline.
Gruß Werner
.. , - ...
Anzeige
AW: hmm, nachgefragt ...
07.10.2018 09:26:52
Steffen
Hallo Werner,
ich habe hier die Beispieltabelle einmal bearbeitet und den von mir gewünschten Mittelwert händisch für die Periode 2016 berechnet. Ich hoffe, so wird es verständlicher: www.herber.de/bbs/user/124467.xlsx
So wie Du es beschrieben hast, stimmt es. Ich möchte außerdem, dass der auszuwertende Analyst mit keiner seiner Schätzungen vorkommt. Sondern lediglich, wie Du beschrieben hast, nur die anderen und dann jeweils das jüngste Datum sofern es vor dem letzten Datum des auszuwertenden Analysten liegt.
Grund:
Ich möchte einen Konsensus (Mittelwert) bilden, den der jeweilige Analyst zum jeweiligen Datum hat. Und darin lediglich die aktuellsten Werte der anderen wiederfinden. Weil sich die EPS-Schätzungen über das Jahr verteilt deutlich ändern können, möchte ich den jeweils aktuellsten Wert der anderen Analysten.
In Spalte E sind alle Zeitpunkte angegeben, zu denen ein Analyst Zahlen veröffentlicht hat (hier auch Analyst Z am 31.5.2016). In F lediglich der Zeitpunkt, zu dem ein Analyst zum letzten Mal in der jeweiligen Periode veröffentlicht hat.
Insgesamt sind es rund 30.000 Datenpunkte. Falls Berechnungen in Excel 20 Minuten dauern, habe ich kein Problem damit.
Sollte so etwas nur via VBA lösbar sein, habe ich nicht die Kenntnisse einen Code zu schreiben...
Viele Grüße und vielen Dank, dass Du Dich meines Problems annimmst,
Steffen
Anzeige
AW: jetzt etwas klarer, jedoch ...
07.10.2018 11:00:21
neopa
Hallo Steffen,
... ist mir z.B. unklar, warum Du für die MW-Berechnung von J2 den Wert aus H267 mit einbezogen hast? Das Datum in E267 ist kleiner als das in E268 bei gleichen Analysten.
Das wäre ein Widerspruch gegen Deine verbalen Vorgaben. Oder?
Die Ergebnisswerte in jeder Datenzeile nur mit Formeln in einer Spalte zu ermitteln, könnte bei 30.000 Datensätzen nicht nur lange Rechenzeit nach sich ziehen sondern ist teilweise nicht nötig, weil gleiche Ergebniswerte, wie z.B. in J57:J59 ... Doch auch wenn das formelmäßig berücksichtigt werden kann, ist noch viel Rechenzeit notwendig und vor allem was nützt Dir ein derartiges Listing?
Deshalb denke ich momentan an eine PIVOTauswertung kombiniert mit einer einer Formelergänzung. Alternativ käme möglicherweise auch eine PowerQuery-Auswertung in Betracht.
Für beides sollte die auszuwertende Datenquelle in eine "intelligente" Tabelle gewandelt werden, was leicht und schnell realisiert ist. Dazu empfiehlt sich jedoch, Deine Spaltenüberschriften sinnvoll ein zu kürzen (und ohne Sonderzeichen wie Leerzeichen).
Gruß Werner
.. , - ...
Anzeige
AW: jetzt etwas klarer, jedoch ...
07.10.2018 11:50:31
Steffen
Hallo Werner,
gut, dass ich es händisch gemacht habe, um es Dir verdeutlichen zu können. Leider hat sich da dann der Fehler eingeschlichen, den Du genannt hast. Doof, dass es ausgerechnet beim ersten Wert aufgetreten ist. Aber die Intuition scheint ja dennoch klar geworden zu sein.
Der Lösungsweg, der gegangen wird - falls er gegangen werden kann - ist mir nicht so wichtig, wo der Mittelwert am Ende steht ebenfalls nicht.
Ich habe auch schon mit Hilfe einer Pivot-Tabelle versucht mir zu helfen. Bin jedoch bisher auf keinen grünen Zweig gekommen.
Ich habe die Tabelle dahingehend schon einmal modifiziert: https://www.herber.de/bbs/user/124470.xlsx
Gruß Steffen
Anzeige
AW: mit Pivot allein geht es auch nicht ...
07.10.2018 13:01:00
neopa
Hallo Steffen,
... wie bereits geschrieben, stelle ich mir momentan noch eine Kombination von Pivot- mit Formelauswertung vor.
Folgende Fragen hätte ich noch: Momentan sind in der Beispieldatei max 22 Analysten je Periode auszuwerten. Können das je Periode mehr werden und wenn ja wie viele max? Und bleibt es bei max 9 Perioden?
Ich geh jetzt jedoch gleich wieder offline und komme möglicherweise erst morgen wieder dazu mich Deiner Angelegenheit anzunehmen.
Gruß Werner
.. , - ...
AW: mit Pivot allein geht es auch nicht ...
07.10.2018 14:12:51
Steffen
Hallo Werner,
die Anzahl der Analysten je Periode variiert, der höchste Wert liegt bei etwa 40.
Die Jahresperioden belaufen sich auf 11. Jedoch würde ich gern noch eine weitere Untersuchung nach dem gleichen Muster mit quartärlichen Zahlen machen. Dort wären es dann 42 Perioden.
Es handelt sich um 59 Unternehmen und insgesamt gut 32.000 Zeilen.
Ich danke Dir noch einmal vielmals!
Gruß
Steffen
Anzeige
AW: hierzu folgender Hinweis ...
08.10.2018 09:32:50
neopa
Hallo Steffen,
... meine angedachte Lösung (eine Pivotauswertung anschließend mit Formeln auszuwerten) würde dann wohl nur vertretbar sein, wenn die Pivotauswertung immer nur für eine Firma und auch nur eine Periode gefiltert wird. Für diesen Fall würde ich eine (Hilfs-)Auswertungsformel erstellen und diese in max 2000 Zellen kopieren, um damit bis zu 50 Ergebniswerte zu ermitteln.
Du müsstest dann also immer die Pivotauswertung entsprechend filtern, wenn Du mit Hilfe dieser Formeln die Ergebnisse anderer Perioden und Firmen angezeigt haben möchtest.
Wäre das denn überhaupt eine akzeptable Lösung für Dich?
Gruß Werner
.. , - ...
Anzeige
AW: hierzu folgender Hinweis ...
08.10.2018 09:51:14
Steffen
Hallo Werner,
ist zwar ein wenig Arbeit, aber ich halte die Lösung für akzeptabel. Das Maximum einer Firma liegt bei 1587 Schätzungen. Somit sollten also 1600 Zellen ausreichen.
Vielen Dank!
AW: das wäre schon noch etwas anders ...
08.10.2018 10:37:25
neopa
Hallo Steffen,
... wenn Du eine komplette Auswertung je Firma anstrebst, wären das bei meiner angedachten Lösung dann 1600x42 Formeln (wobei natürlich nicht alle immer wirklich etwas berechnen müssen, aber si müssten vorhanden sein. Ich wollte bisher nur 40x42 Formeln vorsehen. Kann ich momentan nicht einschätzen, ob das für 1600 Zeilen noch zumutbar ist/wäre.
Gruß Werner
.. , - ...
Anzeige
AW: das wäre schon noch etwas anders ...
08.10.2018 11:04:19
Steffen
Hallo Werner,
falls es möglich ist, auch eine Auswertung für jede Firma zu machen, wäre das super.
Ansonsten liegt das Maximum unterschiedlicher Schätzungen bei einer Firma für ein bestimmtes Jahr bei 247.
Viele Güße
Steffen
AW: nun ...
08.10.2018 14:40:12
neopa
Hallo Steffen,
... nach langen tüfteln, hab ich für die eingestellte Beispieldatei einen Lösungsvorschlag entwickeln können. Dieser beinhaltet zwei Pivotdatenauswertung mit jeweils zusätzlichen Hilfsspalten (einfache Formeln) und zusätzlich eine komplexen Formel zur Ermittlung der maßgebenden Daten, die für die endgültige Mittelwertberechnung zu Grunde gelegt werden können.
Dies kann ich alles aus Zeitgründen hier nicht weiter beschreiben und die Datei ist jetzt zu groß, um sie hier hoch zu laden. Ich kann sie Dir aber direkt zu senden.
Meine Mailadresse bekommst Du, wenn Du in eine leere Standardexcelzelle folgende Formel schreibst: =WECHSELN("neopaCode";"Co";ZEICHEN(64)&"email.")
Schreibe in den Betreff bitte: Herber-Forum thread; Steffen/ (damit ich die Mail darüber aus meinem Spamordner herausfiltern kann).
Wenn Du die Mail abgesendet hast, schreibe bitte hier, dass Du sie versendet hast.
Ich bin jetzt aber erst einmal ein paar Stunden offline, melde mich dann am Abend oder morgen.
Gruß Werner
.. , - ...
Anzeige
AW: Mail erhalten, Antwort ist "unterwegs" owT
08.10.2018 18:28:01
neopa
Gruß Werner
.. , - ...

302 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige