Microsoft Excel

Herbers Excel/VBA-Archiv

Abweichung eines Wertes vom Durchschnitt | Herbers Excel-Forum


Betrifft: Abweichung eines Wertes vom Durchschnitt von: Jens
Geschrieben am: 22.01.2010 08:55:46

Hallo zusammen,

ich habe ein Problem bei der Bestimmung der Abweichung eines Wertes vom Durchschnitt der restlichen Werte.

https://www.herber.de/bbs/user/67408.xls

Dabei werden die Werte von B2 ausgehend nach rechts eingetragen, d.h. der letzte eingetragene Wert steht immer rechts (hier I2=200)

ich möchte in O2 ausgegeben haben, wieviel der jeweils letzte Wert vom Durchschnitt der vorherigen Werte (hier B2:H2) abweicht.

Gruß Jens

  

Betrifft: AW: Abweichung eines Wertes vom Durchschnitt von: Hajo_Zi
Geschrieben am: 22.01.2010 09:04:33

Hallo Jens,


Tabelle1

 AB
1BezeichnungQ1 08
2Gesamtergebnis100
3Mittelwert140
4letzter0
5Abweichung140

verwendete Formeln
Zelle Formel
B3 =MITTELWERT(B2:IV2)
B4 {=INDEX(Tabelle1!2:2;MAX(SPALTE(Tabelle1!2:2)*(Tabelle1!2:2<>"")))}
B5 =B3-B4
{} Matrixformel mit Strg+Umschalt+Enter abschließen

Tabellendarstellung in Foren Version 5.4



GrußformelHomepage


  

Betrifft: AW: Abweichung eines Wertes vom Durchschnitt von: welga
Geschrieben am: 22.01.2010 09:22:20

Hallo,

schreibe in die C3 einfach =WENN(C2="";"";C2-MITTELWERT($B$2:B2)) und kopiere die nach rechts.

Dann schreibe in O2:
{=INDEX(Tabelle1!3:3;MAX(SPALTE(Tabelle1!3:3)*(Tabelle1!3:3<>"")))}
Eingabe mit STRG+Umschalt+Enter, damit du eine Matrix-Formel draus machst.

Gruß
welga


  

Betrifft: AW: Abweichung eines Wertes vom Durchschnitt von: Tino
Geschrieben am: 22.01.2010 09:16:54

Hallo,
ich habe es so gelöst.

 ABCDEFGHIJKLMNO
1BezeichnungQ1 08Q2 08Q3 08Q4 08Q1 09Q2 09Q3 09Q4 09Q1 10Q2 10Q3 10Q4 10  
2Gesamtergebnis1002003002501002502802000000 11

Formeln der Tabelle
ZelleFormel
O2=MITTELWERT(BEREICH.VERSCHIEBEN($B$2;0;0;;VERGLEICH(1E-300;$B$2:$M$2;-1)-1))-INDEX($B$2:$M$2;1;VERGLEICH(1E-300;$B$2:$M$2;-1))



Gruß Tino


  

Betrifft: AW: Abweichung eines Wertes vom Durchschnitt von: Hajo_Zi
Geschrieben am: 22.01.2010 09:25:10

Hallo Tino,

0 ist auch ein Wert, bei <>0 würde ich was anderes rausbekommen
Tabelle1

 AB
1BezeichnungQ1 08
2Gesamtergebnis100
3Mittelwert210
4letzter200
5Abweichung10

verwendete Formeln
Zelle Formel
B3 {=MITTELWERT(WENN(B2:IV2<>0;B2:IV2))}
B4 {=INDEX(Tabelle1!2:2;MAX(SPALTE(Tabelle1!2:2)*(Tabelle1!2:2<>"")*(Tabelle1!2:2<>0)))}
B5 =B3-B4
{} Matrixformel mit Strg+Umschalt+Enter abschließen

Tabellendarstellung in Foren Version 5.4



Gruß Hajo


  

Betrifft: @Hajo ... von: Tino
Geschrieben am: 22.01.2010 09:42:09

Hallo,
er schreib
'wieviel der jeweils letzte Wert vom Durchschnitt der vorherigen Werte (hier B2:H2) abweicht.'

Ich habe den Mittelwert von B2 bis H2 (=211) verwendet und davon die 200 abgezogen.

 AB
5Mittelwert211,428571
6letzter200
7Abweichung11,4285714

Formeln der Tabelle
ZelleFormel
B5=MITTELWERT(BEREICH.VERSCHIEBEN($B$2;0;0;;VERGLEICH(1E-300;$B$2:$M$2;-1)-1))
B6=INDEX($B$2:$M$2;1;VERGLEICH(1E-300;$B$2:$M$2;-1))
B7=B5-B6


Du verwendest den Mittelwert von B2 bis I2.

Gruß Tino


  

Betrifft: Minuend und Subtrahend verdauscht von: Tino
Geschrieben am: 22.01.2010 09:52:46

Hallo,
so rum müsste es richtiger sein.

 AB
5Mittelwert211,428571
6letzter200
7Abweichung-11,4285714
8oder eine Formel-11,4285714

Formeln der Tabelle
ZelleFormel
B5=MITTELWERT(BEREICH.VERSCHIEBEN($B$2;0;0;;VERGLEICH(1E-300;$B$2:$M$2;-1)-1))
B6=INDEX($B$2:$M$2;1;VERGLEICH(1E-300;$B$2:$M$2;-1))
B7=B6-B5
B8=INDEX($B$2:$M$2;1;VERGLEICH(1E-300;$B$2:$M$2;-1))-MITTELWERT(BEREICH.VERSCHIEBEN($B$2;0;0;;VERGLEICH(1E-300;$B$2:$M$2;-1)-1))


Gruß Tino


  

Betrifft: AW: Minuend und Subtrahend verdauscht von: Jens
Geschrieben am: 22.01.2010 11:22:01

Hallo Tino,

Deine Tipp funktioniert ganz hervorragend. Ich konnte die Zusammenhänge in der Formel auch nachvollziehen, bis auf "1E-300" im bei Vergleich(1E-300;...) Kannst Du mir das näher erklären?

Gruß Jens


  

Betrifft: AW: Minuend und Subtrahend verdauscht von: Jens
Geschrieben am: 22.01.2010 11:38:09

noch drei Fragen dazu:

1. wie muß ich die Formel abändern wenn ich nur die jeweils letzten 4 quartale (hier E2:H2) vor dem aktuellen Eintrag (hier I2) in die Durchschnittsberechnung einbeziehen möchte?
2. ich erhalte ein #NV wenn eine Null in der ersten auszuwertenden Zelle steht. Wie kann ich das abstellen?
3. ich erhalte ein #BEZUG wenn eine Null irgendwo in den auszuwertenden Zellen steht. Wie kann ich das abstellen?

Gruß Jens


  

Betrifft: müsste so funktionieren von: Tino
Geschrieben am: 22.01.2010 12:17:07

Hallo,
wie wäre es hiermit?

 ABCDEFGHIJ
1BezeichnungQ1 08Q2 08Q3 08Q4 08Q1 09Q2 09Q3 09Q4 09Q1 10
2Gesamtergebnis1002003002501002502802000
3          
4          
5          
6          
7 Ab B2letzten 4       
8Mittelwert211,428571220       
9letzter200200       
10Abweichung-11,4285714-20       
11oder eine Formel-11,4285714-20       

Formeln der Tabelle
ZelleFormel
B8=MITTELWERT(BEREICH.VERSCHIEBEN($B$2;0;0;;VERGLEICH(1E-300;$B$2:$M$2;-1)-1))
C8=MITTELWERT(BEREICH.VERSCHIEBEN($B$2;0;WENN(VERGLEICH(1E-300;$B$2:$M$2;-1)>5;VERGLEICH(1E-300;$B$2:$M$2;-1)-5;0); ;WENN(VERGLEICH(1E-300;$B$2:$M$2;-1)>5;4;VERGLEICH(1E-300;$B$2:$M$2;-1))))
B9=INDEX($B$2:$M$2;1;VERGLEICH(1E-300;$B$2:$M$2;-1))
C9=INDEX($B$2:$M$2;1;VERGLEICH(1E-300;$B$2:$M$2;-1))
B10=B9-B8
C10=C9-C8
B11=INDEX($B$2:$M$2;1;VERGLEICH(1E-300;$B$2:$M$2;-1))-MITTELWERT(BEREICH.VERSCHIEBEN($B$2;0;0;;VERGLEICH(1E-300;$B$2:$M$2;-1)-1))
C11=INDEX($B$2:$M$2;1;VERGLEICH(1E-300;$B$2:$M$2;-1))-MITTELWERT(BEREICH.VERSCHIEBEN($B$2;0;WENN(VERGLEICH(1E-300;$B$2:$M$2;-1)>5;VERGLEICH(1E-300;$B$2:$M$2;-1)-5;0); ;WENN(VERGLEICH(1E-300;$B$2:$M$2;-1)>5;4;VERGLEICH(1E-300;$B$2:$M$2;-1))))


PS: hier würde ich wahrscheinlich mit Hilfszellen arbeiten weil die Formel einfach zu unübersichtlich wird.

Gruß Tino


  

Betrifft: etwas kürzer von: Tino
Geschrieben am: 22.01.2010 12:55:28

Hallo,
habe die Formel noch etwas kürzer hinbekommen und noch eine vierte Variante mit Namen eingebaut.

 ABCDEFGHIJ
1BezeichnungQ1 08Q2 08Q3 08Q4 08Q1 09Q2 09Q3 09Q4 09Q1 10
2Gesamtergebnis1002003002501002502802000
3          
4          
5          
6          
7 Ab B2letzten 4mit Namen      
8Mittelwert211,428571220220      
9letzter200200200      
10Abweichung-11,4285714-20-20      
11oder eine Formel-11,4285714-20-20      

Formeln der Tabelle
ZelleFormel
B8=MITTELWERT(BEREICH.VERSCHIEBEN($B$2;0;0;;VERGLEICH(1E-300;$B$2:$M$2;-1)-1))
C8=MITTELWERT(BEREICH.VERSCHIEBEN($A$2;0;MAX(1;VERGLEICH(1E-300;$2:$2;-1)-5); ;MIN(4;VERGLEICH(1E-300;$2:$2;-1)-2)))
D8=MITTELWERT(Bereich)
B9=INDEX($B$2:$M$2;1;VERGLEICH(1E-300;$B$2:$M$2;-1))
C9=INDEX($B$2:$M$2;1;VERGLEICH(1E-300;$B$2:$M$2;-1))
D9=INDEX($B$2:$M$2;1;VERGLEICH(1E-300;$B$2:$M$2;-1))
B10=B9-B8
C10=C9-C8
D10=D9-D8
B11=INDEX($B$2:$M$2;1;VERGLEICH(1E-300;$B$2:$M$2;-1))-MITTELWERT(BEREICH.VERSCHIEBEN($B$2;0;0;;VERGLEICH(1E-300;$B$2:$M$2;-1)-1))
C11=INDEX($B$2:$M$2;1;VERGLEICH(1E-300;$B$2:$M$2;-1))-MITTELWERT(BEREICH.VERSCHIEBEN($A$2;0;MAX(1;VERGLEICH(1E-300;$2:$2;-1)-5); ;MIN(4;VERGLEICH(1E-300;$2:$2;-1)-2)))
D11=INDEX($B$2:$M$2;1;VERGLEICH(1E-300;$B$2:$M$2;-1))-MITTELWERT(Bereich)
Namen in Formeln
ZelleNameBezieht sich auf
B8Bereich=BEREICH.VERSCHIEBEN(Tabelle1!$A$2;0;MAX(1;VERGLEICH(1E-300;Tabelle1!$2:$2;-1)-5);;MIN(4;VERGLEICH(1E-300;Tabelle1!$2:$2;-1)-2))
C8Bereich=BEREICH.VERSCHIEBEN(Tabelle1!$A$2;0;MAX(1;VERGLEICH(1E-300;Tabelle1!$2:$2;-1)-5);;MIN(4;VERGLEICH(1E-300;Tabelle1!$2:$2;-1)-2))
D8Bereich=BEREICH.VERSCHIEBEN(Tabelle1!$A$2;0;MAX(1;VERGLEICH(1E-300;Tabelle1!$2:$2;-1)-5);;MIN(4;VERGLEICH(1E-300;Tabelle1!$2:$2;-1)-2))
B11Bereich=BEREICH.VERSCHIEBEN(Tabelle1!$A$2;0;MAX(1;VERGLEICH(1E-300;Tabelle1!$2:$2;-1)-5);;MIN(4;VERGLEICH(1E-300;Tabelle1!$2:$2;-1)-2))
C11Bereich=BEREICH.VERSCHIEBEN(Tabelle1!$A$2;0;MAX(1;VERGLEICH(1E-300;Tabelle1!$2:$2;-1)-5);;MIN(4;VERGLEICH(1E-300;Tabelle1!$2:$2;-1)-2))
D11Bereich=BEREICH.VERSCHIEBEN(Tabelle1!$A$2;0;MAX(1;VERGLEICH(1E-300;Tabelle1!$2:$2;-1)-5);;MIN(4;VERGLEICH(1E-300;Tabelle1!$2:$2;-1)-2))
Namen verstehen


Gruß Tino


  

Betrifft: AW: etwas kürzer von: Jens
Geschrieben am: 22.01.2010 13:52:15

das mit den Namen ist wegen der Übersichtlichkeit klasse. Es zeigt sich für mich nun folgendes Problem:

1. es kann durchaus vorkommen das in den letzten 4 Quartalen irgendwo eine Null (bzw. G2 wäre nicht 250 sondern Null) oder auch zwei Nullen hintereinander stehen. Dann werden alle hinter G2 stehenden Werte ignoriert, richtig? Somit würde sich der Durchschnitt aus B2:E2 ergeben, oder? Ich möchte aber den Durchschnitt der 4 Quartale E2:H2 erzwingen, auch unter Einbeziehung der Quartale mit Null.

Die Schwierigkeit ist vermutlich der Formel zu sagen, wo ist die Zelle wirklich leer und wo steht als Wert eine Null. Kannst Du meinem Gedankengang folgen?

Gruß Jens


  

Betrifft: AW: etwas kürzer von: Tino
Geschrieben am: 22.01.2010 14:29:00

Hallo,
müste so funktionieren.

 ABCDEFGHIJ
1BezeichnungQ1 08Q2 08Q3 08Q4 08Q1 09Q2 09Q3 09Q4 09Q1 10
2Gesamtergebnis10020030025010002802000
3          
4          
5          
6          
7 letzten 4mit Namen       
8Mittelwert157,5157,5       
9letzter200200       
10Abweichung42,542,5       
11oder eine Formel-149-149       

Formeln der Tabelle
ZelleFormel
B8=MITTELWERT(BEREICH.VERSCHIEBEN($A$2;0;MAX(1;VERWEIS(2;1/(2:2<>0); SPALTE(2:2))-5); ;MIN(4;VERWEIS(2;1/(2:2<>0); SPALTE(2:2))-2)))
C8=MITTELWERT(Bereich)
B9=VERWEIS(2;1/(2:2<>0); 2:2)
C9=VERWEIS(2;1/(2:2<>0); 2:2)
B10=B9-B8
C10=C9-C8
B11=VERWEIS(2;1/(2:2<>0); SPALTE(2:2))-MITTELWERT(BEREICH.VERSCHIEBEN($A$2;0;MAX(1;VERWEIS(2;1/(2:2<>0); SPALTE(2:2))-5); ;MIN(4;VERWEIS(2;1/(2:2<>0); SPALTE(2:2))-2)))
C11=VERWEIS(2;1/(2:2<>0); SPALTE(2:2))-MITTELWERT(Bereich)
Namen in Formeln
ZelleNameBezieht sich auf
B8Bereich=BEREICH.VERSCHIEBEN(Tabelle1!$A$2;0;MAX(1;VERWEIS(2;1/(Tabelle1!$2:$2<>0);SPALTE(Tabelle1!$2:$2))-5);;MIN(4;VERWEIS(2;1/(Tabelle1!$2:$2<>0);SPALTE(Tabelle1!$2:$2))-2))
C8Bereich=BEREICH.VERSCHIEBEN(Tabelle1!$A$2;0;MAX(1;VERWEIS(2;1/(Tabelle1!$2:$2<>0);SPALTE(Tabelle1!$2:$2))-5);;MIN(4;VERWEIS(2;1/(Tabelle1!$2:$2<>0);SPALTE(Tabelle1!$2:$2))-2))
B11Bereich=BEREICH.VERSCHIEBEN(Tabelle1!$A$2;0;MAX(1;VERWEIS(2;1/(Tabelle1!$2:$2<>0);SPALTE(Tabelle1!$2:$2))-5);;MIN(4;VERWEIS(2;1/(Tabelle1!$2:$2<>0);SPALTE(Tabelle1!$2:$2))-2))
C11Bereich=BEREICH.VERSCHIEBEN(Tabelle1!$A$2;0;MAX(1;VERWEIS(2;1/(Tabelle1!$2:$2<>0);SPALTE(Tabelle1!$2:$2))-5);;MIN(4;VERWEIS(2;1/(Tabelle1!$2:$2<>0);SPALTE(Tabelle1!$2:$2))-2))
Namen verstehen


Gruß Tino


  

Betrifft: Fehler beim Coder erstellen. (Beispieldatei) von: Tino
Geschrieben am: 22.01.2010 17:37:39

Hallo,
hier meine Versuchsdatei, da ist es richtig.

https://www.herber.de/bbs/user/67425.xls

Gruß Tino


  

Betrifft: AW: Minuend und Subtrahend verdauscht von: Tino
Geschrieben am: 22.01.2010 11:44:54

Hallo,
hier ein Beispiel damit kann man es verstehen.

1E-5 =1/100000 =0,00001
1E-300 =1 / 1 mit 300 Nullen also eine sehr kleine Zahl die aber dennoch größer ist als 0

In Excel ist dies glaube ich die Wissenschaftlich- oder Exponenten- Schreibweise.

Gruß Tino


  

Betrifft: Nicht nur in Excel...! ;-) Gruß owT von: Luc:-?
Geschrieben am: 22.01.2010 16:48:25

:-?


Beiträge aus den Excel-Beispielen zum Thema "Abweichung eines Wertes vom Durchschnitt"