Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenTue, 08 Jul 2014 12:35:27 +0200LibreOffice Calc: Error 510, what's wrong with this formula?https://ask.libreoffice.org/en/question/36628/libreoffice-calc-error-510-whats-wrong-with-this-formula/Hi guys,
Currently trying to categorise some data based upon whether or not it is greater than or equal to the value in column F.
This is the formula I'm using
=COUNTIFS($A$2:$A$407,>F1,$A$2:$A$407,<=F2)
So basically I'm aiming to count all occurrences of values greater than the figure in F1 and less than or equal to the figure in F2.
However it's not working and I get error 510 every time.
It does however work if I replace `<F1` and `<=F2` with `"<0"` and `"<=365"` which are the values in F1 and F2. As you can probably see, I'm working with length of time here and the values in column F are the number of days in 1 year, 2 years e.t.c. It's a problem that's pretty easy to workaround, just by placing the number in the formula as opposed to linking to the cell, but why it's not working is a little beyond me.
I can imagine there's going to be something remarkably obvious I'm missing...
All help appreciated :)
Thanks
(Using LibreOffice 4.2.4.2 on Ubuntu 14.04)Tue, 08 Jul 2014 09:08:26 +0200https://ask.libreoffice.org/en/question/36628/libreoffice-calc-error-510-whats-wrong-with-this-formula/Comment by oweng for <p>Hi guys,
Currently trying to categorise some data based upon whether or not it is greater than or equal to the value in column F.</p>
<p>This is the formula I'm using</p>
<pre><code>=COUNTIFS($A$2:$A$407,>F1,$A$2:$A$407,<=F2)
</code></pre>
<p>So basically I'm aiming to count all occurrences of values greater than the figure in F1 and less than or equal to the figure in F2.</p>
<p>However it's not working and I get error 510 every time.</p>
<p>It does however work if I replace <code><F1</code> and <code><=F2</code> with <code>"<0"</code> and <code>"<=365"</code> which are the values in F1 and F2. As you can probably see, I'm working with length of time here and the values in column F are the number of days in 1 year, 2 years e.t.c. It's a problem that's pretty easy to workaround, just by placing the number in the formula as opposed to linking to the cell, but why it's not working is a little beyond me.</p>
<p>I can imagine there's going to be something remarkably obvious I'm missing...</p>
<p>All help appreciated :)
Thanks</p>
<p>(Using LibreOffice 4.2.4.2 on Ubuntu 14.04)</p>
https://ask.libreoffice.org/en/question/36628/libreoffice-calc-error-510-whats-wrong-with-this-formula/?comment=36640#post-id-36640@Regina, I have fixed the formatting.Tue, 08 Jul 2014 12:35:27 +0200https://ask.libreoffice.org/en/question/36628/libreoffice-calc-error-510-whats-wrong-with-this-formula/?comment=36640#post-id-36640Comment by Regina for <p>Hi guys,
Currently trying to categorise some data based upon whether or not it is greater than or equal to the value in column F.</p>
<p>This is the formula I'm using</p>
<pre><code>=COUNTIFS($A$2:$A$407,>F1,$A$2:$A$407,<=F2)
</code></pre>
<p>So basically I'm aiming to count all occurrences of values greater than the figure in F1 and less than or equal to the figure in F2.</p>
<p>However it's not working and I get error 510 every time.</p>
<p>It does however work if I replace <code><F1</code> and <code><=F2</code> with <code>"<0"</code> and <code>"<=365"</code> which are the values in F1 and F2. As you can probably see, I'm working with length of time here and the values in column F are the number of days in 1 year, 2 years e.t.c. It's a problem that's pretty easy to workaround, just by placing the number in the formula as opposed to linking to the cell, but why it's not working is a little beyond me.</p>
<p>I can imagine there's going to be something remarkably obvious I'm missing...</p>
<p>All help appreciated :)
Thanks</p>
<p>(Using LibreOffice 4.2.4.2 on Ubuntu 14.04)</p>
https://ask.libreoffice.org/en/question/36628/libreoffice-calc-error-510-whats-wrong-with-this-formula/?comment=36638#post-id-36638Your text after "It does ..." is unreadable for me. Try to write a grave accent before and after a formula to hide special characters like `<` to be interpreted as html tag, or write the formula into a separate line using the 1010-button of the editor, for example.Tue, 08 Jul 2014 12:09:32 +0200https://ask.libreoffice.org/en/question/36628/libreoffice-calc-error-510-whats-wrong-with-this-formula/?comment=36638#post-id-36638Answer by Regina for <p>Hi guys,
Currently trying to categorise some data based upon whether or not it is greater than or equal to the value in column F.</p>
<p>This is the formula I'm using</p>
<pre><code>=COUNTIFS($A$2:$A$407,>F1,$A$2:$A$407,<=F2)
</code></pre>
<p>So basically I'm aiming to count all occurrences of values greater than the figure in F1 and less than or equal to the figure in F2.</p>
<p>However it's not working and I get error 510 every time.</p>
<p>It does however work if I replace <code><F1</code> and <code><=F2</code> with <code>"<0"</code> and <code>"<=365"</code> which are the values in F1 and F2. As you can probably see, I'm working with length of time here and the values in column F are the number of days in 1 year, 2 years e.t.c. It's a problem that's pretty easy to workaround, just by placing the number in the formula as opposed to linking to the cell, but why it's not working is a little beyond me.</p>
<p>I can imagine there's going to be something remarkably obvious I'm missing...</p>
<p>All help appreciated :)
Thanks</p>
<p>(Using LibreOffice 4.2.4.2 on Ubuntu 14.04)</p>
https://ask.libreoffice.org/en/question/36628/libreoffice-calc-error-510-whats-wrong-with-this-formula/?answer=36636#post-id-36636A criterion has the data type "string". Only expressions, which result in a single number value are converted automatically.
Therefore you need to write `=COUNTIFS($A$2:$A$407;">"&F1;$A$2:$A$407;"<="&F2)` (my parameter delimiter is `;`). The number values in F1 and F2 are automatically converted to string when they a concatenated to a string using `&`. The application help has some examples with correct syntax in the help for COUNTIF.Tue, 08 Jul 2014 12:00:56 +0200https://ask.libreoffice.org/en/question/36628/libreoffice-calc-error-510-whats-wrong-with-this-formula/?answer=36636#post-id-36636