Excel 2003: Mittelwert ohne 0 errechnen

[Hakaori]

Average

Hi Jungs,

hat sich erledigt.

Danke und lieben Gruß

 

[Madman1209]

Hi,

meinst du sowas?

VG,
Mad

 

[Hakaori]

Ja genau das hatte ich schon gefunden, aber das ist doch nicht anwendbar auf meine Funktion oder?

 

[Cave Johnson]

Ohne es gestestet zu haben, pack doch noch eine Bedingung mit rein:

($D$19:$O$19<>0)

 

[Hakaori]

Aber dann funktioniert meine Formel nicht mehr, dass der Bereich zur Mittelwert Berechnung dynamisch ist.
B8 definiert das Jahr.

 

[myoizo]

=wenn(($d$19:$o$19>=$b$8)*($d$19:$o$19<=$b$9);summe(d27:o27)/zählenwenn(d27:o27;">0"))

 

[Hakaori]

Danke Myoi

 

[Cave Johnson]

Evtl.

=SUMMENPRODUKT(($D$19:$O$19>=$B$8)*($D$19:$O$19<=$B$9)*D27:O27)/ZÄHLENWENN(D27:O27;"<>0")

 

[Hakaori]

Nein

 

[Cave Johnson]

Jetzt aber:

=SUMMENPRODUKT(($D$19:$O$19>=$B$8)*($D$19:$O$19<=$B$9)*D27:O27)/SUMMENPRODUKT(($D$19:$O$19>=$B$8)*($D$19:$O$19<=$B$9)*(D27:O27<>0))

Hatte nicht beachtet, dass Zählenwenn die anderen beiden Bedingungen nicht berücksichtigt.

 

[Hakaori]

Mal sehen ob ich das berücksichtigen kann

 

[cumulonimbus8]

Kann das sein, dass ich da völlig auf dem falschen Dampfer bin?

Als Beispiel stehe in A1 bis A10 die Sequenz 10,11,12,0,13,0,14,15,0,16. Das sind zufällig 10 Zellen, und der Mittelwert ohne die Nullen ist 13, wie man leicht sieht.

Also hätten wir:
=ZÄHLENWENN(A1:A10;0) → 3
=ANZAHL(A1:A10) → 10
=SUMME(A1:A10) → 91

Und die simple Formel, die dann mit WENN aktiviert werden müsste, sähe nach meiner Meinung so aus:
=SUMME(A1:A10) / ( ANZAHL(A1:A10) - ZÄHLENWENN(A1:A10;0) )
…was nach Adam Riese ungefähr 13 ergibt. Der Bereich A1:A10 ist latürnich flexibel.

Persönlich übrigens habe ich einen Wert Null gegenüber ‹Nichts› fein zu unterscheiden gelernt. Wenn 0 zurückkommt ist es 0, wenn dort Nichts zu strhen hätte, warum steht dort nicht auch Nichts?!

CN8

 

[Cave Johnson]

Was den "Mittelwert ohne 0" angeht wäre das korrekt, aber wie bringst du dann obige Bedingungen mit ins Spiel?

Da es sich um Bereichsangaben handelt, kommst du mit einem einfach Wenn nicht weiter - höchstens mit einer Matrixformel, dann aber wiederum nicht in Verbindung mit deinem Ansatz.

 

[Mirakel]

Falls deine Formel "=MITTELWERT(WENN(($D$19:$O$19>=$B$8)*($D$19:$O$19<=$B$9);D27:O27))" soweit funktioniert, warum nimmst du nicht einfach den Hinweis aus der ersten Antwort und machst folgendes daraus?

=WENN(($D$19:$O$19>=$B$8)*($D$19:$O$19<=$B$9);SUMME(D27:O27)/ZÄHLENWENN(D27:O27;">0"))

Falls nötig, auch mit Dollarzeichen.

Sollte an deiner Formel etwas nicht stimmen, können wir diese gerne optimieren.
Könntest du dafür mal ein Beispiel bereitstellen, wie die Daten vorliegen?

 

[Cave Johnson]

Die Formel liefert aber ein falsches Ergebnis, da dein Divisor ZÄHLENWENN(D27:O27;">0") die Wenn-Bedingung nicht berücksichtigt. Er kann zufällig stimmen, kann aber auch zu groß sein.

Du summierst korrekt nur dort D27:O27, wo ($D$19:$O$19>=$B$8)*($D$19:$O$19<=$B$9) wahr ist. Du dividierst aber durch die Summe von (D27:O27)>0, egal ob die obige Bedingung zutrifft. Das können mehr Summanden als im Divident sein - somit ist das nicht der Mittelwert.

 

[cawti]

Grundidee:
Die Daten als Tabelle formatieren. Damit werden sie automatisch ein dynamischer Bereich. Ergebnisszeile einblenden. Die 0 Werte rausfiltern und dann den Mittelwert bilden.
-> Dynamischer Bereich mit einem Mittelwert der Nullwerte ignoriert.

 

[Mirakel]

Wenn ich nochmal so darüber nachdenke, bietet sich eine Pivot-Tabelle an. Aber das will ich mir in Excel2003 nicht antuen

 

[Hakaori]

Es sind gigantische Datenmengen und Tabellen

 

[Cave Johnson]

In der ersten Formel erhälst du als Rückgabewert nur eine einzelne Zelle - da ist mit Mittelwert genau so groß

Bei der zweiten kannst du erst die Summe berechnen und dann durch die Anzahl nicht 0-Werte dividieren:
=SUMME(INDEX(I$20:I$85;$B$5+4):INDEX(I$20:I$85;$B$6+4))/ZÄHLENWENN(INDEX(I$20:I$85;$B$5+4):INDEX(I$20:I$85;$B$6+4);"<>0")

 

[Hakaori]

Danke für den Tip ;-)