gesucht ist eine XL365- Formellösung ...

Forumthread: gesucht ist eine XL365- Formellösung ...

25.11.2023 17:40:37

neopa C

Hallo XL365 Formelfans,

... die (ohne Hilfszeile) für die max Anzahl an Zahlen ermittelt, deren Summe von links gerechnet einen vorgegebenen Grenzwert (im Beispiel 10) nicht erreicht.

Für das nachfolgende Beispiel (Datenwerte in D2:O2): wäre das gesuchte Ergebnis also 5 (Zahlen). Die Formel sollte möglichst ohne LET() und ohne LAMBDA auskommen, damit ich diese dann mit dem CSE-Tool von Flotter Feger in meiner XL-Version nachvollziehen kann.

Arbeitsblatt mit dem Namen 'Tabelle6'
	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2		Txt	4	0		-1	nix		4	-1	4	-3	1
3		0	5	5	5	4	4	4	8	6	10	7	8
4

Zelle	Formel
D3	=SUMME($D2:D2)
M3	=SUMME($D2:M2)

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

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

Gruß Werner
.. , - ...

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: thread weiter offen, aber in ner älteren XL-Version ...

26.11.2023 11:50:47

neopa C

Hallo zusammen,

... kann man es z.B. wie folgt ermitteln:
=ANZAHL(D2:INDEX(2:2;AGGREGAT(15;6;SPALTE(D2:O2)/(SUMMEWENN(INDIREKT("Z2S4:Z2S"&SPALTE(E2:O2););">0")>=10);1)))

In XL365 wird es so sicher auch gehen, aber ich hoff(t)e, daß es da etwas einfacher gehen sollte. Oder?

Gruß Werner
.. , - ...

nach- Frage

30.11.2023 17:18:02

Josef B

Hallo Werner

Hast du im Bereich D2:O2 wie in deinem Beispiel auch Text?
Ansonsten könnte man statt Indirekt auch MMULT verwenden.

Gruss Sepp

AW: komme leider erst heute dazu zu antworten ...

05.12.2023 08:49:25

neopa C

Hallo Sepp,

... war seit Donnerstag nicht mehr online. Tut mir leid, daß dies ausgerechnet nun war. Denn natürlich hab ich mich heute gefreut mal wieder von Dir zu lesen.
Nun zu Deiner Frage. Ja in dem Bereich können in unterschiedlicher Anzahl und Reihung Textwerte vorkommen. Genau daran lag für mich das Problem, weil diese eben auch nicht als 0 gezählt werden können.
Du hast aber eben mein Interesse geweckt, wie Deine Lösungsvorschlag mit MMULT() aussähe, wenn nur Zahlenwerte auszuwerten wäre?
Würde mich freuen, diese von Dir zu lesen.

Gruß Werner
.. , - ...

AW: thread weiter offen; nur noch zur Erläuterung ...

26.11.2023 12:11:18

neopa C

Hallo nochmal,

... damit kein Mißverständnis entsteht.

Eine einfacher nachvollziehbare XLS365 Formel suche ich für einen Bekannten, der wenig XL-Erfahrung hat und dem ich meine hier aufgezeigte Formel nicht wirklich vertretbar verständlich erklären kann. Letzteres insbesondere wegen dem INDIREKT()-Formelteil. Zu beachten ist auch, daß ich im Formelteil SPALTE(E2:O2) den Startformelbezug auf Spalte E nicht wegen dem Textwert in D2 gelegt habe, sondern weil ich so den Zahlenwert mit dem der Grenzwert überschritten wird nicht in die Ergebnisanzahl mit einbeziehe.

Gruß Werner
.. , - ...

OT: VBA ist deutlich weniger komplex

26.11.2023 17:41:13

Sulprobil

Function WievieleZahlenBevorSummeErreicht(r As Range, s As Double) As Long

Dim t As Double

Dim i As Long

Dim v As Variant

For Each v In r

    If IsNumeric(v) And v > "" Then

        t = t + v

        If t >= s Then

            WievieleZahlenBevorSummeErreicht = i

            Exit Function

        End If

        i = i + 1

    End If

Next v

WievieleZahlenBevorSummeErreicht = -1

End Function

AW: Eine Möglichkeit mit SEQUENZ ab Excel 2021

27.11.2023 16:54:59

Sulprobil

Hallo Werner,

Wenn der Bereich D als D2:O2 definiert ist:

=ANZAHL(INDEX(D;1):INDEX(D;VERGLEICH(1;--(MMULT(--(SEQUENZ(SPALTEN(D);SPALTEN(D))>=MTRANS(SEQUENZ(SPALTEN(D);SPALTEN(D))));MTRANS(WENNFEHLER(--D;0)))>=10);0)-1))

SEQUENZ wird hier lediglich dazu verwendet, eine untere linke Dreiecksmatrix aus Einsen zu erzeugen.

Diese Formel hat denselben Nachteil wie Deine, wenn die Summe 10 gar nicht erreicht wird. Dann kommt 0 (Null) heraus, aber besser wäre natürlich -1 wie bei meinem VBA Code.

Wir landen also erneut bei unserem alten Dissens, ob (nein, dass!) solcherart Formeln (nicht!) praxistauglich sind :-)

Have fun,
Bernd

AW: zwar das angestrebte Ergebnis ...

27.11.2023 17:39:27

neopa C

Hallo Bernd,

... wird mit Deine Formel ermittelt, aber mit einem größerem Funktion-Aufwand als mit meiner. Ich hatte jedoch auf eine Formel gehofft, die mit XL365er Funktionen eine Formelvereinfachung gegenüber meiner ergeben könnte.
Meine Formel ist mittlerweile auch praxiserprobt.

Das von Dir angesprochene Problem, daß der Grenzwert nicht erreicht oder überschritten werden könne, besteht für das angestrebte in der Praxis nicht. Es ist gesichert, das der Grenzwert erreicht wird, das hatte ich nur vergessen mitzuteilen, sorry.

Danke Dir nochmal für Deine Bemühungen.

Gruß Werner
.. , - ...

AW: Beide Formeln sind suboptimal

29.11.2023 05:18:12

Sulprobil

Hallo Werner,

Ich verstehe ja, dass Du ohne VBA auskommen möchtest.

Aber: das kleine VBA Programm checkt, ob die Summe überhaupt erreicht wird, es funktioniert auch für vertikale Eingabebereiche, und es führt keine überflüssigen Rechnungen durch.

Unsere beiden Formeln sind dagegen zu kompliziert, d.h. fehleranfällig. Meine Schlussfolgerung kennst Du: so programmiert man nicht, bei weniger versierten Anwendern wäre Verzweiflung vorprogrammiert.

Have fun,
Bernd

AW: sicherlich ...

29.11.2023 12:39:27

neopa C

Hallo Bernd,

... aber es gibt (immer wieder oder noch) Firmen die VBA-Einsatz in der Anwendung ihren MA in der Firma nicht gestatten und den kann ich auch so helfen, wenn ich Lust dazu habe. In meinem ehemaligen Berufsleben, war ich Anwender von mehreren dutzenden verschiedensten teils komplexen Softwareprodukte (u.a. fast ein Dutzend verschiedenster CAD-Software) in jeweils zig Versionsstufen. Gänzlich fehlerfrei war keins, was zumindest ich auch als durchaus menschlich ansehe. Denn wer oder was ist schon fehlerfrei.
Mit Excelformeln beschäftige ich mich hobbymäßig. Meine Brötchen muß ich mir damit nicht verdienen. Meine hier aufgezeigte Formel macht auch nach Meinung dessen, der sie inzwischen nutzt, genau das was sie soll und das ist für ihn und mich zumindest momentan entscheidend.

Gruß Werner
.. , - ...

AW: hatte solches auch mit ner XL365 Formel erhofft ...

26.11.2023 19:37:42

neopa C

Hallo Bernd,

... denn VBA soll hierfür nicht eingesetzt werden. Aber danke für Deine VBA-Lösung.
Möglicherweise haben die XL365-Formel Cracks dieses WE bisher keine Zeit gehabt/gefunden. Vielleicht ja morgen.

Thread ist also weiter offen.

Gruß Werner
.. , - ...

AW: Hab nur kurz mit Excel 2021 geguckt

26.11.2023 20:27:01

Sulprobil

Hallo Werner,

Aber mit 2021 (z. B. mit Sequenz) sah ich keine elegante Formellösung.

Viele Grüße,
Bernd

AW: danke für Deine Info; vielleicht heute ein Anderer? ....

27.11.2023 09:05:29

neopa C

Hallo Bernd,

... mein Ansatz mit dem CSE-Tool war, zunächst mit (da) FILTER_() die Text auszufiltern. Dann wollte ich diese Matrix mit INDEX() analog meinem INDIREKT()-Formelteil die weitere Auswertung vornehmen. Dies weil ich mal vor vielen Monaten in einem thread gelesen hatte, das {Boris} mit INDEX() in der XL365er Version neu (im Gegensatz zu den alten Versionen) damit auch eine ähnlich auswertbare Matrix bilden konnte. Aber entweder mache ich da was falsch oder mit dem CSE-Modul ist es mit INDEX() (noch) nicht möglich.

Deswegen hoffte ich ja auch auf XL365er-Formel-Cracks wie z.B. lupo1.
Thread deshalb weiter als offen gekennzeichnet.

Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

gesucht ist eine XL365- Formellösung ...

Forumthread: gesucht ist eine XL365- Formellösung ...

12
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

gesucht ist eine XL365- Formellösung ...

Forumthread: gesucht ist eine XL365- Formellösung ...

12 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

12
Beiträge zum Forumthread

Beiträge zu diesem Forumthread