Hallo zusammen,
ich habe folgendes Problem:
Ich habe in Spalte A eine Reihe von Sportläden. In Spalten B bis ZZ habe ich die Monatsumsätze jedes Ladens. Ich möchte nun den Durchschnittlichen Monatsumsatz jedes Ladens aus den letzten
12 Monaten ermitteln. Gezählt werden sollen nur die Monate, in denen der Laden auch einen Wert hat. Es kann nämlich sein, dass ein Laden zwischenzeitlich geschlossen hatte und keinen Umsatz gemacht hat. Hat ein Store weniger als 12 Monate mit Umsätzen, sollten der Durchschnitt der gültigen Monate verwendet werden.
Beispiel:
Store A: 10 10 10 10 - - 20 20 20 20 20 20 20 20 20 = 17,5 ((9*20 + 3*10)/12)
Store B: 10 10 10 20 20 20 20 20 20 20 20 20 20 20 20 = 20 (12*20 /12)
Store C: - - - - - - - - - - - - - - - - - 20 40 = 30 ((20 +40) /2
Irgendeine Idee wie ich das hinbekomme?
Vielen Dank schon einmal an jeden, der sich die Mühe macht hier eine Lösung zu finden! 
Hallo
wenn du mit Formeln arbeiten willst auf die schnelle eine Idee. Verwende in einer Hilfsspalte COUNTA
Damit kannst du die Anzahl der Eintrage in den Spalten Jnauar - Dezember feststellen. Es dürfen aber keine Nullen drin sein!
mfg Gast 123
Hallo Itzeberg,
z.B. so, mit zwei Hilfsspalten zu Ermittlung von Beginn und Ende des zu berücksichtigenden Zellbereiches:
| Arbeitsblatt mit dem Namen 'MTD' |
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V |
| 8 | | Hilfsspalten | | | | | | | | | | | | | | | | | | | | |
| 9 | | erste | letzte | Total | Gewünschtes | | Total | Total | Total | Total | Total | Total | Total | Total | Total | Total | Total | Total | Total | Total | Total | Total |
| 10 | Jan_Periodic 2019 | Spalte | Spalte | Run-rate | Ergebnis | | Net Sales | Net Sales | Net Sales | Net Sales | Net Sales | Net Sales | Net Sales | Net Sales | Net Sales | Net Sales | Net Sales | Net Sales | Net Sales | Net Sales | Net Sales | Net Sales |
| 11 | Store A | 5 | 16 | | 151 | | 126 | 86 | 140 | 183 | 188 | 116 | 186 | 164 | 183 | 164 | 183 | 117 | 171 | 117 | 51 | 171 |
| 12 | Store B | 3 | 16 | | 149 | | 126 | 86 | 140 | 183 | 188 | 116 | 186 | | | 164 | 183 | 117 | 171 | 117 | 51 | 171 |
| 13 | Store C | 1 | 16 | | 139 | | | | | | | | | | | 164 | 183 | 117 | 171 | 117 | 51 | 171 |
| Zelle | Formel |
| B11 | =MAX(1;VERGLEICH(-1;$G11:$AAB11;-1)-11-ANZAHLLEEREZELLEN(INDEX($G11:$AAB11;1):INDEX($G11:$AAB11;$C11))) |
| C11 | =VERGLEICH(-1;$G11:$AAB11;-1) |
| E11 | =MITTELWERT(INDEX($G11:$AAB11;$B11):INDEX($G11:$AAB11;$C11)) |
| Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010 |
| Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
(Formeln dann nach unten ziehen)
Funktioniert nur (richtig), wenn die Umsatzwerte positiv sind; wenn hier auch mal negative Werte vorkommen können, müssen die Formeln etwas modifiziert werden
Das ganze geht natürlich auch ohne die beiden Hilfsspalten; man kann das alles auch direkt in die MITTELWERT-Formel reinpacken, aber dann wird die Formel halt sehr lang und unübersichtlich...
Gruß
Fred
Moin!
Ich bin an die Aufgabe gegangen, ohne die bisherigen Lösungen zu sehen, weil es ja wirklich interessant ist.
War dann tatsächlich sehr nah an LCohens Lösung.
Da meine Formel (INDEX(11:11;;
0)) in einen Fehler lief, wenn weniger als 12 Einträge vorhanden sind, habe ich mir mit MAX() beholfen:
=MITTELWERT(ZZ11:INDEX(11:11;;MAX(5;KGRÖSSTE((E11:ZZ11<>"")*SPALTE(E11:ZZ11);12))))
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O |
| 9 | | Total | gew. | | Total | Total | Total | Total | Total | Total | Total | Total | Total | Total | Total |
| 10 | | Run-rate | Erg | | TNS | TNS | TNS | TNS | TNS | TNS | TNS | TNS | TNS | TNS | TNS |
| 11 | Store A | 151 | 151 | | 126 | 86 | 140 | 183 | 188 | 116 | 186 | 164 | 183 | 164 | 183 |
| 12 | Store B | 149 | 149 | | 126 | 86 | 140 | 183 | 188 | 116 | 186 | | | 164 | 183 |
| 13 | Store C | 139 | 139 | | | | | | | | | | | 164 | 183 |
| Zelle | Formel |
| B11 | =MITTELWERT(ZZ11:INDEX(11:11;;MAX(5;KGRÖSSTE((E11:ZZ11<>"")*SPALTE(E11:ZZ11);12)))) |
| B12 | =MITTELWERT(ZZ12:INDEX(12:12;;MAX(5;KGRÖSSTE((E12:ZZ12<>"")*SPALTE(E12:ZZ12);12)))) |
| B13 | =MITTELWERT(ZZ13:INDEX(13:13;;MAX(5;KGRÖSSTE((E13:ZZ13<>"")*SPALTE(E13:ZZ13);12)))) |
Gruß Ralf
Ganz herzlichen Dank an alle Helfer!
Ich bin mit der Formel von LCohen / RPP63 gegangen, weil ich da am wenigsten an der Datenstruktur ändern muss.
Danke euch!
Dann habe ich mich für Dich anscheinend missverständlich ausgedrückt. C:ZZ sind IMMER mehr als 12 Werte, weil sie in 0 oder Zahl gewandelt werden, jedoch nicht leer bleiben.