[cunster]

Hallo,
ich habe eine Excel Tabelle erstellt, in der Werte eingegeben sind. In der Mappe KPI_Auswertung möchte ich in Zelle E4 den Durchschnitt berechnen.
Momentan berechnet sich der Werte aus Mappe Datenbasis Spalte Z für den jeweiligen Monat(in diesem Fall 9).
15.031.457:22=683.248
Das ist ja falsch, da Tage mit einbezogen werden, die noch keinen Wert haben.

Richtig wäre die Rechnung:
15.031.457:8=1.878.932

Wie kann ich diese Berechnung richtig ausführen?
Kann man evtl. andere Berechnungen vereinfachen oder sicherer gestalten, sodass Fehler vermieden werden.

Hier findet ihr die Tabelle:
[ Link nur für registrierte Mitglieder sichtbar. Bitte einloggen oder neu registrieren ]


schöne Grüße

[tomcrswer]

- Berechne die Summe aus Spalte Z für den entsprechenden Monat.
- Berechne die Anzahl der Tage dieses Monats.
- mit summewenns(Bereich; "") kannst du die leeren Zellen bestimmen

mittelwert=Summe : (anzahl tage - anzahl leere zellen)

[cunster]

Hast du das ne konkrete Berechnung für mich. Stehe gerad n bissel aufn Schlauch.

[tomcrswer]

[ Link nur für registrierte Mitglieder sichtbar. Bitte einloggen oder neu registrieren ]

Zelle H10 folgende.
Ich weiß allerdings nicht, ob das mit deiner Matrixschreibweise kompatibel ist, damit kenne ich mich nicht aus.

[eitch100]

Also wenn deine Tabelle immer weiter läuft kannst natürlich nicht nur nach dem Monat suchen, sondern musst das Jahr in die Suche mit einschließen... Hier die Matrixformel für E4 ohne Jahr...

{=MITTELWERT(WENN(Datenbasis!D:D=KPI_Auswertung!D4;WENN(Datenbasis!Z:Z<>"";Datenbasis!Z:Z)))}

Hier mit Jahr...

{=MITTELWERT(WENN(Datenbasis!D:D=KPI_Auswertung!D4;WENN(Datenbasis!E:E=KPI_Auswertung!C3;WENN(Datenbasis!Z:Z<>"";Datenbasis!Z:Z))))}

Denke dran, falls du die Formel kopierst, dass du sie mit Strg+Shift+ENTER abschließt... geht auch alles ohne Matrixformel... bei Bedarf sag Bescheid...

P.S. die Anzahl der Zellen, die im jeweiligen Monat nicht leer sind, kann man übrigens folgendermaßen einfacher berechnen. Der große Vorteil: man kann mehrere Kriterien verarbeiten, also z.B. gegebenenfalls das Jahr in die Suche einschließen...

ohne Jahr...
=SUMMENPRODUKT((Datenbasis!D:D=KPI_Auswertung!D4)*(Datenbasis!Z:Z<>""))

mit Jahr...
=SUMMENPRODUKT((Datenbasis!D:D=KPI_Auswertung!D4)*(Datenbasis!E:E=KPI_Auswertung!C3)*(Datenbasis!Z:Z<>""))

[cunster]

Danke euch beiden.
eitch100, ich habe versucht deine Formel einzufügen aber leider tut sich bei der Formel nichts.
Hättest du Lust und Zeit dies in die Datei einzutragen und denn hoch zu laden?

[eitch100]

Welche Formel? Die unteren beiden zählen nur die Zellen die nicht leer sind...
Die oberen beiden sind Matrixformeln... du musst ans Ende der Formel klicken und dann Strg+Shift+Enter drücken, damit Excel die geschweiften Klammern hinzufügt...

Wenn du die Formeln nicht nachvollziehen kannst, bringt dir das auch nicht viel, wenn ich sie dir einfüge, weil du sie nicht reproduzieren kannst bei Veränderungen.... hochladen würde ich sie dir natürlich... mit oder ohne Jahresabfrage?

[cunster]

Mit Jahresabfrage wäre cool.
Danke für die Mühe!
Aber ich versuche es trotzdem auch nachzuvollziehen.
In welche Zelle soll die Formel rein?

[eitch100]

Die zweite Formel in E4 sollte ihren Zweck erfüllen... ist im Grunde dieselbe, die vorher schon drin, außer das 2 WENN-Abfragen mehr drin sind, um einmal das Jahr aus C3 mit Spalte E:E abzufragen und ob die Zelle in Spalte Z:Z leer ist...

Und nochmal... du musst die Formel mit STRG+Shift+Enter abschließen. Kopiere die Formel ohne die geschweiften Klammern!!! Die geschweiften Klammern manuell einfügen bringt nichts...

[cunster]

Ok ich habe die geschweiften Klammern mit kopiert.
Jetzt klappt es. Super danke
Jetzt wo die Formel in excel ist, kann ich auch deine Schritte nachvollziehen.

[cunster]

Ich wollte die Formel auch für die Zelle E5 nehmen.
Hab in der Formel D5 statt D4 genommen aber ich bekommen #Div/0.
In der Zelle D5 hab ich ja eine Formel und daran liegt es. Wenn ich zum Beispiel den Monat als Zahl eingebe, bekomme ich das korrekte Ergebnis. Hast du vielleicht noch ne Idee wie in die Formel in D5 anders gestalten kann?

[eitch100]

das kommt weil das eigentliche Ergebnis deiner Formel in D5 nicht 8 ist sondern 42247. Siehst du, wenn du die Formatierung von D5 auf Standard machst...

Gib in D5 folgende Formel ein und das funzt dann...
=D4-1

oder anstatt =MONATSENDE(B12;-1) nur =MONAT(B12)-1
(gibt aber in allen Fällen Probleme, wenn der Monat Januar ist, da der vorherige Monat der 12. des Vorjahres ist...)

P.S. Wenn in Datenbasis in Spalte Z in dem gesuchten Monat keine Werte sind, kommt diese Fehlermeldung, weil eine Division durch 0 mathematisch nicht möglich ist...

mit dieser Formel würde bei fehlenden Werten dort dann "Keine Werte vorhanden" stehen...

{=WENNFEHLER(MITTELWERT(WENN(Datenbasis!D:D=KPI_Auswertung!D5;WENN(Datenbasis!E:E=KPI_Auswertung!C3;WENN(Datenbasis!Z:Z<>"";Datenbasis!Z:Z))));"Keine Werte vorhanden")}

Edit: So würde ich das mit dem Vormonat lösen...
[ Link nur für registrierte Mitglieder sichtbar. Bitte einloggen oder neu registrieren ]

Und gleich noch etwas zur Formel! Beim Mittelwert sucht die Formel im Moment nach Zellen in Spalte Z die nicht leer sind. Solltest du also eine NULL eintragen, ist die Zelle nicht leer und er teilt beim Mittelwert durch einen höheren Wert. Kannst du ausprobieren, wenn du z.B. in Datenbasis Z183 mal eine Null eingibst...