Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 10:39:36
Schaltfläche auf einem Tabellenblatt NICHT mit abspeichern
19.04.2024 09:26:26
Median für Zeilen mit gleichen Zeitstempel
19.04.2024 08:41:23
Zwei Makros in ein Arbeitsblatt - zweite wird nicht ausführt
18.04.2024 21:09:11
4 Spalten aus smarter Tabelle in andere smarte Tab. kopieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1444to1448
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1444to1448
1444to1448
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Mittelwert nur von Funktionen

Mittelwert nur von Funktionen
24.08.2015 11:55:09
Funktionen
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

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Mittelwert nur von Funktionen
24.08.2015 12:03:52
Funktionen
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

AW: Mittelwert nur von Funktionen
24.08.2015 12:09:48
Funktionen
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

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

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

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

aah, ja - das stimmt
24.08.2015 12:27:04
WF
.

AW: ISTFORMEL() gibt es mE erst ab XL 2013 owT
24.08.2015 12:25:47
...
Gruß Werner
.. , - ...

AW: Mittelwert nur von Funktionen
24.08.2015 12:18:20
Funktionen
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

Anzeige
AW: Mittelwert nur von Funktionen
24.08.2015 12:31:46
Funktionen
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

AW: Bereichsdefintionen müssen gleich sein ...
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
.. , - ...

Anzeige
AW: Mittelwert nur von Funktionen
24.08.2015 12:54:42
Funktionen
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

AW: Mittelwert nur von Funktionen
24.08.2015 13:12:05
Funktionen
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

Anzeige
AW: nicht mit bedingter Formatierung ...
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
.. , - ...

AW: Mittelwert nur von Funktionen
24.08.2015 12:34:40
Funktionen
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

Anzeige
AW: Mittelwert nur von Funktionen
24.08.2015 12:57:08
Funktionen
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

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige