Why does calc get confused by .setFormula('=MAX(4.5,3)')?

[yonkiman]

I’ve been using setFormula in my python calc macro for a few days, and it’s worked fine with all the ‘=(A4 * B4) -D3’ formulas I’ve needed. But now I need to use some functions, and it’s acting flaky. For the line:

sheet.getCellByPosition(column, row).setFormula('=MAX(4)')

The cell content is 4 (as expected).

sheet.getCellByPosition(column, row).setFormula('=MAX(4,3)')

The cell content is #NAME? That is not what I expected.

If I double=click on the #NAME? cell, I see the correct formula (=MAX(4,3)). If I then ^A to select the formula, ^X to cut it, and ^V to immediately paste the formula back in, then hit Enter, the cell responds with the correct value of 4.

This is the simplest form of the problem I could find. I found it because this much longer formula:

setFormula('=-' + quantity_cell + '*100*MAX(((0.25*' + underlying_cell + ')+MAX(' + underlying_cell + '-' + strike_cell + ',0) +' + price_cell + '), (0.15*' + strike_cell + '+' + price_cell + '))')

Behaves the same way, except instead of #NAME?, the error in the cell is Err:508. Again, an in-place cut and paste will make the formula work, but I’d like to have calc display it correctly without the intervention.

I suspect this is a bug, but perhaps there’s a work-around?

This is LO 5.1.2.2, and the problem occurs under Ubuntu 16.04 and Windows 10.

[Ratslinger]

Replace comma with semicolon.

  Syntax
 MAX(Number1; Number2; ...Number30)
 Number1; Number2;...Number30 are numerical values or ranges. 

Not sure why but comma works when directly entered.

[yonkiman]

That did it! I’m not gonna ask why but I’m delighted to have a solution!

[Ratslinger]

It bugged me. Separators are set in Tools->Options under LibreOffice Calc->Formula. Function is set as comma (can be changed to semicolon) whereas the macro requires defined syntax of semicolon.