Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index


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

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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.