Microsoft Excel

Herbers Excel/VBA-Archiv

WENN-DANN-Formel ohne WENN-Funktion

Betrifft: WENN-DANN-Formel ohne WENN-Funktion von: mpb
Geschrieben am: 25.10.2014 08:41:59

Hallo,

Aufgabenstellung: Wenn der Wert in einer Zelle, z.B. A1, kleiner als 500 ist, soll über eine Formel 1 ausgegeben werden, bei Werten über 100000 eine 0 und sonst der Wert aus A1.

Klassische Lösung mit WENN-Funktion:

=WENN(A1>100000;0;WENN(A1<500;1;A1))

Ich suche jetzt aus rein didaktischen Gründe (ich möchte jemandem die vielfältigen Möglichkeiten von Excel näherbringen) alternative Formeln, die nicht auf die WENN-Funktion zurückgreifen. Ein Beispiel wäre:

=((A1>=500)*A1)*(A1<=100000)+ (A1<500)

Auf weitere Vorschläge bin ich gespannt.

Gruß
Martin

  

Betrifft: komplizierter - aber aus Didaktik ? von: WF
Geschrieben am: 25.10.2014 10:47:33

Hi,

=WAHL(VERGLEICH(A1;{0;500;100000};1);1;A1;0)

Salut WF


  

Betrifft: Weitere Vorschläge erwünscht von: mpb
Geschrieben am: 25.10.2014 11:11:47

Danke. Ich stelle nochmal auf offen für evtl. weitere Vorschläge.


  

Betrifft: Ähnlich wie die von WF von: Tino
Geschrieben am: 25.10.2014 11:32:48

Hallo,

 AB
14991
2500500
31000010

Formeln der Tabelle
ZelleFormel
B1=WAHL(1+(A1<500)*1+(A1>100000)*2;A1;1;0)
B2=WAHL(1+(A2<500)*1+(A2>100000)*2;A2;1;0)
B3=WAHL(1+(A3<500)*1+(A3>100000)*2;A3;1;0)


Gruß Tino


  

Betrifft: kurz durch potenzieren ... von: neopa C (paneo)
Geschrieben am: 25.10.2014 11:53:58

Hallo Martin,

... so: =(A1*(A1<=10^5))^(A1>=500)

Es geht auch mit VERWEIS() und VERGELICH(), wird nur länger. Denn die Formel von WF ergibt für negative Werte und für exakt 100000 noch nicht das gewünschte..

Gruß Werner
.. , - ...


  

Betrifft: prima - aber 0 (null) ergibt #ZAHL! von: WF
Geschrieben am: 25.10.2014 12:07:46

.


  

Betrifft: das Essen hatte gerufen, ... von: neopa C (paneo)
Geschrieben am: 25.10.2014 12:39:20

Hallo WF,

... dann eben so: =(A1*(A1<=10^5)+(A1=0))^(A1>=500), welche so momentan noch immer die kürzeste Formel ist ;-)

Gruß Werner
.. , - ...


  

Betrifft: AW: komplizierter - aber aus Didaktik ? von: mpb
Geschrieben am: 25.10.2014 12:39:08

Hallo,

die Formel liefert für negative Zahlen in A1 #NV. Man kann selbstverständlich den Parameter 0 auf z.B. -100 setzen, aber wie funktioniert es für alle negativen Zahlen?

Bei 100000 ist das Ergebnis 0, sollte aber 100000 sein.

Gruß
Martin


  

Betrifft: na ja - ich sagte doch komplizierter von: WF
Geschrieben am: 25.10.2014 13:06:04

Hi,

statt 100000 trägst Du eben 100001 oder 100000,01 in die Formel ein.

Die von Dir ist eigentlich nicht optimierbar - lediglich ein Klammerpaar kannst Du sparen:
=(A1>=500)*A1*(A1<=100000)+(A1<500)
und statt 100000 könntest Du 10^5 schreiben - dann sind in der Formel aber nicht nur die Grundrechenarten

WF


  

Betrifft: =sverweis(kriterium, Matrix, Spaltenindex;WAHR) von: ransi
Geschrieben am: 25.10.2014 20:00:46

Hallo MArtin,

Du schriebst:"Klassische Lösung mit WENN-Funktion:"

Der Klassiker für dieses Problem ist eigentlich der sverweis().
Da trägst du deine Werte in eine Liste ein und sverweis() liefert das Ergebniß.
Hat eigentlich nur Vorteile...
Versuch mal mit =wenn(((((geschachteltbisdieFingerbluten))))) oder anderen Konstrukten 4, 5 oder noch mehr Kriterien zu erfassen. Da kommst du ziemlich schnell an deine Grenzen.


Tabelle1

 ABCDE
11234512345-10000000000001 
2  50012345 
3  1000000 
4     
5     

Formeln der Tabelle
ZelleFormel
B1=SVERWEIS(A1;C1:D3;2;WAHR)
D2=A1


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4



ransi


  

Betrifft: so aber nur bedingt ... von: neopa C (paneo)
Geschrieben am: 26.10.2014 09:50:35

Hallo ransi,

... Dein Vorschlag entspricht bzgl. des Ergebnisses für 100000 noch nicht der Vorgabe: "kleiner gleich" 100000 und müsste zudem für alle theoretisch möglichen Negativwerte auch noch angepasst werden.

Um das angestrebte mit SVERWEIS() in einer Formel zu realisieren, wäre ab Excel 2007 vielleicht folgende Formel: =WENNFEHLER(SVERWEIS(A1;{500.1;100000,00000001.0};2)*A1;1) eine weitere Variante. Alternativ ähnlich aber etwas knapper: =WENNFEHLER(VERWEIS(A1;{500;100000,00000001};{1.0})*A1;1)

Ich habe dabei bewusst darauf verzichtet, darin das WENNFEHLER() durch eine entsprechende Erweiterung in der konstanten Suchmatrix zu ersetzen.

Gruß Werner
.. , - ...


 

Beiträge aus den Excel-Beispielen zum Thema "WENN-DANN-Formel ohne WENN-Funktion"