2013 Archives by date, by thread · List index

# Re: [libreoffice-users] countif() problem in calc

```At 20:49 29/05/2013 -0700, James E. Lang wrote:
```
I am having a problem with a formula in Calc. The formula includes a call to function COUNTIF() which is miscounting in one case. The problem appears to be very sensitive to the data value in cell K3 as shown below.
```
Cell E3 contains the value 49.14
Cell F3 contains the value 54.14
Cell I3 contains the formula =F3-E3 which evaluates to 5
Cell J3 contains the formula =4*(E3-3.5)/43+1 which evaluates to 5.2456...
Cell K3 contains the formula =I3+J3 which evaluates to 10.2456...
```
Cell M3 contains the function call COUNTIF(\$K\$3:\$K\$44,">"&K3) which is evaluated as being 1
```\$K\$4:\$K\$44 are empty cells

Why is the value in M3 1 rather than 0?
```
```
```
When you divide by 43 in J3, you create a fraction which (as you indicate) goes on further than the four factional places that you want and that you display. When you do the comparison in your COUNTIF(), you first concatenate the ">" sign with K3. This requires an implicit conversion of the value in K3 from number to text. The COUNTIF() function then interprets the formula, and - in order to carry out the comparisons - presumably converts the text after the ">" sign back to a number. In order that the value in K3 should be seen to be not greater than itself, you are relying on this two-way conversion producing an exactly similar binary result - down to the last binary digit stored by and calculated with by your system. That's asking a bit much - evidently too much: occasionally rounding will produce slightly different results and the two versions of what is in K3 may give a positive indication to COUNTIF().
```
```
```How can I work around this problem?
```
FWIW, values in E3, F3, and I3 are formatted as standard currency while the values in J3 and K3 are formatted as currency with tenths and hundredths of a cent shown enclosed in square brackets ...
```
```
Easy, I think. Go to Tools | Options... | LibreOffice Calc | Calculate, and tick "Precision as shown". Your value in J3 will still have excess fractional places, but the calculation in K3 will use the values *as displayed* in I3 and J3, so will be limited to hundredths of a cent. That way, the comparison should be more reliable.
```
```
An alternative may be to modify your formula in J3 to round the value to hundredths of a cent explicitly.
```
I trust this helps.

Brian Barker

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
```