Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 06 May 2016 21:21:12 +0200Why does calc get confused by .setFormula('=MAX(4.5,3)')?https://ask.libreoffice.org/en/question/69419/why-does-calc-get-confused-by-setformulamax453/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. Fri, 06 May 2016 03:50:56 +0200https://ask.libreoffice.org/en/question/69419/why-does-calc-get-confused-by-setformulamax453/Answer by Ratslinger for <p>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:</p>
<pre><code>sheet.getCellByPosition(column, row).setFormula('=MAX(4)')
</code></pre>
<p>The cell content is <code>4</code> (as expected).</p>
<pre><code>sheet.getCellByPosition(column, row).setFormula('=MAX(4,3)')
</code></pre>
<p>The cell content is <code>#NAME?</code> That is <em>not</em> what I expected.</p>
<p>If I double=click on the <code>#NAME?</code> cell, I see the correct formula (<code>=MAX(4,3)</code>). 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 <code>4</code>.</p>
<p>This is the simplest form of the problem I could find. I found it because this much longer formula:</p>
<pre><code>setFormula('=-' + quantity_cell + '*100*MAX(((0.25*' + underlying_cell + ')+MAX(' + underlying_cell + '-' + strike_cell + ',0) +' + price_cell + '), (0.15*' + strike_cell + '+' + price_cell + '))')
</code></pre>
<p>Behaves the same way, except instead of <code>#NAME?</code>, the error in the cell is <code>Err:508</code>. 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.</p>
<p>I suspect this is a bug, but perhaps there's a work-around?</p>
<p>This is LO 5.1.2.2, and the problem occurs under Ubuntu 16.04 and Windows 10. </p>
https://ask.libreoffice.org/en/question/69419/why-does-calc-get-confused-by-setformulamax453/?answer=69449#post-id-69449Replace 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.Fri, 06 May 2016 19:07:27 +0200https://ask.libreoffice.org/en/question/69419/why-does-calc-get-confused-by-setformulamax453/?answer=69449#post-id-69449Comment by yonkiman for <p>Replace comma with semicolon. </p>
<pre><code> Syntax
MAX(Number1; Number2; ...Number30)
Number1; Number2;...Number30 are numerical values or ranges.
</code></pre>
<p>Not sure why but comma works when directly entered.</p>
https://ask.libreoffice.org/en/question/69419/why-does-calc-get-confused-by-setformulamax453/?comment=69451#post-id-69451That did it! I'm not gonna ask why but I'm delighted to have a solution!Fri, 06 May 2016 19:53:17 +0200https://ask.libreoffice.org/en/question/69419/why-does-calc-get-confused-by-setformulamax453/?comment=69451#post-id-69451Comment by Ratslinger for <p>Replace comma with semicolon. </p>
<pre><code> Syntax
MAX(Number1; Number2; ...Number30)
Number1; Number2;...Number30 are numerical values or ranges.
</code></pre>
<p>Not sure why but comma works when directly entered.</p>
https://ask.libreoffice.org/en/question/69419/why-does-calc-get-confused-by-setformulamax453/?comment=69454#post-id-69454It 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.Fri, 06 May 2016 21:21:12 +0200https://ask.libreoffice.org/en/question/69419/why-does-calc-get-confused-by-setformulamax453/?comment=69454#post-id-69454