Ask Your Question
0

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

asked 2016-05-06 03:50:56 +0100

yonkiman gravatar image

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
2

answered 2016-05-06 19:07:27 +0100

Ratslinger gravatar image

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.

edit flag offensive delete link more

Comments

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

yonkiman gravatar imageyonkiman ( 2016-05-06 19:53:17 +0100 )edit

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.

Ratslinger gravatar imageRatslinger ( 2016-05-06 21:21:12 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-05-06 03:50:56 +0100

Seen: 231 times

Last updated: May 06 '16