Von Zahlen etwas abziehen

[midnight]

Hallo zusammen,

bisher habe ich in Calc in Spalte A Zahlen eingeben und diese wurden in der Nachbarspalte mit einer Zahl subtrahiert ausgeben.
von Zahlen bis 42 wird 1 und von Zahlen ab 42 2 abgezogen.
Das habe ich folgendermaßen gelöst:
=WENN(A1<42;A1-1;WENN(A1>41;A1-2;))

Jetzt soll es aber etwas umfangreicher werden:

Von Zahlen im Bereich -20 soll 1 abgezogen werden;
von 21-31 sollen 2 abgezogen werden;
von 32-42 3;
von 43-53 4
und von 54 bis 74 dann 5 abgezogen werden.

Könnte mir bitte jemand mit einer Formel aushelfen?

Danke!

[OttoZ]

Code: Alles auswählen

=A1-WENN(A1=0;0;WENN(A1<=20;1;WENN(A1<=31;2;WENN(A1<=42;3;WENN(A1<=53;4;WENN(A1<=74;5;0))))))

mfG OttoZ

edit: die Formel scheint falsch, ab der 3. Schachtelung liefert sie Fehler
edit2: dank Karolus ist die Formel nun brauchbar, Schreibfehler

[karolus]

Hallo

Code: Alles auswählen

=A1-VERGLEICH(A1;{0;21;32;43;54};1)

Karolus

[OttoZ]

Hi Karolus, Du Raumfahrer - ich Fußgänger.
Nicht nur, dass deine Lösung mir genial erscheint, sie arbeitet richtig,
was meine nicht tut.
Kannst Du mir verraten, warum meine geschachtelte Lösung ab der 3. Schachtel versagt?
danke für einen Tipp,
mfG OttoZ

[midnight]

Die Lösung von OttoZ kann ich noch nachvollziehen, allerdings funktioniert sie ab der dritten Stelle tatsächlich nicht mehr.

Karolus Methode ist mir komplett fremd, funktioniert dafür aber prächtig!


Danke euch beiden, werde morgen mal schauen, ob ich mir damit was passendes basteln kann.

[karolus]

Hallo

Kannst Du mir verraten, warum meine geschachtelte Lösung ab der 3. Schachtel versagt?

@Otto:
Weil du in der 4. WENN-schachtel einen :Doppelpunkt statt einem ;Semicolon eingebaut hast.

Karolus

[OttoZ]

@Karolus, danke sehr!
Man sucht immer die komplizierte Lösung und sieht Schreibfehler nicht!
Auch ich werde in den nächsten Tagen versuchen, deine Lösung zu verstehen.
mfG OttoZ

[Lupo]

Code: Alles auswählen

=A1-VERGLEICH(A1;{0;21;32;43;54};1)

Das ist die kürzeste Lösung, die Du auch unbedingt nehmen solltest.

Sie braucht ca. 25 Sekunden (bei 1048576 Verwendungen und dem Eingangswert 54).

-----

Generell könnte man übrigens auch einen Rechenweg statt eines Lookup-Wegs beschreiten, da Deine Vorgabe recht symmetrisch ist:
B1: =A1-GANZZAHL((A1+1)/11)-(A1<10)+(A1>=65) braucht ca. 15 Sekunden, also ca. 60%.

Sie ist also bei massenhaftem Einsatz oder bei sehr langen Lookup-Listen SCHNELLER, länger und nicht mehr selbsterklärend, wie oben die zitierte - und sie berechnet A1-Werte nur von -1 bis 75 korrekt (Du benötigst anscheinend nur 0 bis 74, so dass es ausreicht).

-----

Sind die Eingabewerte von 0, 1, ..., 74 strikt ganzzahlig (kann man per Daten-Gültigkeit so eingrenzen), klappt noch eine weitere Verschlankung (A1 geht dann nur noch 2mal statt 4mal in die Formel ein, wie bei der VERGLEICH-Formel auch):
B1: =GANZZAHL((A1+0,9)/1,1)+GANZZAHL((A1-10)/55) braucht ca. 15 Sekunden, oder
B1: =GANZZAHL(MAX((A1-9)*10/{11;10}+{9;4}))-(A1<10) braucht ca. 21 Sekunden

-----

Die vorletzte Formel ist im Aufbau wieder symmetrisch und erlaubt daher die Kurzform mit nur noch einem A1:
B1: {=SUMME(GANZZAHL((A1+{0,9;-10})/{1,1;55}))} - als Matrixformel eingeben; funktioniert nur bei ganzen Zahlen

Das alles ist jetzt rein akademisch und sollte eigentlich nicht verwirren

[Lupo]

@egsel:

Aussagen sollte man dann aber auch begründen und belegen (meine Messwerte kann jeder nachvollziehen, mit Korrekturen aufgrund unterschiedlich schneller Rechner). Du hast es nicht getestet, sonst würdest Du so etwas nicht schreiben.

Je länger ein VERGLEICH der Länge N wird, desto langsamer wird er auch (allerdings nur etwa um Wurzel N, binäre Suche). Ein Rechenweg hingegen bleibt immer gleich schnell.

Auch ich finde Karolus' Lösung am besten. Sie ist aber nicht die schnellste. Außerdem ist sie nicht konstant schnell.

[Lupo]

die Formel von Karolus ist die schnellste !

Das hast genau Du geschrieben. Dann belege es auch.

Dich interessiert mein Beitrag nicht. Braucht er ja auch nicht. Denn ich schreibe am Ende, dass es nur von akademischem Interesse ist - und das hast Du nicht. Es könnte aber andere interessieren!