Home

SWITCH function

%3CLINGO-SUB%20id%3D%22lingo-sub-915235%22%20slang%3D%22en-US%22%3ESWITCH%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-915235%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20using%20Switch%20function%20for%20my%20worksheet%20and%20at%20some%20points%20even%20simple%20evaluations%20bug%20into%20zero.%3C%2FP%3E%3CP%3E%26nbsp%3BFor%20example%20%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSWITCH(Baz_Vade%3B180%3BR18*%24U%248%3B150%3B%5B%40%5B150%20g%C3%BCn%5D%5D%24U%247%3B120%3B%5B%40%5B120%20g%C3%BCn%5D%5D%24U%246%3B90%3B%5B%40%5B90%20g%C3%BCn%5D%5D%24U%245%3B60%3B%5B%40%5B60%20g%C3%BCn%5D%5D%24U%244%3B30%3B%5B%40%5B30%20g%C3%BCn%5D%5D%24U%243)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20evaluate%20this%20at%20the%20last%20step%20it%20becomes%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSWITCH(180%3B180%3B3960%3B150%3B0%3B120%3B0%3B90%3B0%3B60%3B0%3B30%3B0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eyet%20it%20calculates%20into%200.%20I%20find%20this%20ridiculous.%20Perhaps%20I%20can%20not%20see%20the%20obvious%20problem%20but%20it%20doesn't%20seem%20like%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20first%20used%20the%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSWITCH(Baz_Vade%3B%22180%20g%C3%BCn%22%3BR18*%24U%248%3B%22150%20g%C3%BCn%22%3B%5B%40%5B150%20g%C3%BCn%5D%5D%24U%247%3B%22120%20g%C3%BCn%22%3B%5B%40%5B120%20g%C3%BCn%5D%5D%24U%246%3B%2290%20g%C3%BCn%22%3B%5B%40%5B90%20g%C3%BCn%5D%5D%24U%245%3B%2260%20g%C3%BCn%22%3B%5B%40%5B60%20g%C3%BCn%5D%5D%24U%244%3B%2230%20g%C3%BCn%22%3B%5B%40%5B30%20g%C3%BCn%5D%5D%24U%243)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20it%20evaluated%20to%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESWITCH(%22180%20g%C3%BCn%22%3B%22180%20g%C3%BCn%22%3B3960%3B%22150%20g%C3%BCn%22%3B0%3B%22120%20g%C3%BCn%22%3B0%3B%2290%20g%C3%BCn%22%3B0%3B%2260%20g%C3%BCn%22%3B0%3B%2230%20g%C3%BCn%22%3B0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eit%20again%20evaluated%20to%200.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20base%20problem%20is%20the%20formula%20is%20repeated%20in%20the%20predecent%20formulas%20so%20there%20are%20circular%20references.%20Though%20the%20statement%20of%20Switch(Baz_Vade)%20is%20the%20key%20to%20not%20get%20in%20circular%20calculations%20by%20giving%20an%20outside%20source%20to%20iterate%20the%20function.%20I%20want%20to%20know%20specifically%20why%20the%20ending%20evaluations%20I%20proposed%20ends%20up%20calculated%200.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Emezkur7%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-915235%22%20slang%3D%22en-US%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-915309%22%20slang%3D%22en-US%22%3ERe%3A%20SWITCH%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-915309%22%20slang%3D%22en-US%22%3EI%20also%20tried%20IFS%20function%20just%20now.%20Miraculously%20(!)%20I%20also%20works%20up%20to%200.%3CBR%20%2F%3EI%20got%20so%20worked%20up%20when%20it%20evaluated%20IFS(TRUE%3B3960%3BFALSE%3B0%3BFALSE%3B0%3BFALSE%3B0%3BFALSE%3B0%3BFALSE%3B0)%20to%20be%200.%3CBR%20%2F%3EI'm%20speechless%20right%20now.%20What%20would%20you%20recommend%3F%3C%2FLINGO-BODY%3E
mezkur7
New Contributor

I'm using Switch function for my worksheet and at some points even simple evaluations bug into zero.

 For example : 

=SWITCH(Baz_Vade;180;R18*$U$8;150;[@[150 gün]]$U$7;120;[@[120 gün]]$U$6;90;[@[90 gün]]$U$5;60;[@[60 gün]]$U$4;30;[@[30 gün]]$U$3)

 

When I evaluate this at the last step it becomes:

 

=SWITCH(180;180;3960;150;0;120;0;90;0;60;0;30;0)

 

yet it calculates into 0. I find this ridiculous. Perhaps I can not see the obvious problem but it doesn't seem like it. 

 

When I first used the 

 

=SWITCH(Baz_Vade;"180 gün";R18*$U$8;"150 gün";[@[150 gün]]$U$7;"120 gün";[@[120 gün]]$U$6;"90 gün";[@[90 gün]]$U$5;"60 gün";[@[60 gün]]$U$4;"30 gün";[@[30 gün]]$U$3)

 

and it evaluated to:

 

SWITCH("180 gün";"180 gün";3960;"150 gün";0;"120 gün";0;"90 gün";0;"60 gün";0;"30 gün";0)

 

it again evaluated to 0.

 

The base problem is the formula is repeated in the predecent formulas so there are circular references. Though the statement of Switch(Baz_Vade) is the key to not get in circular calculations by giving an outside source to iterate the function. I want to know specifically why the ending evaluations I proposed ends up calculated 0. 

 

Thank you in advance,

 

mezkur7

2 Replies
I also tried IFS function just now. Miraculously (!) I also works up to 0.
I got so worked up when it evaluated IFS(TRUE;3960;FALSE;0;FALSE;0;FALSE;0;FALSE;0;FALSE;0) to be 0.
I'm speechless right now. What would you recommend?

@mezkur7 

Formula with circular reference is always (if only you didn't enable iterative calculations in settings) returns zero, it doesn't matter what is inside the formula.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies