Mittelwert ohne null

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
MsgBox


Excel-Version: XL8
nach unten

Betrifft: Mittelwert ohne null
von: Dietmar
Geschrieben am: 24.04.2002 - 16:45:03

Hi Leute,

kann mit jemand eine Formel verraten, mit der ich Mittelwerte ohne Einbeziehen der 0 erstellen kann.

1
2
0
3

Mittelwert: 2 (Summe / Anzahl ohne 0)

Das Problem dabei: die Werte stehen nicht untereinander, sondern sind wild verteilt.

Wäre super, wenn ihr mir einen Tipp hättet.

Dietmar

nach oben   nach unten

Re: Mittelwert ohne null
von: Any Body
Geschrieben am: 24.04.2002 - 17:14:21

Summe der Werte: =SUMMEWENN(A1:A12;">0") Anzahl der Werte >0: =ZÄHLENWENN(A1:A12;">0") und nun noch Teilen durcheinander hth Any

nach oben   nach unten

Re: Mittelwert ohne null
von: Dietmar
Geschrieben am: 24.04.2002 - 17:20:09

Hi, Das Problem dabei ist, dass bei Zählenwenn nur ein Bereich zulässig ist. Was kann ich machen, wenn die Werte verstreut sind. Z.B: A1;A100;A5

nach oben   nach unten

Re: Mittelwert ohne null
von: Martin Beck
Geschrieben am: 24.04.2002 - 17:20:38

Hallo Dietmar, gebe dem Bereich, in dem die verstreuten Daten stehen einen Namen (z.B. mw), und verwende folgende benutzedefinierte Funktion:
Function Mittelwert_ohne_Null(Bereich As Range) x = 0 y = 0 For Each cell In Bereich     If Not IsNumeric(cell) Then         Mittelwert_ohne_Null = "#WERT!"         Exit Function     Else         x = x + cell         If cell <> 0 Then             y = y + 1         End If End If Next Mittelwert_ohne_Null = x / y End Function
Aufruf: =Mittelwert_ohne_Null(mw) Gruß Martin Beck

nach oben   nach unten

Re: Mittelwert ohne null
von: Dietmar
Geschrieben am: 24.04.2002 - 17:24:25

Hi Martin, das Problem dabei ist, dass die Werte auf verschiedenen Blättern stehen können. Gruss

nach oben   nach unten

Re: Mittelwert ohne null
von: Heinz A. Wankmüller
Geschrieben am: 24.04.2002 - 20:57:02

Hallo Dietmar, dafür gibt es die Funktion MITTELWERTA(Wert1;...) Ic weiss allerdings nicht, ob sie schon in xl8 vorhanden ist, da ich nur xl2002 zur Verfügung habe. Gruß Heinz

nach oben   nach unten

Re: Mittelwert ohne null
von: Martin Beck
Geschrieben am: 25.04.2002 - 09:12:41

Hallo Heinz, bei MITTELWERTA (in XL97 vorhanden) werden allerdings die Nullen einbezogen, und das möchte Dietmar ja gerade vermeiden. Gruß Martin Beck

nach oben   nach unten

Re: Lösung
von: Dietmar
Geschrieben am: 25.04.2002 - 09:45:40

Ich glaube, ich habe selbst eine Lösung gefunden: Ich mache jetzt zwei Felder: Summe und Anzahl -> Mittelwert = Summe / Anzahl Bei Anzahl prüfe ich jetzt jeden Wert separat auf eine 0! =Anzahl(wenn(A1<>0;A1;null)) Schaut zwar nicht schön aus, funktioniert aber.

nach oben   nach unten

Re: Mittelwert ohne null
von: Martin Beck
Geschrieben am: 25.04.2002 - 09:46:01

Hallo Dietmar, dazu fällt mir keine Formellösung mehr ein. Versuche mal folgendes: 1. Markiere in den Tabellenblättern jeweils den in die Berechnung eingehenden Bereich und vergib einen Namen, der wie folgt aufgebaut sein muß Im ersten Tabellenblatt (Blattindex in VBA = 1): Ber1 Im zweitenTabellenblatt (Blattindex in VBA = 2): Ber2 usw. In Tabellenblättern ohne einzubeziehenden Zellen vergib einen Namen auf eine leere Zelle. 2. Wende folgendes Makro an:
Sub mittelwert() For Each ws In Worksheets     ws.Activate     Range("Ber" & ws.Index).Select     For Each cell In Selection         x = x + cell         If Not IsEmpty(cell) And cell <> 0 Then             y = y + 1         End If     Next Next MsgBox x 'Summe MsgBox y 'Anzahl Zelle ohne LEER und NULL MsgBox x / y End Sub
Gruß Martin Beck

nach oben   nach unten

Re: Lösung
von: Arnim
Geschrieben am: 25.04.2002 - 11:04:54

Hallo Dietmar, hier ein paar Lösungsmöglichkeiten mit Array-Formeln (Eingabe jeweils mit Tasten "Strg"+Shift(Umschalt)" und dann "Enter"): =MITTELWERT(WENN(A1:A20;A1:A20)) etwas sicherer: =MITTELWERT(WENN(A1:A20<>0;A1:A20)) =SUMME(A1:A20)/ANZAHL(WENN(A1:A20<>0;A1:A20)) Genügt es?? Gruß Arnim

 nach oben

Beiträge aus den Excel-Beispielen zum Thema "Mittelwert ohne null"