SOLVED

WENN und WENNFEHLER - zu wenig Argumente?

%3CLINGO-SUB%20id%3D%22lingo-sub-1558034%22%20slang%3D%22de-DE%22%3EIF%20and%20IF%20ERROR%20-%20too%20few%20arguments%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1558034%22%20slang%3D%22de-DE%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20make%20a%20combination%20of%20IF%20and%20WENNERROR.%3C%2FP%3E%3CP%3EIt%20contains%20an%20invoice%20-%20if%20this%20is%20less%20than%20-30%25%2C%20the%20result%20should%20be%20displayed%2C%20the%20same%20at%20more%20than%20%2B30%25.%20Otherwise%2C%20the%20cell%20should%20be%20empty.%20This%20also%20works%20without%20any%20problems.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20since%20not%20all%20of%20the%20cells%20mentioned%20in%20the%20invoice%20are%20filled%20or%20in%20some%20zero%2C%20Excel%20scolds%20of%20course%20if%20I%20want%20to%20divide%20by%20zero.%20I%20would%20like%20the%20cells%20in%20question%20to%20remain%20empty%20instead%20of%20%23DIV%2F0!%20Display.%20That's%20what%20WENNFEHLER%20is%20good%20for.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20previous%20construct%20looks%20like%20this%3A%3C%2FP%3E%3CP%3E%3DWENN(((E10-H10)%2FE10)%26lt%3B(-30%25)%3B((E10-H10)%2010)%3Bwenn(((e10-h10)%2Fe10)%3D%22%22%26gt%3B30%25%3B((E10-H10)%2FE10)%3B%22%3B%22%3B%20IF%20ERROR((E10-H10)%2FE10)%3B%22)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20doesn't%20let%20me%20do%20that%2C%20but%20always%20scolds%20%22Too%20few%20arguments%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20do%20I%20do%20wrong%2F%20what%20have%20I%20overlooked%3F%26nbsp%3B%3CLI-EMOJI%20id%3D%22lia_confused-face%22%20title%3D%22%3Aconfused_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1558034%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1558127%22%20slang%3D%22en-US%22%3ERe%3A%20WENN%20und%20WENNFEHLER%20-%20zu%20wenig%20Argumente%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1558127%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F745726%22%20target%3D%22_blank%22%3E%40Simva%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DWENNFEHLER(WENN((E10-H10)%2FE10%26lt%3B-30%25%3B(E10-H10)%2FE10%3BWENN((E10-H10)%2FE10%26gt%3B30%25%3B(E10-H10)%2FE10%3B%22%22))%3B%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1558131%22%20slang%3D%22de-DE%22%3ERe%3A%20IF%20and%20IF%20ERROR%20-%20too%20few%20arguments%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1558131%22%20slang%3D%22de-DE%22%3E%3CP%3EWorks%20perfectly%2C%20thank%20you!%20Makes%20total%20sense%20-%20I've%20already%20broken%20my%20head%20as%20I%20connect%20the%20two%20formula%20types%2C%20but%20I%20didn't%20think%20of%20that%20at%20all.%3C%2FP%3E%3CP%3EThank%20you%20very%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1558132%22%20slang%3D%22en-US%22%3ERe%3A%20WENN%20und%20WENNFEHLER%20-%20zu%20wenig%20Argumente%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1558132%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F745726%22%20target%3D%22_blank%22%3E%40Simva%3C%2FA%3E%26nbsp%3BYou're%20welcome!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hallo,

 

ich versuche eine Kombination aus WENN und WENNFEHLER zu basteln.

Darin ist eine Rechnung enthalten - ergibt diese weniger als -30%, soll das Ergebnis angezeigt werden, gleiches bei mehr als +30%. Ansonsten soll die Zelle leer sein. Das funktioniert auch problemlos.

 

Da aber nicht alle in der Rechnung erwähnten Zellen gefüllt sind bzw. in einigen Null steht, schimpft Excel natürlich wenn ich durch Null teilen will. Ich hätte gerne, dass die betreffenden Zellen auch leer bleiben, anstatt #DIV/0! anzuzeigen. Dafür bietet sich ja WENNFEHLER an.

 

Mein bisheriges Konstrukt sieht so aus:

=WENN(((E10-H10)/E10)<(-30%);((E10-H10)/E10);WENN(((E10-H10)/E10)>30%;((E10-H10)/E10);"";WENNFEHLER((E10-H10)/E10);"")))

 

Excel lässt mich das aber so nicht machen, sondern schimpft immer "Zu wenig Argumente".

 

Was mache ich falsch/ was habe ich übersehen? 

 

3 Replies
Highlighted
Best Response confirmed by Simva (Occasional Contributor)
Solution

@Simva 

Try this:

=WENNFEHLER(WENN((E10-H10)/E10<-30%;(E10-H10)/E10;WENN((E10-H10)/E10>30%;(E10-H10)/E10;""));"")

 

Highlighted

Funktioniert perfekt, danke! Ergibt auch total Sinn - habe mir schon den Kopf zerbrochen wie ich die beiden Formelarten miteinander verbinde, aber das ist mir so gar nicht eingefallen ^.^

Vielen Dank!

Highlighted

@Simva You're welcome!