Forum Discussion

mezkur7's avatar
mezkur7
Copper Contributor
Oct 15, 2019

SWITCH function

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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    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.

  • mezkur7's avatar
    mezkur7
    Copper Contributor
    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?

Resources