Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1980to1984
1980to1984
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Mittelwert berechnen am 28. Kalendertag

Forumthread: Mittelwert berechnen am 28. Kalendertag

Mittelwert berechnen am 28. Kalendertag
12.06.2024 18:54:29
Pedder
Hallo,
in meinem Haushaltsbuch stehen in einer Spalte die monatlichen Ausgaben für einen bestimmten Kostenbereich, der detailliert in einer benachbarten Arbeitsmappe abgebildet ist, als jeweilige Summe pro Monat: F3:F14. In A3:A14 stehen die festen Datumsangaben vom 03.01.2024 bis 03.12.2024.Ich möchte den aktuellen Mittelwert dieser Zellen (F3:F14) ermitteln, und zwar für jeden Monat zum 28. Kalendertag. Derzeit steht in der Ausgabezelle F16 folgende Funktion: =MITTELWERT(F3:F14). In F8 sind aktuell, also bis zum 12.06.2024 weniger Moneten geflossen, sodaß dieser Wert in F8 den Mittelwert verzerrt. Ich möchte nun für die Zellen F3:F14 festlegen, dass der Mittelwert in der Ausgabezelle F16 erst ab dem 28.Kalendertag berechnet wird. Dazu habe ich mir die Augen fusselig gesucht und kriegs einfach nicht hin. Bitte um Hilfe.
Anzeige

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Mittelwert berechnen am 28. Kalendertag
12.06.2024 19:16:49
BoskoBiati2
Hi, 

=wenn(tag(heute()))>=28;mittelwert(F3:F14);"")


Ist am nächsten 1. wieder weg!

Gruß

Edgar
AW: Mittelwert berechnen am 28. Kalendertag
12.06.2024 23:42:33
Oppawinni
Es wäre vorteilhaft, wenn man eine Beispieldatei hätte.
Ich denke du hättest in diesem Fall schon ein vielfaches an Lösungen bekommen. Ich schlage mal vor:
=MITTELWERTWENN($A$3:$A$14;"="&DATUM(JAHR($A$3);WENN(TAG(HEUTE())>=28;MONAT(HEUTE());MONAT(HEUTE())-1);3);$F$3:$F$14)
Wobei es vermutlich günstig wäre, wenn du eine Zelle hättest, in der du z.B. per Strg+"." jeweils das aktuelle Datum eingibst.
und dann einen Bezug auf diese Zelle machst, statt Heute() zu verwenden.
Sonst passiert es halt, dass du nächstes Jahr im Januar deine Werte vom Vorjahr nicht angezeigt bekommst, oder gar Blödsinn heraus kommt.
(Wobei obige Formel vor dem 28 Januar auch einen Fehler werfen wird, weil es Monat 0 nicht gibt.)
Im nächsten Jahr solltest du dann natürlich nichts mehr an der Zelle mit dem aktuellen Datum ändern. Es sei denn, du verfolgst damit einen besonderen Zweck.
Anzeige
AW: Mittelwert berechnen am 28. Kalendertag
13.06.2024 07:45:58
Pedder
BAM!!! Oppawinni Volltreffer!!! Was für eine (für mich) gigantische Formel :-) Ich habe eine Beispieldatei hochgeladen und deine Formel gleich mit eingefügt: passt! Soweit so gut. Mal sehen, was am 28. passiert. Danke Oppawinni!

Die Datei wird um den 01.01. des Folgejahres in eine pdf umgewandelt, das Datum für die dann neuen Berechnungen an das dann aktuelle Jahr angepasst.

Vielen Dank auch an alle weiteren Mitwirkenden.
Anzeige
AW: Mittelwert berechnen am 28. Kalendertag
15.06.2024 11:11:09
Oppawinni
Hm, ich denke mal, dass das hier schon als erledigt angesehen wird.
Wenn du noch Fragen dazu hast, dann kannst du die auch noch anbringen.
Dann aber nicht vergessen "Frage noch offen" zu markieren.
Eine Beispieldatei wäre primär dann wichtig, wenn es offene Fragen gibt.
Ein Antworter muss sich dann nicht erst selbst etwas basteln und sich
aus verbalen Beschreibungen etwas zusammen reimen.
In aller Regel dürfte man damit schneller zu einer passenden Lösung kommen.
Anzeige
AW: Mittelwert berechnen am 28. Kalendertag
17.06.2024 11:39:13
Pedder
Hallo Oppawinni,
vielen Dank für die Info. Nun ja, ich hätte natürlich die Formel von Dir gerne verstanden. So habe ich sie einfach übernommen und in meine Tabelle (F16) eingefügt. Eines konnte ich jedoch tun: die Formel so abändern, dass sie sogar in K16 funktioniert, dazu habe ich eine aktuelle Beispiel-Tabelle hochgeladen. Die Ergebnisse in beiden Zellen stimmen nach händischer Überprüfung auf den Cent genau.

Die aktuelle Beispiel-Tabelle geht inhaltlich in der unteren Hälfte weiter. Insgesamt gibts in der kompletten Datei 5 Reiter mit unterschiedlichen Kostenstellen, dazu gehören Versicherungsbeiträge, Bankkonto, Kosten für Telefon, Internet, Abos, Apotheke, Nebeneinkünfte usw. Würde ich deine Formel verstehen, dann könnte ich sie auch für die Zellen D32 und E32 ummodeln. Die Ergebnisse werden jedoch durch irgendeinen Grund verfälscht und ich weiss halt nicht wieso. In diesen letztgenannten Zellen sowie in C19:C30 sind kleine Formeln enthalten, das wäre in etwa mein Excel-Niveau, zzgl. zweier WENN(ISTLEER();"";Mulitplikation) Formeln in einem anderen Reiter. Vielleicht kannst Du mir für die Zellen D32 und E32 noch mal unter die Arme greifen?

https://www.herber.de/bbs/user/170310.xlsx
Anzeige
AW: Mittelwert berechnen am 28. Kalendertag
17.06.2024 15:28:28
Oppawinni
Nun, bei der Formel geht im Wesentlichen um diesen Teil:
DATUM(JAHR($A$3);WENN(TAG(HEUTE())>=28;MONAT(HEUTE());MONAT(HEUTE())-1);3)
mit dem das Vergleichsdatum berechnet wird.
Nur was kleiner oder gleich diesem Datum ist, wird dann bei der Bildung des Mittelwerts berücksichtigt.

Das passiert mit der Formel DATUM( jahr ,monat , tag)
Bei der Formel wird
das Jahr aus dem Datum der Zelle $A$3 entnommen.
der monat wird dann berechnet und zwar,
WENN der TAG des aktuellen Monats >= 28 ist, dann darf der aktuelle Monat (MONAT(HEUTE()) ) mit in die Berechnung,
ansonsten nur bis zum Vormonat, MONAT(HEUTE())-1.
Tag ist hier halt 3, weil Datumsangaben in deiner Tabelle immer der dritte des Monats waren, da kannst du aber auch 28 rein schreiben, aber z.B. nicht 1,
da wäre man sonst noch einen Monat weiter zurück.

Insofern hätte man da wohl auch folgendes schreiben können
DATUM(JAHR($A$3);MONAT(HEUTE());WENN(TAG(HEUTE())>=28;28;1)
Indem man für die Berechnung des Vergleichsdatum aktuelles jahr, aktuellen monat, aber wenn der TAG des Monats >= 28, dann 28, ansonsten 1.

Ich hoffe, dass das einigermaßen verständlich war.
In dem Fall sollte es für dich kein Problem ist, die Formel entsprechend anzupassen.
Wenn es nicht klappt, einfach nochmal melden.
Anzeige
AW: Mittelwert berechnen am 28. Kalendertag
17.06.2024 19:35:13
Pedder
Es geht nur ganz langsam vorwärts *heul* Aber nicht, weil ich die Formeln aus dem Kopf heraus hinschreibe, sondern mir diese nur stückweise per copy & paste mittels Texteditor aus den Formel-Fragmenten deiner letzten Antwort zusammenstückelte und dann die in die Zellen einfüge. Eine Qual. So sieht dann der erste Versuch aus:

=MITTELWERTWENN($A$19:$A$30;"="&DATUM(JAHR($A$19);MONAT(HEUTE());WENN(TAG(HEUTE())>=28;28;1)))

Heraus kommt die Zahl 45.379,6 , was auf ein Datum hinweist: 28.03.2024

Zweiter Versuch:

=MITTELWERTWENN($A$19:$A$30;"="&DATUM(JAHR($A$19);WENN(TAG(HEUTE())>=28;MONAT(HEUTE());MONAT(HEUTE());3);$D$19:$D$30)
Fehler:504.

Dritter Versuch:
=MITTELWERTWENN($A$19:$A$30;"="&DATUM(JAHR($A$19);WENN(TAG(HEUTE())>=28;MONAT(HEUTE());MONAT(HEUTE())-1);3);$D$19:$D$30)
Ergebnis: D32= 1205,60 €, händisch nachgerechnet: 1250,21 €.

Vierter Versuch:
=MITTELWERTWENN($A$19:$A$30;"="&DATUM(JAHR($A$19);WENN(TAG(HEUTE())>=28;MONAT(HEUTE());MONAT(HEUTE())-1);28);$D$19:$D$30)
Ergebnis: D32=-1250,21 € und E32= 17,51€ PASST

Zeit: ~ 120 Minuten. Ganz übel. Aber ich bin hingekommen, immerhin ^^. Damit wäre die Tabelle für den Stand heute komplett. Mittlerweile wirkt die Formel vertrauter, nebulös bleibt vor allem:

das "=" in Anführungsstrichen,

das"&" zwischen zwei WENN

und zum Gipfel der Verwirrung das Ende (für D32 und E32): ;$E$19:$E$30) bzw. ;$D$19:$D$30)

https://www.herber.de/bbs/user/170327.xlsx
Anzeige
AW: Mittelwert berechnen am 28. Kalendertag
17.06.2024 21:14:15
Oppawinni
Naja, die Syntax für
MITTELWERTWENN(Bereich, Kriterien, [Mittelwert_Bereich])
ist halt so und bei deinem ersten Versuch, hast du halt den Mittelwert von Datum gebildet, weil du keinen Mittelwert_Bereich angegeben hast.
Der steht in eckigen Klammern, weil das ein optionaler Parameter der Funktion ist.
Bereich ist halt der Bereich, der auf Kriterien geprüft wrid und....
Kriterien, das ist halt hier ein String. ">4" oder sowas halt. In deinem Fall halt ein zusammengesetzt und Zeichenketten kann man halt zum Beispiel mit dem & conca..ähh zusammenführen.
Ich will da jetzt aber nicht jeden Fehler analysieren.
Es ist aber durchaus von Vorteil, wenn man sich die einzelnen Funktionen einmal separat anschaut.
Davon hat es ja da allerhand Jahr(), Tag(), Monat(), Heute(), Datum( jahr; monat; tag)
Aber vielleicht gehst du mal über Formel - Funktion einfügen und schaust, was es so alles an Funktionen gibt und spielst da mal mit der einen oder anderen Funktion.
Mit der damit gegebenen Hilfestellung sollte das alles machbar sein. Ich hatte zum Beispiel überlegt die Funktion Monatsende zu benutzen, oder EDatum.
Dann wird eine längere, verschachtelte Formel vielleicht auch schneller transparent.
Ich hab halt leider auch nur Excel 2016, insofern hab ich da auch einen Rückstand, den ich wohl mal aufholen muss.....
Office 2021 liegt zwar bereit, aber es installiert sich einfach nicht alleine :o)
Anzeige
AW: Mittelwert berechnen am 28. Kalendertag
18.06.2024 05:26:21
Eifeljoi5
Hallo

Nur als Tipp
Nutze Intelligente Tabellen und setze im Menü ein Häckchen für die Ergebniszeile, dann kannst du dort direkt wählen welche Berechnung statt finden soll (Mittelwert oder Summe oder oder)

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige