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