Live-Forum - Die aktuellen Beiträge
Datum
Titel
18.04.2024 21:09:11
4 Spalten aus smarter Tabelle in andere smarte Tab. kopieren
18.04.2024 20:45:35
Worksheet_Activate für alle Sheets
18.04.2024 19:30:20
VBA - Tabelle mit Mannschaftsergebnissen sortieren
18.04.2024 18:04:29
Formel fehler Wert#
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1864to1868
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1864to1868
1864to1868
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formel auf Mittelwert umstellen

Formel auf Mittelwert umstellen
13.01.2022 13:46:29
Marcus
Hallo zusammen,
habe hier im Forum super Hilfe gefunden und seit dem folgende Formel im Einsatz
=SUMMENPRODUKT((TEXT($A$16:$A$20000;"MMMM")=BF1)*($C$16:$C$20000=$E$14);$D$16:$D$20000)
Die liefert mir die Summe nach Kriterien - das gleiche im Grunde bräuchte ich jetzt mit dem Mittelwert
Formel Aufbau:
Spalte A = hier stehen die Datums
Zelle BF1 = hier steht das Such-Datum in Textform mit dem in Spalte A gesucht werden soll
Spalte C = hier stehen die Kriterien drin
Zelle E14 = Hier stehe der Such-Wert mit dem in Spalte C gesucht werden soll
Spalte D = hier stehen die Werte drin, wo mit der aktuellen Formel eine Summe gebildet wird und wo ich jetzt den Mittelwert bräuchte.
Hat jemand einen Tipp?
Habe es mit Mittelwertwenns probiert, komme aber nicht zum Ziel
Danke im Voraus
Marcus

22
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel auf Mittelwert umstellen
13.01.2022 13:53:10
{Boris}
Hi,
geht auch mit Deiner bisherigen Formellogik - ungetestet:
=SUMMENPRODUKT((TEXT($A$16:$A$20000;"MMMM")=BF1)*($C$16:$C$20000=$E$14);$D$16:$D$20000) /SUMMENPRODUKT((TEXT($A$16:$A$20000;"MMMM")=BF1)*($C$16:$C$20000=$E$14))
VG, Boris
AW: Formel auf Mittelwert umstellen
13.01.2022 14:10:30
Marcus
@Boris besten dank
wenn ich es richtig verstehe, wird die Summe durch die Anzahl geteilt?
okay
Es funktioniert
Summe/Anzahl = Mittelwert - exakt so ist es ...oT
13.01.2022 14:11:24
{Boris}
VG, Boris
AW: Formel auf Mittelwert umstellen
13.01.2022 15:10:11
Marcus
@Boris,
wie würde die Formel abgeändert werden müssen, wenn ich ohne die Hilfspalte den Mittelwert haben müsste?
also in meinem Beispiel müsste : ($C$16:$C$20000=$E$14) würde ich gerne weglassen, bloß wie stelle ich das an? bzw.
neuer Aufbau:
Spalte A = hier stehen die Datums
Zelle BF1 = hier steht das Such-Datum in Textform mit dem in Spalte A gesucht werden soll
weglassen Spalte C = hier stehen die Kriterien drin
weglassen Zelle E14 = Hier stehe der Such-Wert mit dem in Spalte C gesucht werden soll
Spalte D = hier stehen die Werte drin, wo mit der aktuellen Formel eine Summe gebildet wird und wo ich jetzt den Mittelwert bräuchte.
Gruß
Marcus
Anzeige
Lad doch mal bitte...
13.01.2022 15:54:04
{Boris}
Hi,
...Deine Beispieldatei mit der EXAKTEN Datenstruktur hoch - müssen auch nur ne Handvoll Werte drin sein.
VG, Boris
AW: Lad doch mal bitte...
15.01.2022 20:55:28
Marcus
@Boris,
hier wäre die Datei.
https://www.herber.de/bbs/user/150405.xlsm
ich bräuchte halt den Mittelwert, aber das Suchmonat soll Text bleiben, bitte.
Danke für die zeit und Hilfe
AW: mein Formelvorschlag dafür nun angepasst ...
16.01.2022 09:21:05
neopa
Hallo Marcus,
... an Deine nun etwas geändert vorliegende Tabellenstruktur.
Also in E2:
=MITTELWERTWENNS($C16:$C20000;$A16:$A20000;"&gt"&("1-"&E1&"-"&JAHR($A16))-1;$A16:$A20000;"&lt="&MONATSENDE(--("1-"&E1&"-"&JAHR($A16));0))
und diese nach rechts kopiert.
Allerdings bedürfte es dieser gar nicht, denn die Auswertung ist einfach(er) und flexibler mit einer Pivotauswertung vorzunehmen. Dazu bedarf es nur max ein Dutzend Mausklicks.
Für beide Auswertungsarten empfiehlt sich darüber hinaus, zuvor die Datenliste in eine "intelligente" Tabelle zu wandeln, wozu es auch nur weniger Mausklicks bedarf. Wenn die Formeldefinition an die "intelligente" Datentabelle umgeschrieben, passt sich die Formel danach automatisch an Datensatzerweiterungen an.
Gruß Werner
.. , - ...
Anzeige
AW: mein Formelvorschlag dafür nun angepasst ...
16.01.2022 10:56:29
Marcus
@Werner
dankeschön, funktionieren tut es und das richtig Ergebnis kommt auch.
2 Fragen hätte ich noch:
a. für was steht das "Jahr" in der Formel, wie funktioniert das
b. es darf zwar nicht 2 Jahre in einer Datei erfasst werden, das war mein Wunsch / Vorgabe, aber ich habe jetzt mal Daten für 21 und 22 in einer Spalte und die Formel rechnet nur mit Daten aus 2021, da diese Daten als erstes in der Spalte stehen. Drehe ich die Datenreihe rechnet er nur die 2022 und ignoriert die 2021. ist das so korrekt oder habe ich da was falsch gemacht?
Das was du beschrieben hast:
Für beide Auswertungsarten empfiehlt sich darüber hinaus, zuvor die Datenliste in eine "intelligente" Tabelle zu wandeln, wozu es auch nur weniger Mausklicks bedarf. Wenn die Formeldefinition an die "intelligente" Datentabelle umgeschrieben, passt sich die Formel danach automatisch an Datensatzerweiterungen an.
habe ich Tage probiert und es nie hinbekommen, meine Datei wäre um einiges kleiner und schneller, aber dafür fehlt mir noch Seitenweise Wissen
Danke Marcus
Anzeige
AW: zu Deinen Zusatzfragen ...
16.01.2022 12:00:09
neopa
Hallo Marcus,
... zu a) JAHR() ermittelt aus (vorhandenen echten) Datumswerten das jeweilige Jahr als Ganzzahl.
und zu b) das ist so korrekt. Entscheidend dafür ist, dass mit der aktuellen Formel durch den Formelteil JAHR($A16) das Jahr aus der obersten Datumsangabe geholt wird.
Die intelligente Tabelle erstellst Du wie folgt:
- deaktiviere Deine vorhandene Filterung
- aktiviere eine Zelle innerhalb des Datentabellenbereiches.
- betätige die Tastenkombination [StrG] +[t]
- bestätige dort [ok] mit [Enter]
Es entsteht eine "formatierte Tabelle", die im Namensmanager zunächst automatisch den Namen "Tablle1" erhält.
Die Formel nun dafür umschreiben.
Gruß Werner
.. , - ...
Anzeige
AW: ergänzende Hinweise ...
16.01.2022 13:03:03
neopa
Hallo Marcus,
... ich hatte vergessen Dir mitzuteilen, dass ich in Deiner eingestellten Tabelle die Daten in A14 C14 gelöscht hatte.
Die können natürlich stehen bleiben. Dann musst Du allerdings den Datenbereich für die zu "formatierende Tabelle" von =$A$6:$C$73 auf =$A$16:$C$73 abändern.
Die Formel für die Auswertung der "intelligenten" Tabelle lautet dann in E2 z.B. so:
=MITTELWERTWENNS(INDEX(Tabelle2;;3);INDEX(Tabelle2;;1);"&gt"&("1-"&E1&"-"&JAHR($A16))-1;INDEX(Tabelle2;;1);"&lt="&MONATSENDE(--("1-"&E1&"-"&JAHR($A16));0))
In der Formel verwende ich INDEX(), damit die Formel auch nach rechts ziehend kopiert werden kann. Das geht auch alternativ etwas anders.
Gruß Werner
.. , - ...
Anzeige
AW: ergänzende Hinweise ...
16.01.2022 17:43:47
Marcus
@Werner,
dickes Danke vorab.
Ich habe (ich wies nicht wie) beide Wege in 2 Musterdateien hingebracht. Bin leicht stolz auf mich :)
Jetzt stell ich mir Noob die Frage, was ist besser für eine Datei?
a. intelligente Tabelle
b. normale Tabelle
was braucht weniger Performens, den bei 260 Sheets denke ich spielt das eine Rolle?
Danke nochmal
AW: besser? Für mich die "intelligente" Tab. owT
16.01.2022 17:48:36
neopa
Gruß Werner
.. , - ...
AW: besser? Für mich die "intelligente" Tab. owT
16.01.2022 18:20:08
Marcus
ok werde ich umstellen
jetzt noch eine Frage wo baue ich bei deiner Formel
=MITTELWERTWENNS($C16:$C20000;$A16:$A20000;">"&("1-"&BF1&"-"&JAHR($A16))-1;$A16:$A20000; " das Fehlerabfangen ein? also wenn das Ergebnis #DIV/0! ist?
das was ich normal verwende:
wennfehler soll man ja nicht mehr benutzten? bzw. bei der Formel habe ich keinen Schimmer wo. Da ich aber mit #DIV/0! nicht weiter rechnen kann, habe ich wieder eine unüberwindliche Aufgabe für mich gefunden
in der intelligenten Version kommt der gleiche Fehler
Anzeige
AW: dafür ist WENNFEHLER() da, ...
16.01.2022 18:52:06
neopa
Hallo Markus,
... wo hast Du denn die Information "wennfehler soll man ja nicht mehr benutzten" her?
Anwendung hier ist einfach so und zwar für beide Formel gleich: =WENNFEHLER( MITTELWERTWENNS(...);"")
Gruß Werner
.. , - ...
AW: dafür ist WENNFEHLER() da, ...
16.01.2022 19:28:25
Marcus
@Werner
einer vom Verein meinte man soll =WENN(ISTFEHLER verwenden
habe die Formel so umgeändert:
=WENNFEHLER(MITTELWERTWENNS($C16:$C20000;$A16:$A20000;">"&("1-"&E1&"-"&JAHR($A16))-1;$A16:$A20000; " scheint zu funktionieren, wenn des so weiter geht kann ich das in 3-4 Jahren auch alleine :)
Danke
Anzeige
WENN(ISTFEHLER(...
16.01.2022 21:52:00
{Boris}
Hi,
...ist ein Relikt VOR Excelversion 2007. Demnach scheint Dein Vereinspartner noch etwas antiquiert unterwegs zu sein ;-)
VG, Boris
AW: ergänzende Hinweise ...
16.01.2022 18:14:11
Marcus
@Werner,
dickes Danke vorab.
Ich habe (ich wies nicht wie) beide Wege in 2 Musterdateien hingebracht. Bin leicht stolz auf mich :)
Jetzt stell ich mir Noob die Frage, was ist besser für eine Datei?
a. intelligente Tabelle
b. normale Tabelle
was braucht weniger Performens, den bei 260 Sheets denke ich spielt das eine Rolle?
Danke nochmal
AW: doppelt owT
16.01.2022 18:48:26
neopa
Gruß Werner
.. , - ...
AW: doppelt owT
16.01.2022 18:51:02
Marcus
Fehler von mir, sorry war mir nicht sicher ob schon gesendet oder nicht.
Anzeige
AW: da stellt sich noch folgende Frage, ...
13.01.2022 16:13:54
neopa
Hallo Marcus,
... willst Du wirklich eine Mittelwertauswertung für einen in BF1 angegebenen Monat über alle Jahre vornehmen oder doch nur für ein Jahr und in Spalte A stehen nur nur Datumswerte aus einem Jahr? Wenn letzteres der Fall sein sollte, kann man auch mit MITTELWERTWENNS() auswerten ob mit oder ohne Kriteriumsberücksichtigung in Spalte C.
Gruß Werner
.. , - ...
AW: da stellt sich noch folgende Frage, ...
14.01.2022 09:16:53
Marcus
@ Werner,
Die Datei enthält immer nur Daten für 1 Jahr. Muss jedes Jahr neu gemacht werden.
AW: dann ...
14.01.2022 15:35:47
neopa
Hallo Marcus,
... sollten folgende Formeln zum angestrebten Ergebnis führen.
Mit Bedingungsprüfung:
=MITTELWERTWENNS(D16:D20000;A16:A20000;"&gt"&("1-"&BF1&"-"&JAHR(A16))-1;A16:A20000;"&lt="&MONATSENDE(--("1-"&BF1&"-"&JAHR(A16));0);C16:C20000;E14)
und ohne dann entsprechend so:
=MITTELWERTWENNS(D16:D20000;A16:A20000;"&gt"&("1-"&BF1&"-"&JAHR(A16))-1;A16:A20000; "&lt="&MONATSENDE(--("1-"&BF1&"-"&JAHR(A16));0))
Gruß Werner
.. , - ...
Anzeige

233 Forumthreads zu ähnlichen Themen

Monatsnamen in englischer Schreibweise
Helmut

Hallo Excel-VBA-Profis,
ich möchte gerne in VBA den Monatsnamen des Vormonats in englischer Sprache ermitteln.
Habe folgendes Makro hierzu (allerdings in deutscher Schreibweise):
Monat = Format(DateValue("01." & Month(Date) & "." & Year(Date)) - 1, "MMMM")
Vielen Dan...
19.06.2010 21:41:09
Thread-ID: 1162907
4 Beiträge
Anzeige
Formel auf 3 Spalten umstellen -Summenprodukt
Wolfgang

Hallo,
nachstehende Formel entdeckte ich unter Rechereche und würde sie gerne in zwei Richtungen umstellen. Irgendwie gelingt mir das aber nicht. Ich würde gerne erreichen, dass bei Ablauf eines Codes im Anschluss der nachstehende Code läuft und die Positionen zählt, wenn in Spalte B2:B400...
09.02.2011 20:52:42
Thread-ID: 1199931
8 Beiträge
Kalender aus UserForm auf Englisch umstellen
S.

Hallo Zusammen,
Ist es möglich die Angaben aus dem Kalender UserForm aus der Datei:
http://hajo-excel.de/chCounter3/getfile.php?id=108
auf English umzustellen?
Ich dachte, dass könnte ich noch irgendwie mit Format ([$-809] MMMM YYYY) hinkriegen
aber ich finde den Ort wo ich...
31.07.2013 21:00:19
Thread-ID: 1325114
2 Beiträge
Anzeige
Zahlenformat Punkt statt Komma
Lilli

Hallo!Ich habe folgendes Problem:
Im Ursprungsfeld habe ich ein Datumsformat JJJJMM. In einem Makro erzeuge ich einen String im Format "MM.JJJJ", was soweit funktioniert. Nun will ich diesen String in einer anderen Zelle ausgeben und dort erscheint dann die Zahl MM,JJJJ. Hinzu kommt, dass fü...
09.10.2002 20:35:38
Thread-ID: 167861
7 Beiträge
zahlenformate (punkt statt komma)
olaf

hallo zusammen,ich habe ein kleines problem:in Zelle A1 habe ich eine zahl stehen die bis zu vier stellen vor dem komma haben kann und zwei stellen nach dem komma haben kann ( z.b. die zahl 1200,20 / oder jedoch -0,50 möglich)diese zahl muss ich nun statt mit dem komma mit einem punkt haben(120...
11.10.2002 12:37:37
Thread-ID: 168442
7 Beiträge
VBA-Code von Excel 97 deutsch nach englisch
Carlos

Hi,
ich habe folgendes Problem:
Ich erstelle z.Z. ein VBA-Tool für Excel 97 in einer deutschen Version.
Mit diesem Tool sollen aber auch Leute im europäischen Ausland und in den USA arbeiten können.
Ist das möglich und wenn ja, was muss ich berücksichtigen bzw. beachten?
Dank und...
17.10.2002 14:25:29
Thread-ID: 170407
2 Beiträge
Zahlen in hexadezimaler Schreibweise
Andreas

Hallo,ich benötige in einer Tabelle Zahlen in folgendem Format:Spalte1 Spalte2255 0x000000FF
254 0x000000FE
253 0x000000FD
...wobei die Zahlen in Spalte 2 automatisch aus den in Spalte 1 generiert werden sollen...
28.02.2003 15:09:47
Thread-ID: 225675
3 Beiträge
Zahlen: Punkt wird nicht als Komma erkannt!
jockel

Hallo, ich importiere eine Text-Datei in der Zahlen abgespeichert sind, wovon einige Kommazahlen sind, getrennt durch Punkt.
z.B.
1,15,10.15,12.15,...Scheiß-Excel denkt nicht daran "12.15" als "12,15" zu interpretieren, sondern macht mir "1215" draus!
Finde keine Einstellmöglichkeite...
03.03.2003 14:31:42
Thread-ID: 226536
2 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
3
3
7
1
3
6
6
1
2
1
2
3
3
3
4
2
3
1
1
6
1
9
1
2
7