Herbers Excel/VBA-Archiv

Abweichung eines Wertes vom Durchschnitt | Herbers Excel-Forum

• Abweichung eines Wertes vom Durchschnitt von Jens vom 22.01.2010 08:55:46
• AW: Abweichung eines Wertes vom Durchschnitt - von Hajo_Zi am 22.01.2010 09:04:33
  • AW: Abweichung eines Wertes vom Durchschnitt - von welga am 22.01.2010 09:22:20
• AW: Abweichung eines Wertes vom Durchschnitt - von Tino am 22.01.2010 09:16:54
  • AW: Abweichung eines Wertes vom Durchschnitt - von Hajo_Zi am 22.01.2010 09:25:10
    • @Hajo ... - von Tino am 22.01.2010 09:42:09
  • Minuend und Subtrahend verdauscht - von Tino am 22.01.2010 09:52:46
    • AW: Minuend und Subtrahend verdauscht - von Jens am 22.01.2010 11:22:01
      • AW: Minuend und Subtrahend verdauscht - von Jens am 22.01.2010 11:38:09
        • müsste so funktionieren - von Tino am 22.01.2010 12:17:07
          • etwas kürzer - von Tino am 22.01.2010 12:55:28
            • AW: etwas kürzer - von Jens am 22.01.2010 13:52:15
              • AW: etwas kürzer - von Tino am 22.01.2010 14:29:00
                • Fehler beim Coder erstellen. (Beispieldatei) - von Tino am 22.01.2010 17:37:39
      • AW: Minuend und Subtrahend verdauscht - von Tino am 22.01.2010 11:44:54
        • Nicht nur in Excel...! ;-) Gruß owT - von Luc:-? am 22.01.2010 16:48:25

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

	A	B
1	Bezeichnung	Q1 08
2	Gesamtergebnis	100
3	Mittelwert	140
4	letzter	0
5	Abweichung	140

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

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.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

Bezeichnung

Q1 08

Q2 08

Q3 08

Q4 08

Q1 09

Q2 09

Q3 09

Q4 09

Q1 10

Q2 10

Q3 10

Q4 10

2

Gesamtergebnis

100

200

300

250

100

250

280

200

0

0

0

0

11

Formeln der Tabelle

Zelle Formel 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

	A	B
1	Bezeichnung	Q1 08
2	Gesamtergebnis	100
3	Mittelwert	210
4	letzter	200
5	Abweichung	10

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.

	A	B
5	Mittelwert	211,428571
6	letzter	200
7	Abweichung	11,4285714

Formeln der Tabelle

Zelle Formel 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.

	A	B
5	Mittelwert	211,428571
6	letzter	200
7	Abweichung	-11,4285714
8	oder eine Formel	-11,4285714

Formeln der Tabelle

Zelle Formel 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?

	A	B	C	D	E	F	G	H	I	J
1	Bezeichnung	Q1 08	Q2 08	Q3 08	Q4 08	Q1 09	Q2 09	Q3 09	Q4 09	Q1 10
2	Gesamtergebnis	100	200	300	250	100	250	280	200	0
3
4
5
6
7		Ab B2	letzten 4
8	Mittelwert	211,428571	220
9	letzter	200	200
10	Abweichung	-11,4285714	-20
11	oder eine Formel	-11,4285714	-20

Formeln der Tabelle

Zelle Formel 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.

	A	B	C	D	E	F	G	H	I	J
1	Bezeichnung	Q1 08	Q2 08	Q3 08	Q4 08	Q1 09	Q2 09	Q3 09	Q4 09	Q1 10
2	Gesamtergebnis	100	200	300	250	100	250	280	200	0
3
4
5
6
7		Ab B2	letzten 4	mit Namen
8	Mittelwert	211,428571	220	220
9	letzter	200	200	200
10	Abweichung	-11,4285714	-20	-20
11	oder eine Formel	-11,4285714	-20	-20

Formeln der Tabelle

Zelle Formel 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 Zelle Name Bezieht sich auf B8 Bereich =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)) C8 Bereich =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)) D8 Bereich =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)) B11 Bereich =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)) C11 Bereich =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)) D11 Bereich =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.

	A	B	C	D	E	F	G	H	I	J
1	Bezeichnung	Q1 08	Q2 08	Q3 08	Q4 08	Q1 09	Q2 09	Q3 09	Q4 09	Q1 10
2	Gesamtergebnis	100	200	300	250	100	0	280	200	0
3
4
5
6
7		letzten 4	mit Namen
8	Mittelwert	157,5	157,5
9	letzter	200	200
10	Abweichung	42,5	42,5
11	oder eine Formel	-149	-149

Formeln der Tabelle

Zelle Formel 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 Zelle Name Bezieht sich auf B8 Bereich =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)) C8 Bereich =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)) B11 Bereich =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)) C11 Bereich =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"