Mittelwert und STDEV berechnen aus Histogramm

[NameZero912]

Hi,

ich möchte gern AVG und STDEV berechnen. Die Daten liegen aber leider nicht einfach als einfache Reihe vor, sondern als Histogramm, also z.B. bei einer Messung bei der 5 verschiedene Werte auftreten können habe ich in der Datei 5 Zeilen, z.B.

1 | 4
2 | 2
3 | 1
4 | 4
5 | 3

das entspräche der Reihe 1 1 1 1 2 2 3 4 4 4 4 5 5 5.

Wie kann ich möglichst einfach Mittelwert und Standardabweichung aus den Ausgangsdaten berechnen?

Gruß!
Peter

[Fennek]

Hallo,
Angenommen die Werte stehen in Spalte a und b:

1. Schritt in c1: =wiederholen(zeile();b1)
2. Schritt

=summenprodukt(a1:a5,b1:b5)/summe(b1:b5)

Mfg

[Fennek]

Sry,

Schritt 1war ein Versuch und ist bei der Lösung nicht mehr notwendig

[radagast]

heje excelfreunde,

gibt es einen math. zusammenhang zwischen den zahlen(messreihe)???
ganz weit hergeholt: gauß = fehlertheorie, fehlerstatistik etc.

[shift-del]

Hallo Peter

Mittelwert:

Code:

=SUMMENPRODUKT(Häufigkeiten*Klassen)/SUMME(Häufigkeiten)

Standardabweichung:

Code:

=WURZEL(SUMME(((Klassen-Mittelwert)^2)*Häufigkeiten)/SUMME(Häufigkeiten))

Und als Bonus.
Modalwert

Code:

=INDEX(Klassen;VERGLEICH(MAX(Häufigkeiten);Häufigkeiten;0))

Letzter Modalwert

Code:

=VERWEIS(MAX(Häufigkeiten);Klassen)

Alle Modalwerte

Code:

=AGGREGAT(15;6;Klassen/(Häufigkeiten=MAX(Häufigkeiten));n)

n durch 1, 2, 3, etc. ersetzen.
Anzahl Modalwerte

Code:

=ZÄHLENWENN(Häufigkeiten;MAX(Häufigkeiten))

Median

Code:

=MEDIAN(WENN(SPALTE(1:1)<=Häufigkeiten;Klassen))

Varianz

Code:

=Standardabweichung^2

[NameZero912]

Hi. Danke Fennek, Durchschnittberechnung klappt schonmal.

@shift-del: deine Standardabweichungsformel funktioniert offensichtlich nicht. Es kommt "#WERT" zurück. Ich kann Excel da auch zustimmen. Du versuchst bei "Klassen-Mittelwert" einen Wert von einem Bereich abzuziehen. Wie soll das gehen?

Für mich zu Klarstellung:
Klassen = A1:A5
Häufigkeiten = B1:B5

Dadurch ist der Mittelwert (wie von Fennek angegeben)

Code:

=SUMMENPRODUKT(A1:A5;B1:B5)/SUMME(B1:B5)

Und deine (mathematisch zwar korrekte, aber für Excel inkorrekte) Formel für die Standardabweichung (wenn wir annehmen dass ich den Mittelwert in H10 gespeichert habe):

Code:

=WURZEL(SUMME(((A1:A5-H10)^2)*B1:B5)/SUMME(B1:B5))

Ich glaube nicht dass Excel das mit Formeln überhaupt kann (falls es jemand besser weiß, dann bitte die konkrete Formel mit A1:A5 und B1:B5, ohne Abstrahierungen). Man kann es natürlich mittels VB Script hinbekommen. Das werde ich mal versuchen. Sonst müsste ich halt die Daten exportieren und ein richtiges Statistikprogramm verwenden (R).

Gruß!

[Fennek]

Hallo,

Eine mögliche, wenn auch nicht unbedingt schöne Lösung könnte sein:
1. Das Histogramm "aufzubrösseln" in c1: =wiederholen(a1;b1)
2. Mit einem kleinen Makro die Original-Daten wiederstellen, sollte bis zu 256 Werten funktionieren, ansonsten unterteilen.
sub myHist()
Arr = range("c1:c5")
Arr = application.transpose(arr)
MyH = join(arr)
MyH = "#" & replace(myH, " ","')
Cells(10,1) = myH
For i = 2 to len(myH
Cells(12+i,1) = mid(myH, i,1)
Next
End sub 'Danke an snb

Kleinere Anpassungen sind sicher noch nötig. Danach können dann alle Formeln angewendet werden.

Mfg

[shift-del]

@shift-del: deine Standardabweichungsformel funktioniert offensichtlich nicht.

Sie funktioniert. Ich habe nur die {} vergessen. Die Formeln müssen mit CTRL-SHIFT-ENTER eingegeben werden. Das gilt auch für die Median-Formel.

[NameZero912]

@shift-del: deine Standardabweichungsformel funktioniert offensichtlich nicht.

Sie funktioniert. Ich habe nur die {} vergessen. Die Formeln müssen mit CTRL-SHIFT-ENTER eingegeben werden. Das gilt auch für die Median-Formel.

Hi. Funktioniert tatsächlich. Warum auch immer. Vielen Dank! 

Hier die konkrete Formel für meinen Fall (für alle anderen: STRG+Shift+Enter Eingabe führt dazu, dass automatisch { und } am Anfang/Ende eingefügt werden):

Code:

=WURZEL(SUMME(((A1:A5-H10)^2)*B1:B5)/SUMME(B1:B5))

[NameZero912]

Danke auch an die anderen Postings, es scheint als dass kein VB-Script nötig ist