Some modifications to the rules for the use of Real Statistics’ lambda functions using cell formulas are necessary for users of a non-English language version of Excel. These modifications are only applicable to the Real Statistics lambda functions and don’t apply to the Excel LAMBDA function.
When a comma is used as the decimal symbol
Example 1
As explained in Real Statistics’ lambda functions using cell formulas, you can use Real Statistics’ lambda capabilities to pass a function as an argument to a worksheet formula. E.g. we can express the function f(x) = 4x2 via the worksheet formula =4*A2^2 placed in cell A1. Here, A2 serves as a dummy reference to x.
We can then use the formula =DERIV(3.2, A1) to obtain the derivative of f(x) = 4x2 at x = 3.2. Here, we are using the Real Statistics worksheet function DERIV that takes the arguments DERIV(x,R1,incr,Rx) as described in Numerical Differentiation.
This approach works fine except when your version of Excel uses a comma as the decimal symbol. In this case, you need to use the formula =DERIV(“3.2”;A1) instead. As usual, a semi-colon is used to separate arguments, but the value assigned to x must be expressed as 3.2 enclosed in quotations instead of 3,2.
Note, however, if you place the value 3,2 in cell C1 (or C1 contains a formula with this value), then you can use the formula =DERIV(C1;A1).
The situation is similar if you explicitly set the incr argument in DERIV instead of using the default value of 0.000001. Usually, the formula =DERIV(3,A1) is equivalent to =DERIV(3,A1,0.000001), but when your version of Excel uses a comma as the decimal symbol, then =DERIV(3;A1) is equivalent to =DERIV(3;A1;”0.000001″) or =DERIV(3;A1;D1) if D1 contains the value 0,000001.
Example 2
If A1 expresses the formula f(x) = 2.1x3+1.7, then we could place the worksheet formula =2.1*A2^3+1.7 in cell A1 and then use =EVALS(A1,2) to obtain the value f(2) = 2.1(2)3+1.7 = 18.5. Similarly, the formula =EVALS(A1,2.5) could be used to obtain the value f(2.5) = 2.1(2.5)3+1.7 = 34.5125.
For versions of Excel where a comma is used as the decimal symbol, these formulas would produce an error value. Instead, you need to place the formula =2,1*A2^3+1,7 in cell A1 (replacing 2.1 and 1.7 by 2,1 and 1,7, respectively). To obtain f(2), you would use the formula =EVALS(A1;2), using a semi-colon instead of a comma to separate arguments. To obtain f(2.5), you need to use the formula =EVALS(A1;”2.5″). Here, the constant 2,5 needs to be expressed as 2.5 enclosed in quotation symbols.
Guiding principle
We see that formulas in a cell use the usual conventions (commas for decimals and semi-colons for argument separators), but constants in the lambda formulas (EVALS and DERIV in the examples) use the English-language convention of a period for the decimal symbol, although with the constant enclosed in quotes.
Other issues
Example 3
The guiding principles also hold for the names of worksheet functions. In the examples given above, we used Real Statistics functions. The names of these functions are the same in all versions of Excel. This is not the case for the built-in Excel functions.
Let’s use the POISSON.DIST(x, mean, cum) worksheet function as an example. In the Italian version of Excel, this function is expressed as DISTRIB.POISSON. Now let f(x) = the cumulative distribution function at x for the Poisson distribution with mean 2.4. In my English-language version of Excel, I could obtain f(3.1) via the formula =POISSON.DIST(3.1,2.4,TRUE) or via the formula =EVALS(A1,2.4) where I place the formula =POISSON.DIST(A2,2.4,TRUE) in cell A1.
In the Italian version, I use the formula =EVALS(A1;”3.1″) where I place the formula DISTRIB.POISSON(A2;2,4;VERO) in cell A1. Here, VERO is the Italian way of expressing TRUE. As in the guiding principle, I use the comma form 2,4 in the formula in cell A1, but the period form “3.1” inside the EVALS formula.
Example 3
There is one more issue to take care of when using a non-English language version of Excel. This time, let’s define f(x,y,z) = POISSON.DIST(x, y, z). Thus, z takes the value TRUE or FALSE. In Example 3, the TRUE/FALSE value was expressed in the formula in cell A1 (via VERO in the Italian version).
This time, we need to pass the Boolean value as an argument in EVALS (or some other lambda function). The proper way to do this is not to use Boolean values at all, but to instead replace TRUE by 1 and FALSE by 0. This works since POISSON.DIST(x, y, TRUE) is equivalent to POISSON.DIST(x, y, 1). Also, POISSON.DIST(x, y, FALSE) is equivalent to POISSON.DIST(x, y, 0).
In an English-language version of Excel, we can place the formula =POISSON.DIST(A2,A3,A4) in cell A1. We can then calculate f(3.1,2.5,TRUE) via the formula =EVALS(A1,3.1,2.5,1). In an Italian version of Excel, we achieve the same result by placing the formula =DISTRIB.POISSON(A2;A3;A4) in cell A1 and then using the formula =EVALS(A1;”3.1″;”2.5″;1).
References
Microsoft (2021) Lambda function
https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67
Easy Excel (2023) Excel in other languages
https://easy-excel.com/excel-in-other-languages/