Anzahl aufeinanderfolgender Werte unterhalb eines Schwellenwertes

[Shadow]

Hallo zusammen, 

ich würde gerne die Anzahl aufeinanderfolgender Werte unterhalb eines Schwellenwertes berechnen. Wie stelle ich das am besten an?

In der Spalte B2 bis B1000 sind verschiedene Werte von 40 bis 100 eingetragen. Ich würde nun gerne die Anzahl der Werte berechnen, die in aufeinander folgenden Zeilen liegen (mindestens 15 aufeinanderfolgende Zeilen) und dabei niedriger als (der Schwellenwert von) 70 sind.

Über Vorschläge würde ich mich sehr freuen

[LCohen]

=SUMMENPRODUKT(
(B02:B0986<70)*
(B03:B0987<70)*
(B04:B0988<70)*
(B05:B0989<70)*
(B06:B0990<70)*
(B07:B0991<70)*
(B08:B0992<70)*
(B09:B0993<70)*
(B10:B0994<70)*
(B11:B0995<70)*
(B12:B0996<70)*
(B13:B0997<70)*
(B14:B0998<70)*
(B15:B0999<70)*
(B16:B1000<70))

zählt die entsprechenden Blöcke: 

15er = 1
16er = 2
17er = 3 usw.

Das kann man sicher noch genauer hinbekommen.

[Shadow]

Vielen Dank!

[Fred11]

Hallo Shadow,

ich habe auch mal ein bisschen rumprobiert.
Anbei ein Lösungsvorschlag mit Hilfsspalten - siehe angehängte Beispieldatei

Hilfsspalte1 ©
prüft, ob die Werte in Spalte B kleiner als der Schwellenwert sind

Hilfsspalten 2 und 3 (D, E)
Ermittlung der Anzahl der aufeinanderfoglenden (zusammenhängenden) Werte, die kleiner als der Schwellenwert sind
(mit der Funktion VERGLEICH in Spalte D wird geprüft, wann die nächste 0 kommt…)


Gruß
Fred

[Shadow]

Hallo Fred,

das ist wahnsinn! Vielen, vielen Dank!!!

[Shadow]

Ich habe noch eine weiterführende Frage:

In der Spalte B können aufeinander folgende Werte, die unterhalb des Schwellenwertes liegen, von Werte unterbrochen werden, die oberhalb des Schwellenwertes liegen, z. B.:

59
54
52
49
68
51
45
41
46
48
66
67
59
48
65
71
71
72
71
71
66
64
63
56
48

Wie sollte die Formel aussehen, das hierbei nicht nur 15 aufeinander folgende Werte unter 70 gezählt werden, sondern ebenso die 5 Ausreißer (die knapp über 70 liegen), als auch die 5 darauf folgenden Werte (die wieder unter 70 liegen)? Wenn es mehr als 5 Ausreißer sind (bspw. 6, 7 oder 8), dann sollen nur die ersten 15 Werte als Block gezählt werden, sonst alle 25.

Vielen Dank für eure Hilfe!

[Fred11]

Hallo Shadow,

kannst du dann nicht einfach den Schwellenwert generell entsprechend höher setzen ?

....sondern ebenso die 5 Ausreißer (die knapp über 70 liegen), als auch die 5 darauf folgenden Werte (die wieder unter 70 liegen)? Wenn es mehr als 5 Ausreißer sind (bspw. 6, 7 oder 8), dann sollen nur die ersten 15 Werte als Block gezählt werden, sonst alle 25.

So wie du das hier beschreibst, wird das meiner Meinung nach schwierig, das sinnvoll mit einer Formel abzufragen und auszuwerten (ich wüßte aktuell nicht, wie)

Was ist ausserdem z.B. wenn weniger als 5 Ausreisser zwischen den regulären Blöcken liegen (z.B. nur einer oder zwei…) ?
Wie sollen die dann behandelt werden ?
Oder was ist wenn die regulär kleineren Werte und die Ausreisser bunt gemischt sind ?
Was ist, wenn ein (zusammenhängender) Block von Ausreiser nicht zwischen zwei regulären Blöchen liegt, sondern z.B. am Ende eines Blockses ?
Sollen diese Ausreisser dann auch berücksichtigt werden ?

Das sind einfach sehr viele individuelle Bedingungen.
Man könnte vielleicht schon was hinkriegen mit nochmal 3…4 zusätzlichen Hilfsspalten oder so - aber ich befürchte, irgendwann wird das Formel-/Hilfsspaltenknstrukt dann so kompliziert, dass es am Ende keiner mehr (inklusive mir…) versteht.

Eventuell liesse sich das besser mit VBA lösen (kannst du VBA ?) - aber selbst hier wird das ein komplexes Konstrukt mit sehr vielen WENN/DANN-Abfragen…

Was willst du eigentlich mit dem ganzen bezwecken; was sind denn das für Werte ?
Vielleicht gibt es ja noch einen anderen Workaround….

Gruß
Fred