GNOME.org
 

[gnumeric] Add PERCENTRANK.EXC

commit dc0a9a8cdea783d4217976a4b90df62e0bd0f3df
Author: Andreas J. Guelzow <aguelzow pyrshep ca>
Date:   Mon Jun 15 20:08:35 2015 -0600

    Add PERCENTRANK.EXC
    
    2015-06-15  Andreas J. Guelzow <aguelzow pyrshep ca>
    
        * functions.c (help_percentrank_exc): new
        (gnumeric_percentrank_exc): new
        (stat_functions): connect the above
        * plugin.xml.in: add PERCENTRANK.EXC
    
    2015-06-15  Andreas J. Guelzow <aguelzow pyrshep ca>
    
        * openoffice-read.c (oo_func_map_in): Add PERCENTRANK.EXC and
        RANK.AVG
        * openoffice-write.c (odf_expr_func_handler): Export PERCENTRANK.EXC
        and RANK.AVG with Microsoft prefix

 NEWS                                  |    2 +-
 plugins/fn-stat/ChangeLog             |    7 ++
 plugins/fn-stat/functions.c           |  104 ++++++++++++++++++++++++++++++++-
 plugins/fn-stat/plugin.xml.in         |    1 +
 plugins/openoffice/ChangeLog          |    7 ++
 plugins/openoffice/openoffice-read.c  |    2 +
 plugins/openoffice/openoffice-write.c |    6 +-
 7 files changed, 125 insertions(+), 4 deletions(-)
---
diff --git a/NEWS b/NEWS
index 5d9eb7d..08922bb 100644
--- a/NEWS
+++ b/NEWS
@@ -4,7 +4,7 @@ Andreas:
        * Fix memory handling error on fuzzed sxc file. [#748535]
        * Improve import/export of page layout from/to ODF.
        * Improve function import to ODF. [#750627]
-       * Add CONFIDENCE.T, PERCENTILE.EXC and QUARTILE.EXC.
+       * Add CONFIDENCE.T, PERCENTILE.EXC, PERCENTRANK.EXC and QUARTILE.EXC.
 
 Jean:
        * Fix xlsx import of plot area manual layout. [#748016]
diff --git a/plugins/fn-stat/ChangeLog b/plugins/fn-stat/ChangeLog
index 2a1ac23..d2475e2 100644
--- a/plugins/fn-stat/ChangeLog
+++ b/plugins/fn-stat/ChangeLog
@@ -1,3 +1,10 @@
+2015-06-15  Andreas J. Guelzow <aguelzow pyrshep ca>
+
+       * functions.c (help_percentrank_exc): new
+       (gnumeric_percentrank_exc): new
+       (stat_functions): connect the above
+       * plugin.xml.in: add PERCENTRANK.EXC
+
 2015-06-14  Andreas J. Guelzow <aguelzow pyrshep ca>
 
        * functions.c (help_percentile_exc): new
diff --git a/plugins/fn-stat/functions.c b/plugins/fn-stat/functions.c
index ecd4e5b..96eb246 100644
--- a/plugins/fn-stat/functions.c
+++ b/plugins/fn-stat/functions.c
@@ -2731,7 +2731,7 @@ gnumeric_stdevpa (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
 /***************************************************************************/
 
 static GnmFuncHelp const help_percentrank[] = {
-       { GNM_FUNC_HELP_NAME, F_("PERCENTRANK:rank of a data point in a data set")},
+       { GNM_FUNC_HELP_NAME, F_("PERCENTRANK:rank of a data point in a data set (Hyndman-Fan method 7: N-1 
basis)")},
        { GNM_FUNC_HELP_ARG, F_("array:range of numeric values")},
        { GNM_FUNC_HELP_ARG, F_("x:data point to be ranked")},
        { GNM_FUNC_HELP_ARG, F_("significance:number of significant digits, defaults to 3")},
@@ -2827,6 +2827,105 @@ gnumeric_percentrank (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
 
 /***************************************************************************/
 
+
+
+static GnmFuncHelp const help_percentrank_exc[] = {
+       { GNM_FUNC_HELP_NAME, F_("PERCENTRANK_EXC:rank of a data point in a data set (Hyndman-Fan method 6: 
N+1 basis)")},
+       { GNM_FUNC_HELP_ARG, F_("array:range of numeric values")},
+       { GNM_FUNC_HELP_ARG, F_("x:data point to be ranked")},
+       { GNM_FUNC_HELP_ARG, F_("significance:number of significant digits, defaults to 3")},
+       { GNM_FUNC_HELP_NOTE, F_("If @{array} contains no data points, this function returns a #NUM! "
+                                "error.") },
+       { GNM_FUNC_HELP_NOTE, F_("If @{significance} is less than one, this function returns a #NUM! "
+                                "error.") },
+       { GNM_FUNC_HELP_NOTE, F_("If @{x} exceeds the largest value or is less than the smallest "
+                                "value in @{array}, this function returns a #NUM! error.") },
+       { GNM_FUNC_HELP_NOTE, F_("If @{x} does not match any of the values in @{array} or @{x} matches "
+                                "more than once, this function interpolates the returned value.") },
+       { GNM_FUNC_HELP_SEEALSO, 
"LARGE,MAX,MEDIAN,MIN,PERCENTILE,PERCENTILE.EXC,QUARTILE,QUARTILE.EXC,SMALL"},
+       { GNM_FUNC_HELP_END }
+};
+
+static GnmValue *
+gnumeric_percentrank_exc (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
+{
+       gnm_float *data, x, significance, r;
+       GnmValue *result = NULL;
+       int i, n;
+       int n_equal, n_smaller, n_larger;
+       gnm_float x_larger, x_smaller;
+
+       data = collect_floats_value (argv[0], ei->pos,
+                                    COLLECT_IGNORE_STRINGS |
+                                    COLLECT_IGNORE_BOOLS |
+                                    COLLECT_IGNORE_BLANKS |
+                                    COLLECT_ORDER_IRRELEVANT,
+                                    &n, &result);
+       x = value_get_as_float (argv[1]);
+       significance = argv[2] ? value_get_as_float (argv[2]) : 3;
+
+       if (result)
+               goto done;
+
+       n_equal = n_smaller = n_larger = 0;
+       x_larger = x_smaller = 42;
+       for (i = 0; i < n; i++) {
+               gnm_float y = data[i];
+
+               if (y < x) {
+                       if (n_smaller == 0 || x_smaller < y)
+                               x_smaller = y;
+                       n_smaller++;
+               } else if (y > x) {
+                       if (n_larger == 0 || x_larger > y)
+                               x_larger = y;
+                       n_larger++;
+               } else
+                       n_equal++;
+       }
+
+       if (n_smaller + n_equal == 0 || n_larger + n_equal == 0) {
+               result = value_new_error_NA (ei->pos);
+               goto done;
+       }
+
+       if (n == 1)
+               r = 1;
+       else {
+               gnm_float s10;
+
+               /* A strange place to check, but n==1 is special.  */
+               if (significance < 0) {
+                       result = value_new_error_NUM (ei->pos);
+                       goto done;
+               }
+               s10 = gnm_pow10 (-significance);
+               if (s10 <= 0) {
+                       result = value_new_error_DIV0 (ei->pos);
+                       goto done;
+               }
+
+
+               if (n_equal > 0)
+                       r = (n_smaller + 1) / (gnm_float)(n + 1);
+               else {
+                       gnm_float r1 = n_smaller / (gnm_float)(n + 1);
+                       gnm_float r2 = (n_smaller + 1) / (gnm_float)(n + 1);
+                       r = (r1 * (x_larger - x) +
+                            r2 * (x - x_smaller)) / (x_larger - x_smaller);
+               }
+
+               r = gnm_fake_trunc (r / s10) * s10;
+       }
+       result = value_new_float (r);
+
+ done:
+       g_free (data);
+       return result;
+}
+
+/***************************************************************************/
+
 static GnmFuncHelp const help_percentile[] = {
        { GNM_FUNC_HELP_NAME, F_("PERCENTILE:determines the 100* {k}-th percentile of the given data points 
(Hyndman-Fan method 7: N-1 basis)")},
        { GNM_FUNC_HELP_ARG, F_("array:data points")},
@@ -5329,6 +5428,9 @@ GnmFuncDescriptor const stat_functions[] = {
        { "percentrank",  "Af|f",
          help_percentrank, gnumeric_percentrank, NULL, NULL, NULL,
          GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
+       { "percentrank.exc",  "Af|f",
+         help_percentrank_exc, gnumeric_percentrank_exc, NULL, NULL, NULL,
+         GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE},
        { "permut",       "ff",
          help_permut, gnumeric_permut, NULL, NULL, NULL,
          GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
diff --git a/plugins/fn-stat/plugin.xml.in b/plugins/fn-stat/plugin.xml.in
index ae87cf5..da4bd2a 100644
--- a/plugins/fn-stat/plugin.xml.in
+++ b/plugins/fn-stat/plugin.xml.in
@@ -85,6 +85,7 @@
                                <function name="percentile"/>
                                <function name="percentile.exc"/>
                                <function name="percentrank"/>
+                               <function name="percentrank.exc"/>
                                <function name="permut"/>
                                <function name="permutationa"/>
                                <function name="poisson"/>
diff --git a/plugins/openoffice/ChangeLog b/plugins/openoffice/ChangeLog
index c952a47..adcf3a1 100644
--- a/plugins/openoffice/ChangeLog
+++ b/plugins/openoffice/ChangeLog
@@ -1,3 +1,10 @@
+2015-06-15  Andreas J. Guelzow <aguelzow pyrshep ca>
+
+       * openoffice-read.c (oo_func_map_in): Add PERCENTRANK.EXC and
+       RANK.AVG
+       * openoffice-write.c (odf_expr_func_handler): Export PERCENTRANK.EXC
+       and RANK.AVG with Microsoft prefix
+
 2015-06-14  Andreas J. Guelzow <aguelzow pyrshep ca>
 
        * openoffice-read.c (oo_func_map_in): Add PERCENTILE.EXC
diff --git a/plugins/openoffice/openoffice-read.c b/plugins/openoffice/openoffice-read.c
index 0020b31..6037808 100644
--- a/plugins/openoffice/openoffice-read.c
+++ b/plugins/openoffice/openoffice-read.c
@@ -13210,10 +13210,12 @@ oo_func_map_in (GnmConventions const *convs, Workbook *scope,
                { "PERCENTILE.INC","PERCENTILE" },
                { "PERCENTILE.EXC","PERCENTILE.EXC" },
                { "PERCENTRANK.INC","PERCENTRANK" },
+               { "PERCENTRANK.EXC","PERCENTRANK.EXC" },
                { "POISSON.DIST","POISSON" },
                { "QUARTILE.INC","QUARTILE" },
                { "QUARTILE.EXC","QUARTILE.EXC" },
                { "RANK.EQ","RANK" },
+               { "RANK.AVG","RANK.AVG" },
                { "STDEV.S","STDEV" },
                { "STDEV.P","STDEVP" },
                { "T.INV","R.QT" },
diff --git a/plugins/openoffice/openoffice-write.c b/plugins/openoffice/openoffice-write.c
index 0591ab7..62bb1a1 100644
--- a/plugins/openoffice/openoffice-write.c
+++ b/plugins/openoffice/openoffice-write.c
@@ -2266,8 +2266,8 @@ odf_expr_func_handler (GnmConventionsOut *out, GnmExprFunction const *func)
                { "CLEAN","CLEAN" },
                { "CODE","CODE" },
                { "COLUMN","COLUMN" },
-               { "COLUMNS","COLUMNS" }
-,              { "CONFIDENCE.T","COM.MICROSOFT.CONFIDENCE.T" },
+               { "COLUMNS","COLUMNS" },
+               { "CONFIDENCE.T","COM.MICROSOFT.CONFIDENCE.T" },
                { "COMBIN","COMBIN" },
                { "COMBINA","COMBINA" },
                { "COMPLEX","COMPLEX" },
@@ -2494,6 +2494,7 @@ odf_expr_func_handler (GnmConventionsOut *out, GnmExprFunction const *func)
                { "PERCENTILE","PERCENTILE" },
                { "PERCENTILE.EXC","COM.MICROSOFT.PERCENTILE.EXC" },
                { "PERCENTRANK","PERCENTRANK" },
+               { "PERCENTRANK.EXC","COM.MICROSOFT.PERCENTRANK.EXC" },
                { "PERMUT","PERMUT" },
                { "PERMUTATIONA","PERMUTATIONA" },
                /* { "PHI","PHI" },  converted to NORMDIST on import */
@@ -2516,6 +2517,7 @@ odf_expr_func_handler (GnmConventionsOut *out, GnmExprFunction const *func)
                { "RAND","RAND" },
                { "RANDBETWEEN","RANDBETWEEN" },
                { "RANK","RANK" },
+               { "RANK.AVG","COM.MICROSOFT.RANK.AVG" },
                { "RATE","RATE" },
                { "RECEIVED","RECEIVED" },
                { "REPLACE","REPLACE" },
[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]