Mittelwert mit Bedingung und ohne "0" rechnen

[Dietmar65]

Servus, 
ich komm' nicht weiter und wende mich daher an euch. 
Ich möchte einen Mittelwert errechnen, der nicht die Nullen mit einrechnet und zu den angegebenen Bedingungen passt (s. F1+F2). Mittelwert dann in G1 bzw. G2.
Die Tabelle ist stark vereinfacht und hat auch mehrere leere Zeilen. 
Ich bedanke mich recht herzlich für eure Mithilfe. 
Didi

[shift-del]

Hallo

Du suchst diese schlanke Formel:

Code:

=(SUMMEWENNS($A$2:$A$12;$A$2:$A$12;">0";$D$2:$D$12;F1)+SUMMEWENNS($B$2:$B$12;$B$2:$B$12;">0";$D$2:$D$12;F1)+SUMMEWENNS($C$2:$C$12;$C$2:$C$12;">0";$D$2:$D$12;F1))/(ZÄHLENWENNS($A$2:$A$12;">0";$D$2:$D$12;F1)+ZÄHLENWENNS($B$2:$B$12;">0";$D$2:$D$12;F1)+ZÄHLENWENNS($C$2:$C$12;">0";$D$2:$D$12;F1))

[steve1da]

Hola,

so?

Code:

=SUMMENPRODUKT((D2:D12=F1)*(A2:C12))/ZÄHLENWENN(D2:D12;F1)

Gruß,
steve1da

[BoskoBiati]

Hi,

dann doch wohl eher so:

Code:

=SUMMENPRODUKT(($A$2:$C$12>0)*($D$2:$D$12=F1)*$A$2:$C$12)/SUMMENPRODUKT(($A$2:$C$12>0)*($D$2:$D$12=F1))

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I
1	Wert1	Wert2	Wert3	Bedingung		Ort AA	3	3	3
2	0	0	0	Ort AA		Ort BB	3	3	6
3
4	1	0	4	Ort BB
5	1	5	0	Ort BB
6	1	5	0	Ort AA
7
8	0	0	0	Ort AA
9	2	4	0	Ort BB
10	1	5	0	Ort BB
11	1	5	0	Ort AA
12	1	6	0	Ort BB

Zelle	Formel
G1	=(SUMMEWENNS($A$2:$A$12;$A$2:$A$12;">0";$D$2:$D$12;F1)+SUMMEWENNS($B$2:$B$12;$B$2:$B$12;">0";$D$2:$D$12;F1)+SUMMEWENNS($C$2:$C$12;$C$2:$C$12;">0";$D$2:$D$12;F1))/(ZÄHLENWENNS($A$2:$A$12;">0";$D$2:$D$12;F1)+ZÄHLENWENNS($B$2:$B$12;">0";$D$2:$D$12;F1)+ZÄHLENWENNS($C$2:$C$12;">0";$D$2:$D$12;F1))
H1	=SUMMENPRODUKT(($A$2:$C$12>0)*($D$2:$D$12=F1)*$A$2:$C$12)/SUMMENPRODUKT(($A$2:$C$12>0)*($D$2:$D$12=F1))
I1	=SUMMENPRODUKT(($D$2:$D$12=F1)*($A$2:$C$12))/ZÄHLENWENN($D$2:$D$12;F1)

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[Dietmar65]

Servus,
vielen Dank für die Rückmeldungen. Ich habe mich an dem Weg von "steve1da" versucht und es funktioniert auch bei dem ersten Monat, jedoch wenn ich den Bereich vergrößere rechnet er leider nicht mehr.

OK:
SUMMENPRODUKT(([AAA.xlsx]XXX!$AM$2:$AM$545=B6)*([AAA.xlsx]XXX!$J$2:$L$545))/ZÄHLENWENN([AAA.xlsx]XXX!$AM$2:$AM$545;B6)

funktioniert nicht, Fehlermeldung "#BEZUG":
SUMMENPRODUKT(([AAA.xlsx]XXX!$AM$2:$AM$2000=B6)*([AAA.xlsx]XXX!$J$2:$L$2000))/ZÄHLENWENN([AAA.xlsx]XXX!$AM$2:$AM$2000;B6)


Wo liegt hier der Fehler? Die Tabelle umfasst ca. 8000 Zeilen, welche in monatlichen Blöcken unterteilt sind. Jedoch sind alle relevanten Werte immer in der gleichen Spalte und es gibt keinen anderen Informationen als eine Zahl oder eine leere Zelle.


Bei dem Vorschlag von "BoskoBiati" kommt immer #DIV/0

=SUMMENPRODUKT(([AAA.xlsx]XXX!$J$2:$L$545>0)*([AAA.xlsx]XXX!$AM$2:$AM$545=B6)*[AAA.xlsx]XXX!$J$2:$L$545)/SUMMENPRODUKT([AAA.xlsx]XXX!$J$2:$L$545>0)*([AAA.xlsx]XXX!$AM$2:$AM$545=B6)



Leider kann ich die Datei nicht reinstellen, da dort sehr viele Firmeninformationen enthalten sind.
Ich hoffe Ihr versteht mein Problem.
Vielen Dank.

Gruss

[BoskoBiati]

Hi,

Informationen als eine Zahl oder eine leere Zelle.

Das ist was anderes als 0!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[Dietmar65]

Hi,


Das ist was anderes als 0!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Ich hatte meine Test-Tabelle doch mit leeren Zeilen aufgebaut um das zu verdeutlichen, vom Prinzip her ist es genau so- nur das es halt sehr viel größer ist.

Des Weiteren habe ich das auch in meinem ersten Post angegeben: "...Die Tabelle ist stark vereinfacht und hat auch mehrere leere Zeilen. ..."

Daher kann ich das jetzt nicht nachvollziehen. Oder verstehe ich hier etwas falsch?

Gruß

[GMG-CC]

Moin,
vorweg angemerkt: Ich habe heute meinen schlechten Tag und deshalb bin ich nicht so großzügig mit "Fertigprodukten" ...
In diesem Fall frage ich mich, warum es so schwer ist, für die paar Werte eine Wunsch-Lösung zu erstellen. Dann hätten die Kollegen vielleicht einiges weniger an Hirnschmalz verbraten und vor allen Dingen nicht für den Papierkorb gearbeitet.

Als kleine "Revanche" darfst du dir aussuchen, ob deine Wunsch-Lösung in den ScreenShot enthalten ist. 
Screenshot, weil hier ja schon genug "irritierende" Lösungen herumliegen. Und ich habe es mit Power Query erstellt.

[Dietmar65]

Hallo nochmals...
Also ehrlich gesagt verstehe ich hier die Aufregung nicht. Was genau habe ich falsch gemacht? Ich habe doch genau geschrieben, und auch in meiner Datei dargestellt, was ich ausrechnen wollte!?
Falls ich hier etwas falsch gemacht habe, tut es mir leid. Wobei mich auch interessieren würde was ich falsch gemacht habe.

@Günther: Leider wird das nicht funktionieren. Trotzdem danke für die Mühe.

Ich werde mir wohl was anderes überlegen müssen.
Gruß

[GMG-CC]

Moin Dietmar,
"genau beschrieben" ist nun wahrhaftig etwas anderes. Wie viele Interpretationsmöglichkeiten es gibt hast du ja an den unterschiedlichen Antworten erkannt.
Für dich, der du ja dein Wunsch-Ergebnis kennst wäre es doch ein leichtes gewesen diese 9 (belegten) Zeilen aufzudröseln und eben mal zu berechnen. Das Ergebnis dann in G1 und G2 und wir alle hätten gewusst, was bei einer Formel herauskommen muss.

... und btw: "Leider wird das nicht funktionieren" ist eine Aussage, die keinem der Helfer in irgendeiner Form weiter hilft. Auch hier gibt es wiederum diverse Interpretationsmöglichkeiten .