Minimum/Maximum spezial

[Reinhard Schüll]

Hallo NG-Funktionsexperten,

wie läßt sich aus einer dynamischen Datenreihe aus den letzten 10 (oder auch
variabel ..x ) Werten der Maximal-/Minimalwert ermitteln?

Gruß Reinhard

[Alexander Wolff]

Post by Reinhard Schüll
wie läßt sich aus einer dynamischen Datenreihe aus den letzten 10 (oder
auch variabel ..x ) Werten der Maximal-/Minimalwert ermitteln?

Dein dynamischer Datenbereich besteht ja schon und heiße hier M. Dann

=MAX(BEREICH.VERSCHIEBEN(M;ZEILEN(M)-10;))

10 ist variabel.

[Reinhard Schüll]

Hallo Alexander,

mein (der von der Tabelle natürlich) dynamischer Bereich bewegt sich
innerhalb B2:B3600. Das hilft MIR leider auch unter Aufbietung meiner
bescheidenen Kenntnisse noch nicht, den Wahrheitsgehalt deines Vorschlages
nachzuvollziehen ... ;-).
Also bitte wie fülle ich diese Wissenslücke von "M"?
Ich kenne zwar die Funktion
=SUMMENPRODUKT((D3:D3600="")*D2:D3599)
die mir den letzten Wert der dynamischen Reihe zurückgibt, aber wie knüpfe
ich da dran an?

Gruß Reinhard

[Melanie Breden]

Hallo Reinhard,

Post by Reinhard Schüll
mein (der von der Tabelle natürlich) dynamischer Bereich bewegt sich
innerhalb B2:B3600.

für diesen Bereich kannst du meine Matrixfromel wie folgt anpassen:

=MAX(BEREICH.VERSCHIEBEN(B1;MAX((B1:B3600>0)*ZEILE(1:3600))-10;;10))


Mit freundlichen Grüssen
Melanie Breden

[Claus Busch]

Hallo Reinhard,

ohne Matrixformel und ohne die Formel umändern zu müssen, falls der Bereich
sich vergrößern sollte:
=MAX(BEREICH.VERSCHIEBEN(B1;VERWEIS(2;1/(B1:B65535<>"");ZEILE(B:B))-10;;10))
=MIN(BEREICH.VERSCHIEBEN(B1;VERWEIS(2;1/(B1:B65535<>"");ZEILE(B:B))-10;;10))


Mit freundlichen Grüssen
Claus Busch

[Melanie Breden]

Hallo Claus,

Post by Claus Busch
ohne Matrixformel und ohne die Formel umändern zu müssen, falls der Bereich
=MAX(BEREICH.VERSCHIEBEN(B1;VERWEIS(2;1/(B1:B65535<>"");ZEILE(B:B))-10;;10))
=MIN(BEREICH.VERSCHIEBEN(B1;VERWEIS(2;1/(B1:B65535<>"");ZEILE(B:B))-10;;10))

deine Formel gehört übrigens ebenfalls zu den Matrixformeln.

Der Unterschied besteht lediglich darin, dass sie nicht mit der
Tastenkombination 'Strg + Shift + Enter' abgeschlossen werden muss.


Mit freundlichen Grüssen
Melanie Breden

[Claus Busch]

Hallo Melanie,

Post by Melanie Breden
Der Unterschied besteht lediglich darin, dass sie nicht mit der
Tastenkombination 'Strg + Shift + Enter' abgeschlossen werden muss.

dies hatte ich damit ja auch gemeint.
Dass Verweis, Sverweis, Index, Vergleich usw. zu den Matrixformeln gehört,
ist mir bewusst.


Mit freundlichen Grüssen
Claus Busch

[Reinhard Schüll]

Hallo NG,

"wer Recht hat zahlt a Maß" sagt man in Bayern ;-).

Gruß Reinhard

[Reinhard Schüll]

Hallo Claus,

funktionieren beide Formeln sofort.

Gruß Reinhard

[Reinhard Schüll]

Hallo Claus,

noch ne kleine Anfrage. Kannst du mir bitte den Syntax für diese Formeln in
VBA liefern?

result = Application.xyz ... usw ...

Gruß Reinhard

Post by Melanie Breden
Hallo Reinhard,
ohne Matrixformel und ohne die Formel umändern zu müssen, falls der Bereich
=MAX(BEREICH.VERSCHIEBEN(B1;VERWEIS(2;1/(B1:B65535<>"");ZEILE(B:B))-10;;10))
=MIN(BEREICH.VERSCHIEBEN(B1;VERWEIS(2;1/(B1:B65535<>"");ZEILE(B:B))-10;;10))
Mit freundlichen Grüssen
Claus Busch
--
Win XP Prof SP2 / Vista Ultimate
Office 2003 SP2 / 2007 Ultimate

[Claus Busch]

Hallo Reinhard,

probiers mal so:
Range("D1").Formula = _
"=MAX(OFFSET(B1,LOOKUP(2,1/(B1:B65535<>""""),ROW(B:B))-10,,10))"
oder so:
Range("E1").Value = _

Evaluate("MAX(OFFSET(B1,LOOKUP(2,1/(B1:B65535<>""""),ROW(B:B))-10,,10))")


Mit freundlichen Grüssen
Claus Busch

[Reinhard Schüll]

Hallo Claus,

funktionieren auch in der MIN-Version!
Vielen Dank.

Gruß Reinhard

[Alexander Wolff]

Post by Reinhard Schüll
mein (der von der Tabelle natürlich) dynamischer Bereich bewegt sich
innerhalb B2:B3600. Das hilft MIR leider auch unter Aufbietung meiner
bescheidenen Kenntnisse noch nicht, den Wahrheitsgehalt deines Vorschlages
nachzuvollziehen ... ;-).
Also bitte wie fülle ich diese Wissenslücke von "M"?
Ich kenne zwar die Funktion
=SUMMENPRODUKT((D3:D3600="")*D2:D3599)
die mir den letzten Wert der dynamischen Reihe zurückgibt, aber wie knüpfe
ich da dran an?

=MIN(BEREICH.VERSCHIEBEN(D3:D3600;ANZAHL(D:D)-10+x;;10))

(ungetestet. +x, weil Du bei D3 statt D1 beginnst und der Inhalt von D1:D2
unbekannt ist. Datenreihe ausschließlich mit Zahlen gefüllt, keine leeren
Zellen. MAX ebenso)

[Reinhard Schüll]

Hallo Alexander,
wenn ich in der Formel
=MIN(BEREICH.VERSCHIEBEN(D3:D3600;ANZAHL(D:D)-10+x;;10))
.. auch als Matrixformel eingegeben ... und x mit 1 ..., sowie das "D" gegen
das "B" austausche dann klappt es einwandfrei .. nur so n kleiner Scherz am
Rande ..
Also auch deine Matrixlösungen funktionieren.
Gruß Reinhard

[Alexander Wolff]

Post by Alexander Wolff
Dein dynamischer Datenbereich besteht ja schon und heiße hier M. Dann
=MAX(BEREICH.VERSCHIEBEN(M;ZEILEN(M)-10;))
10 ist variabel.

Für MIN dann so (auch für MAX nötig, falls anschließende Zellen nicht leer):

=MIN(BEREICH.VERSCHIEBEN(M;ZEILEN(M)-10;;10))

[Melanie Breden]

Hallo Reinhard,

Post by Reinhard Schüll
wie läßt sich aus einer dynamischen Datenreihe aus den letzten 10 (oder auch
variabel ..x ) Werten der Maximal-/Minimalwert ermitteln?

angenommen, die Daten stehen in Spalte A und gehen maximal bis Zeile 1000,
dann verwende folgende Matrixformel:

=MAX(BEREICH.VERSCHIEBEN(A1;MAX((A1:A1000>0)*ZEILE(1:1000))-10;;10))

Beende die Eingabe der Formel mit der Tastenkombination 'Strg + Shift + Enter'-
Excel fügt dann geschweifte Klammern um die Formel herum ein.

Für den Minimalwert nur die erste MAX-Funktion gegen MIN tauschen:

=MIN(BEREICH.VERSCHIEBEN(A1;MAX((A1:A1000>0)*ZEILE(1:1000))-10;;10))


Mit freundlichen Grüssen
Melanie Breden

[Reinhard Schüll]

Hallo Melanie,

du hilfst mir da schon konkreter.

Die Formel
={MAX(BEREICH.VERSCHIEBEN(B2;MAX((B2:B3600>0)*ZEILE(2:3600))-10;;10))}
funktioniert, doch die Formel
={MIN(BEREICH.VERSCHIEBEN(B2;MIN((B2:B3600>0)*ZEILE(2:3600))-10;;10))}
verweigert mit "#Bezug" die Aufgabe. Die geschweifte Klammer ist vorhanden,
verschwindet wenn ich editieren will .. ich denke das ist normal so bei
Matrixformeln.

Also ich sehe auch nur den Tausch von MAX gegen MIN, aber Excel will nicht.

Gruß Reinhard

[Melanie Breden]

Hallo Reinhard,

Post by Reinhard Schüll
Die Formel
={MAX(BEREICH.VERSCHIEBEN(B2;MAX((B2:B3600>0)*ZEILE(2:3600))-10;;10))}
funktioniert, doch die Formel
={MIN(BEREICH.VERSCHIEBEN(B2;MIN((B2:B3600>0)*ZEILE(2:3600))-10;;10))}
verweigert mit "#Bezug" die Aufgabe. Die geschweifte Klammer ist vorhanden,
verschwindet wenn ich editieren will .. ich denke das ist normal so bei
Matrixformeln.

du hast die Formel ja doch abgeändert ;-)

Deine MAX-Formel müsste eigentlich auch einen falschen Wert liefern?!

Die Funktion ZEILE orientiert sich an den tatsächlichen Zeilen der Tabelle,
nicht an deinem Bereich. Deswegen beginne die Bereiche bei Zeile 1.

Post by Reinhard Schüll
=MIN(BEREICH.VERSCHIEBEN(A1;MAX((A1:A1000>0)*ZEILE(1:1000))-10;;10))

Demnach muss die MIN-Formel auf den neuen Bereich so aussehen:
=MIN(BEREICH.VERSCHIEBEN(B1;MAX((B1:B3600>0)*ZEILE(1:3600))-10;;10))

Funktioniert es jetzt?


Mit freundlichen Grüssen
Melanie Breden

[Reinhard Schüll]

Hallo Melanie,

ja fast ...

bist du sicher, dass ich mit der MIN-Version die letzten 10 Werte erfasse?
Oder sind es nur 9?

Gruß Reinhard

Viele Hunde sind des Hasen tot ... blicks schon fast nicht mehr wer welche
Lösungen produziert hat .. :-D

[Melanie Breden]

Hallo Reinhard,

Post by Reinhard Schüll
bist du sicher, dass ich mit der MIN-Version die letzten 10 Werte erfasse?
Oder sind es nur 9?

ja, da bin ich mir bei meinen Test ziemlich sicher.


Mit freundlichen Grüssen
Melanie Breden

[Reinhard Schüll]

Hallo Melanie,

wenn die letzten 10 Messwerte alle "0" sind, dann zeigt deine (bzw. von mir
abgeänderte) Formel die MIN, bzw. MAX-Werte an vor der 0-Serie.

Gruß Reinhard

[Reinhard Schüll]

Hallo NG-Helfer,

hier meine getesteten (zum Teil geänderten nach der Methode Try and Error
..) Formeln, die alle das selbe Ergebnis bringen:

={MIN(BEREICH.VERSCHIEBEN(B2;MAX((B2:B3600>0)*ZEILE(2:3600))-11;;10))}
=MIN(BEREICH.VERSCHIEBEN(B2:B3600;ANZAHL(B:B)-11;;10))
=MIN(BEREICH.VERSCHIEBEN(B1;VERWEIS(2;1/(B1:B65535<>"");ZEILE(B:B))-10;;10))

bzw.

={MAX(BEREICH.VERSCHIEBEN(B2;MAX((B2:B3600>0)*ZEILE(2:3600))-11;;10))}
=MAX(BEREICH.VERSCHIEBEN(B2:B3600;ANZAHL(B:B)-11;;10))
=MAX(BEREICH.VERSCHIEBEN(B1;VERWEIS(2;1/(B1:B65535<>"");ZEILE(B:B))-10;;10))

... hab sie alle parallel getestet im Wirkbetrieb.

Gruß Reinhard