jeudi 22 août 2019

Same formula different behaviour

I have a Google Sheet with a formula that calulates a price based on different data provided by a Google Form. If I use arrayformula (my formula) in some cases I have a wrong calculation, while if I type the formula directly in the cell I receive the right output.

I've solved the issue by removing the arrayformula, but I wonder if there might be a different solution

This sometimes fails:

=arrayformula(SE(X2:X = "SI";SE(AB2:AB = "SI"; SE(E(Z2:Z = "SI"; Y2:Y = "NO");15;40); 50); SE(AC2:AC = "SI"; SE(AE2:AE ="SI"; SE(E(Z2:Z = "SI"; Y2:Y ="NO");10;25); 35); 50)))

This is working:

=SE(NON(VAL.VUOTO(X9));SE(X9:X = "SI";SE(AB9 = "SI"; SE(E(Z9 = "SI";      Y9:Y = "NO");15;40); 50); SE(AC9 = "SI"; SE(AE9 ="SI"; SE(E(Z9 = "SI"; Y9 = "NO");10;25); 35); 50));)

Text is in Italian, the keywords are:

SE = IF
E = AND
NON = NOT
VAL.VUOTO = ISBLANK

Aucun commentaire:

Enregistrer un commentaire