In einer Tabelle sollen die Werte der Spalte A über den Mittelwert der Vergleichszahlen aus den Vorjahren (Spalten B-J) berichtigt werden. Das führt in den Fällen zu einem Fehler, in denen nur unzureichende Vergleichszahlen zur Verfügung stehen.
Beispiel:
Der Ausgangswert in Zelle A98 wird durch Zuschlag aus dem Mittelwert der Differenzen B98-B97, C98-C97, D98-D97, E98-E97, F98-F97, G98-G97, H98-H97, I98-I97 und J98-J97 bereinigt (L98). Das funktioniert aber in der darüber liegenden Zeile 97 deshalb nicht, weil der Wert in E96 Null ist.
Wie können die Formeln in Spalte L so abgewandelt werden, dass die Fehler in L25, L49, L73 und L97 vermieden werden ?
Für Vorschläge bedanke ich mich schon jetzt.
Hardbopper
[
attachment=38063]
Hallöchen,
nach 56 Beiträgen sollte man eigentlich wissen, dass man Fragen im Fachforum stellt - z.B. Excel - und nicht im Beispielbereich
[
attachment=38065]
Entschuldigung, bin verrutscht
Hardbopper
Hallo,
z.B so (Matrixformel):
| Arbeitsblatt mit dem Namen 'Tabelle1' |
| A | B | C | D | E | F | G | H | I | J | K | L | M |
| 1 | 82160,00 | 0,00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | |
| 2 | 82255,00 | 0,00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 82160,00 | 82160,00 |
| 3 | 82336,70 | 0,00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 82255,00 | 82255,00 |
| 4 | 82429,30 | 0,00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 82336,70 | 82336,70 |
| 5 | 82520,80 | 0,00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 82429,30 | 82429,30 |
| 6 | 82591,20 | 0,00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 82520,80 | 82520,80 |
| 7 | 82680,20 | 0,00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 82591,20 | 82591,20 |
| 8 | 82773,70 | 0,00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 82680,20 | 82680,20 |
| Zelle | Formel |
| L2 | =A1+MITTELWERT(B2-B1;C2-C1;D2-D1;E2-E1;F2-F1;G2-G1;H2-H1;I2-I1;J2-J1) |
| M2 | {=A1+MITTELWERT((B2:J2-B1:J1)*(B2:J2>0)*(B1:J1>0))} |
| Achtung, Matrixformel enthalten! |
| Die geschweiften Klammern{} werden nicht eingegeben. |
| Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine. |
| Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010 |
| Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
Verwende statt MITTELWERT(B2-B1;C2-C1;D2-D1;...) das:
MITTELWERT((B2:J2-B1:J1))
(als Matrixformel)
Und durch den Zusatz
*(B2:J2>0)*(B1:J1>0) wird der Mittelwert der Differenzen zwischen den beiden Zeilen nur dann gebildet, wenn die Werte in beiden Zeilen jeweils größer 0 sind
Gruß
Fred
Vorweg schon einmal danke, Fred. Ich werde die Formel gleich nach Ostern ausprobieren.
Gruß und schöne Ostern
Hardbopper
Frage, ob deine Berechnung wirklich so gewünscht ist:
Berechnung von A26:
zwischen B25 und B26 hast du eine Differenz von 95, in den Spalten C-J nur Nullwerte.
Soll nun der Durchschnitt nur von diesem einen Wertepaar B25 und B26 berücksichtigt werden also 95 (Nullwerte unberücksichtigt)
oder der Durchschnitt von 95 und 8 mal 0 also 95/9?
Hallo Bug99,
die Tabelle ist etwas verkürzt, weil andere Daten, nämlich Datumswerte, für das aufgeworfene Berechnungsproblem keine Rolle spielen. Im Original stehen sie jeweils neben einem Zahlenwert. Dadurch ist erkennbar, dass es darum geht, aktuelle Wert mit denen zu vergleichen, die exakt jeweils ein oder mehrere Jahre zurückliegen. Wo 0-Werte stehen, gibt es keine vergleichbaren Daten, sodass sie bei der Berechnung außer Betracht bleiben können.
Hardbopper
Hallo Fred11,
Deine Formel funktioniert einwandfrei, nochmals vielen Dank.
Hardbopper
(04.04.2021, 23:41)Hardbopper schrieb: [ -> ]Wo 0-Werte stehen, gibt es keine vergleichbaren Daten, sodass sie bei der Berechnung außer Betracht bleiben können.
ok,
aber deine 0-Werte bleiben so ja nicht außer Betracht :)
da sie Einfluss auf den ermittelten Durchschnittswert haben.
Hallo bug99,
danke, dass Du weiter insistiert und mich dadurch dazu gebracht hast, die ursprünglich angehängte Tabelle noch mal zu überprüfen. Mein Fehler liegt darin, dass ich Zellen mit 0-Werten fälschlicherweise einbezogen habe in der Annahme, 0-Werte blieben bei der Mittelwertformel automatisch unberücksichtigt. Fred11 ist kein Vorwurf zu machen, er hat nur die Formel vereinfacht, die ich vorgegeben hatte.
Richtig ist folgendes (siehe
neue verkürzte
Anlage):
Wenn der zu erwartende Wert in A3 mit Hilfe der Vergleichswerte aus den Vorjahren (B2:C3) ermittelt werden soll, dürfen die 0-Werte in D2:T3 nicht in der Mittelwertberechnung erscheinen, weil sie das Ergebnis verfälschen (siehe E9 / I9).
Dich, Fred11, möchte ich bitten, wenn es nicht zu viel Mühe macht, Deine Formel so anzupassen, dass das Ergebnis in I7 dabei herauskommt.
Danke und Entschuldigung für meinen Gedankenfehler,
Hardbopper
[
attachment=38115]
Hallo Hardbopper,
ja, der Einwand von bug99 ist berechtigt...
Die Funktion MITTELWERT behandelt eine 0 wie jede andere Zahl auch, und bezieht sie in die Mittelwertberechnung mit ein.
(Nur
leere Zellen, werden nicht mit einbezogen)
Ich habe da tatsächlich in meinem Lösungsvorschlag aus #4 nicht weiter darüber nachgedacht, sondern mich nur hierauf konzentriert:
Zitat:Wie können die Formeln in Spalte L so abgewandelt werden, dass die Fehler in L25, L49, L73 und L97 vermieden werden ?
Wenn die 0-Werte bei der Berechnung des Mittelwertes nicht miteinbezogen werden sollen, muss die Formel entsprechend angepasst/ergänzt werden (
Matrixformel - Eingabe mit Strg+Shift+Enter):
=A1+WENN(SUMME(B1:J1)>0;MITTELWERT(WENN((B1:J1>0);(B2:J2-B1:J1)));0)
siehe angehängte Beispieldatei (basierend auf deiner ersten Beispieldatei aus #1)
Gruß
Fred