Clever-Excel-Forum

Guten Abend zusammen, 
Ich habe ein fortlaufendes Worksheet, in dem sämtliche Spalten permanent fortlaufend sind, da immer wieder weitere Zeilen mit Inhalten gefüllt werden. Ich tüftele gerade an einer Formel, mit der ich eine Art Rendite errechnen kann.

Hierzu muss ich zum einen mein eingesetztes Kapital berechnen: Das Kapital umfasst ALLE Zahlenwerte, die in Spalte I:I stehen. In Spalte I befinden sich natürlich auch etliche Zellen, die leer sind. Wie kann ich diese ignorieren? Die einfache Summenformel funktioniert nicht. Alle Zahlen, die in Spalte I stehen, sollen addiert werden.

Zum anderen muss ich den Gewinn berechnen: Hier ist es am sinnvollsten, mit SUMMENPRODUKT zu arbeiten. Ich stehe aktuell bei =SUMMENPRODUKT((J:J="Ja")*1;H:H;I:I)-SUMMEWENN(J:J;"Nein";I:I). Hier sind allerdings, vermute ich, im ersten Teil der Formel die Arrays zu groß, die über die gesamten Spalten gehen, da auch hier etliche Zellen (noch) leer und ohne Inhalt sind - mit der Zeit werden diese allerdings gefüllt und ich möchte nicht permanent Den Bezug der Formel ändern. 

Wie bekomme ich das hin, dass in der Formel NUR Zeilen einbezogen werden, in denen in Spalte I ein Text (Ja/Nein) steht? 

Vielen Dank vorab !

Hola,

eliminier deine #DIV/0 Fehler mit Wennfehler(), dann klappt auch Summe().
Summenprodukt sollte man niemals über ganze Spalten laufen lassen.

Gruß,
steve1da

(05.02.2021, 23:33)steve1da schrieb: [ -> ]Hola,

eliminier deine #DIV/0 Fehler mit Wennfehler(), dann klappt auch Summe().
Summenprodukt sollte man niemals über ganze Spalten laufen lassen.

Gruß,
steve1da

Hallo,

Hättest du eine Idee, was man anstelle von SUMMENPRODUKT() nehmen könnte?

Hola,

beschreib doch mal, wie du den Gewinn errechnen willst.
Bilder helfen in der Regel nicht weiter btw.

Gruß,
steve1da

Hallöchen,

hast Du die Befürchtung, dass Du die gesamte Spalte füllst? Zuweilen reichen ja 100 Zeilen. Oder 1.000. Oder 10.000. Oder 100.000. Oder 1.000.000 ...

Zitat:Hier sind allerdings, vermute ich, im ersten Teil der Formel die Arrays zu groß, die über die gesamten Spalten gehen, da auch hier etliche Zellen (noch) leer und ohne Inhalt sind

Ich glaube, ein Array ist wohl eher nicht wegen Leerzeilen zu groß.

Was ist denn eigentlich das Problem? Ich habe hier 1 Mio Zeilen gefüllt, geht. Das Ergebnis ist nur recht groß, man sieht bei der kompletten Formel keinen Unterschied

Arbeitsblatt mit dem Namen 'Tabelle1'
	E	F	G	H	I	J
1	1,1111E+17	1,6667E+11	1,11111E+17	1	1	nein
2				2	2
3				3	3	ja
4				4	4	nein
5				5	5
6				6	6	ja
7				7	7	nein
8				8	8
9				9	9	ja
10				10	10	nein

Zelle	Formel
E1	=SUMMENPRODUKT((J:J="Ja")*1;H:H;I:I)
F1	=SUMMEWENN(J:J;"Nein";I:I)
G1	=SUMMENPRODUKT((J:J="Ja")*1;H:H;I:I)-SUMMEWENN(J:J;"Nein";I:I)
J1	=WENN(REST(ZEILE();3)=0;"ja";WENN(REST(ZEILE();3)=1;"nein";""))

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

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

Zitat:In Spalte I befinden sich natürlich auch etliche Zellen, die leer sind. Wie kann ich diese ignorieren? Die einfache Summenformel funktioniert nicht. Alle Zahlen, die in Spalte I stehen, sollen addiert werden.

Hallo,
mach eine Tabelle daraus (als Tabelle Formatieren oder STRG+T). Dann kannst du den Spaltennamen in der Summenformel verwenden.

Je nach Aufbau der Tabelle käme vielleicht auch eine Pivotauswertung in Frage.

(06.02.2021, 00:16)steve1da schrieb: [ -> ]Hola,

beschreib doch mal, wie du den Gewinn errechnen willst.
Bilder helfen in der Regel nicht weiter btw.

Gruß,
steve1da

Den Gewinn möchte ich berechnen, indem ich pro Zeile die Zellen der Spalten H und I multipliziere, WENN in Spalte J „Ja“ steht. Davon subtrahiert wird der Wert, der pro Zeile in Spalte I steht, WENN in Spalte J ein „Nein“ steht.

Als Tabelle formatiert:



Tabelle1

	H	I	J	K
1	Quote	%Einsatz	Richtig?
2	2,7	49,06	nein
3	2,6	50,94	ja
4	2,4	52,94	nein
5	2,7	47,06	ja
6				-1,20000000000573E-02

verwendete Formeln
Zelle	Formel	Bereich	N/A
K6	=SUMMENPRODUKT((Tabelle1[Quote]*Tabelle1[%Einsatz])*(Tabelle1[Richtig?]="Ja"))-SUMMENPRODUKT((Tabelle1[Quote]*Tabelle1[%Einsatz])*(Tabelle1[Richtig?]="nein"))

Formatierte Tabellen (Tabelle1)
Bereich	Name	Vorlage	Vorlage orig.
$H$1:$J$5	Tabelle1	Tabellenformat - Mittel 2	TableStyleMedium2

http://excel-inn.de/dateien/vba_beispiel..._addin.zip

http://Hajo-Excel.de/tools.htm

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 25.21 einschl. 64 Bit

Hallo Kiwi,

bitte verzichte darauf, die voran gegangenen Antworten zu zitieren. Das ist völlig überflüssig. Ebenso überflüssig ist es, in Spalte B die Formeln im Voraus einzutragen. Zitat aus der Excelhilfe:

[b]Erweiterung der Listenformatierung und -formeln.[/b] Wenn Sie neue Zeilen mit Daten am Ende einer Liste hinzufügen, werden von Excel auf die neuen Zeilen konsistente Formatierungen und Formeln übertragen. Damit ein Format erweitert wird, müssen drei von fünf vorangegangenen Zellen dasselbe Format verwenden. Damit eine Formel erweitert wird, müssen alle vorangegangenen Formeln konsistent sein.

Alternativ kannst du auch mit einer intelligenten Tabellle arbeiten, die verhält sich ebenso.