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

Correction: RANK() does not have this problem however I cannot use it without 
making a copy of the data that I'm attempting to rank since it is actually in 
several disjoint ranges and each call to RANK() must find the value or else an 
error is reported. COUNTIF() does not have this restriction.

--On Wednesday, May 29, 2013 08:49:23 PM -0700 "James E. Lang" 
<jim+lou@lang.hm> 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? How can I work around this problem? I
> have also tried to use the function call RANK(K3, $K$3:$K$44) with similarly
> inconsistent results.
>
> My tests have shown that many pairs of values in E3 and F3 that result in a
> different value in K3 avoid the problem. For example, replacing the values in
> E3 and F3 with 102.89 in both cells produces the identical result but using
> 49.1401 in E3 and/or 54.1401 in F3 avoids the 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 using this format code:
>
> [$$-409]#,##0.00"["00"]";[RED]-[$$-409]#,##0.00"["00"]"
>
> --
> Jim

--
Jim

--
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