Mittelwert nur von Funktionen

Bild

Betrifft: Mittelwert nur von Funktionen
von: stormlamp
Geschrieben am: 24.08.2015 11:55:09

Hallo Zusammen,
ich möchte in einer Spalte (bspw. B5:B1000)einen Mittelwert berechnen jedoch nur der Zellen, die eine Funktion enthalten und bei denen das Funktionsergebnis ungleich 0 ist.
Zellen der Spalte, die Zahlen oder Texte statt Funktionen beinhalten sollen unberücksichtigt bleiben.
Geht das?
Viele Grüße
Hans

Bild

Betrifft: AW: Mittelwert nur von Funktionen
von: Daniel
Geschrieben am: 24.08.2015 12:03:52
Hi
um die 0-Werte auszuschließen, kannst du MittelwertWenn verwenden: =MittelwertWenn(B5:B1000;"<>0")
eine Differenzierung nach Formel und Wert ist in deiner Excelversion meines Wissens nach noch nicht möglich.
da müsste man dann mit VBA was programmieren, oder ggf kannst du mit MittelwertWennS ein weiteres Ausschlusskriterium aus einer anderen Spalte hinzunehmen.
gruß Daniel

Bild

Betrifft: AW: Mittelwert nur von Funktionen
von: stormlamp
Geschrieben am: 24.08.2015 12:09:48
Hallo Daniel,
kann ich eventuell eine Zelle, die eine Funktion enthält, über eine bedingte Formatierung farblich kennzeichnen und dann die Farbe als weiteres Kriterium für die Mittelwertbildung heranziehen?
Viele Grüße
Hans

Bild

Betrifft: Mittelwert nur von Funktionen - klar geht das.
von: WF
Geschrieben am: 24.08.2015 12:21:49
Hi,
mal als Arrayformel:
{=MITTELWERT(WENN(B5:B1000<>0;WENN(ISTFORMEL(B5:B1000);B5:B1000)))}
Kannst Du ja noch zu SUMMEWENNS umfummeln.
Salut WF

Bild

Betrifft: AW: Mittelwert nur von Funktionen - klar geht das.
von: Daniel
Geschrieben am: 24.08.2015 12:24:04
Hi WF
Excel 2010 kennt die Funktion "IstFormel" aber noch nicht.
wurde dies nicht erst mit der folgenden Version eingefügt?
Gruß Daniel

Bild

Betrifft: AW: Mittelwert nur von Funktionen - klar geht das.
von: SF
Geschrieben am: 24.08.2015 12:24:57
Hola,
ISTFORMEL() gibts mW 2010 aber noch nicht.
Gruß,
steve1da

Bild

Betrifft: aah, ja - das stimmt
von: WF
Geschrieben am: 24.08.2015 12:27:04
.

Bild

Betrifft: AW: ISTFORMEL() gibt es mE erst ab XL 2013 owT
von: ... neopa C
Geschrieben am: 24.08.2015 12:25:47
Gruß Werner
.. , - ...

Bild

Betrifft: AW: Mittelwert nur von Funktionen
von: stormlamp
Geschrieben am: 24.08.2015 12:18:20
Hallo Daniel,
ich habe von "Alois Eckl" folgende Möglichkeit gefunden:

1. Einfügen --> Name --> Definieren....
2. Unter 'Namen in der Arbeitsmappe' geben Sie den gewünschten Namen ein, z. B. formel
3. Unter bezieht sich auf erfassen Sie folgende Funktion:
    =ZELLE.ZUORDNEN(48;!B1)+0*JETZT()
4. Mit der Funktion =formel bekommen Sie nun eine 0 als Ergebnis, wenn sich
    keine Formeln in B1 befindet und eine 1 wenn sich eine Formeln in B1 befindet.
5. Mit einer einfachen Wenn-Abfrage können Sie diese 1 bzw. 0 noch in verständlichen
    Text umwandeln: Die Funktion dazu lautet:  =WENN(formel=0;"W";"F")

<
Ließe sich das über den Namen dann als zweite Bedingung in die MittelwertWenn-Formel einbauen?
Gruß
Hans

Bild

Betrifft: AW: Mittelwert nur von Funktionen
von: Daniel
Geschrieben am: 24.08.2015 12:31:46
Hi
wenn du die Kennzeichung ob Formel oder nicht in einer zweiten Spalte machst (z.B. in Spalte C) dann kannst du das mit MittelwertWenns ergänzen.
Ich gehe hier mal von der Kennzeichung mit "W" und "F" aus:

=MittelwertWenns(B5:B1000;B5:B5000;"<>0";C5:C5000;"F")
Gruß Daniel

Bild

Betrifft: AW: Bereichsdefintionen müssen gleich sein ...
von: ... neopa C
Geschrieben am: 24.08.2015 12:39:32
Hallo Daniel,
... die 1000 ist bei Dir sicherlich ein Schreibfehler.
Aber die Wandlung der Hilfszellenergebnisse 1 in "F" bedarf es auch nicht. Wenn die Abfrage auf Formel in der "(Hilfs-)Spalte H vorgenommen wird, dann z.B so:
=MITTELWERTWENNS(B5:B999;B5:B999;"<>0";H5:H999;1)
Gruß Werner
.. , - ...

Bild

Betrifft: AW: Mittelwert nur von Funktionen
von: stormlamp
Geschrieben am: 24.08.2015 12:54:42
Hallo,
danke, so kann ich die Formel gut verwenden:

=MITTELWERTWENNS(E4:E10000;E4:E10000;"<>0";$A4:$A10000;">1")
da nur bei den Funktionen in Spalte A ein Datum steht
Freundliche Grüße
Hans

Bild

Betrifft: AW: Mittelwert nur von Funktionen
von: stormlamp
Geschrieben am: 24.08.2015 13:12:05
Hallo,
es lässt sich in der bedingten Formatierung eine Zelle einfärben, sobald sie eine Funktion enthält:
im Namensmanager:

formel=ZELLE.ZUORDNEN(48;!E4)+0*JETZT()
in der bedingten Formatierung von E4:
=formel 
(dann Farbe)
Diese bedingte Formatierung lässt sich nun auf alle anderen Zellen kopieren.
Jetzt ist mir nur noch nicht klar, ob ich in der MittelwertWenn-Formel abfragen kann, ob die Zelle eingefärbt ist oder nicht.
Gruß
Hans

Bild

Betrifft: AW: nicht mit bedingter Formatierung ...
von: ... neopa C
Geschrieben am: 24.08.2015 13:43:59
Hallo Hans,
... der eingestellte Formel-Lösungsansatz beruht auf der Ermittlung des Vorhandenseins von Formeln in der auszuwertenden Spalte in einer (ausblendbaren) Hilfsspalte. Alles andere wäre ohne VBA zu kompliziert. Auf Basis der Hilfsspaltenergebnisse könntest Du jedoch bei Bedarf natürlich die Formelzellen in der auszuwertenden Spalte zusätzlich noch mittels bedingter Formatierung einfärben.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: Mittelwert nur von Funktionen
von: Gerd L
Geschrieben am: 24.08.2015 12:34:40
Hallo Hans,
kopiere diese Funktion in ein Allgemeines Modul; Matrixformeln sind nicht abgehandelt.

Public Function Stormlamp(Bereich As Range) As Double
Dim objZelle As Range
Dim dblSumme As Double
Dim lngAnzahl As Long
For Each objZelle In Bereich
    If objZelle.HasFormula Then
    If objZelle.Value <> 0 Then
    If IsNumeric(objZelle.Text) Then
        dblSumme = dblSumme + objZelle.Value
        lngAnzahl = lngAnzahl + 1
    End If
    End If
    End If
Next
If lngAnzahl > 0 Then Stormlamp = dblSumme / lngAnzahl
End Function
Gruß Gerd

Bild

Betrifft: AW: Mittelwert nur von Funktionen
von: stormlamp
Geschrieben am: 24.08.2015 12:57:08
Hallo Gerd,
danke für das Script, nur wenn es möglich ist, bleibe ich liebr bei Funktionen als mich in VbA hineinzubegeben.
Viele Grüße
Hans

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Mittelwert nur von Funktionen"