?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
GnmFunc: make this a GObject.
[gnumeric.git] / plugins / fn-stat / functions.c
blob945f2f30b7297382d3749d774dc9d1ae2596010e
1 /*
2 * fn-stat.c: Built in statistical functions and functions registration
3 *
4 * Authors:
5 * Jukka-Pekka Iivonen <jiivonenhutcs.cs.hut.fi>
6 * Michael Meeks <micheal@ximian.com>
7 * Morten Welinder <terra@gnome.org>
8 *
9 * This program is free software; you can redistribute it and/or modify
10 * it under the terms of the GNU General Public License as published by
11 * the Free Software Foundation; either version 2 of the License, or
12 * (at your option) any later version.
13 *
14 * This program is distributed in the hope that it will be useful,
15 * but WITHOUT ANY WARRANTY; without even the implied warranty of
16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 * GNU General Public License for more details.
18 *
19 * You should have received a copy of the GNU General Public License
20 * along with this program; if not, see <https://www.gnu.org/licenses/>.
21 */
22 #include <gnumeric-config.h>
23 #include <gnumeric.h>
24 #include <func.h>
25 #include <mathfunc.h>
26 #include <sf-gamma.h>
27 #include <sf-dpq.h>
28 #include <rangefunc.h>
29 #include <regression.h>
30 #include <sheet.h>
31 #include <collect.h>
32 #include <gutils.h>
33 #include <value.h>
34 #include <expr.h>
35 #include <gnm-i18n.h>
36 #include <goffice/goffice.h>
37 #include <gnm-plugin.h>
38 #include <math.h>
39 #include <stdlib.h>
40 #include <string.h>
41 #include <tools/analysis-tools.h>
42
43 GNM_PLUGIN_MODULE_HEADER;
44
45 #define HELP_DESCRIPTION_TEXT_INCLUSION { GNM_FUNC_HELP_DESCRIPTION, F_("Numbers, text and logical values are included in the calculation too. If the cell contains text or the argument evaluates to FALSE, it is counted as value zero (0). If the argument evaluates to TRUE, it is counted as one (1). Note that empty cells are not counted.")}
46
47 /***************************************************************************/
48
49 static GnmFuncHelp const help_varp[] = {
50 { GNM_FUNC_HELP_NAME, F_("VARP:variance of an entire population")},
51 { GNM_FUNC_HELP_ARG, F_("area1:first cell area")},
52 { GNM_FUNC_HELP_ARG, F_("area2:second cell area")},
53 { GNM_FUNC_HELP_DESCRIPTION, F_("VARP is also known as the N-variance.")},
54 { GNM_FUNC_HELP_EXAMPLES, "=VARP(11.4,17.3,21.3,25.9,40.1)" },
55 { GNM_FUNC_HELP_SEEALSO, ("AVERAGE,DVAR,DVARP,STDEV,VAR")},
56 { GNM_FUNC_HELP_EXTREF, F_("wiki:en:Variance") },
57 { GNM_FUNC_HELP_EXTREF, F_("wolfram:Variance.html") },
58 { GNM_FUNC_HELP_END }
59 };
60
61 static GnmValue *
62 gnumeric_varp (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
63 {
64 return float_range_function (argc, argv, ei,
65 gnm_range_var_pop,
66 COLLECT_IGNORE_STRINGS |
67 COLLECT_IGNORE_BOOLS |
68 COLLECT_IGNORE_BLANKS,
69 GNM_ERROR_DIV0);
70 }
71
72 /***************************************************************************/
73
74 static GnmFuncHelp const help_var[] = {
75 { GNM_FUNC_HELP_NAME, F_("VAR:sample variance of the given sample")},
76 { GNM_FUNC_HELP_ARG, F_("area1:first cell area")},
77 { GNM_FUNC_HELP_ARG, F_("area2:second cell area")},
78 { GNM_FUNC_HELP_DESCRIPTION, F_("VAR is also known as the N-1-variance.")},
79 { GNM_FUNC_HELP_NOTE, F_("Since the N-1-variance includes Bessel's correction, whereas the N-variance calculated by VARPA or VARP does not, "
80 "under reasonable conditions the N-1-variance is an unbiased estimator of the variance of the population "
81 "from which the sample is drawn.")},
82 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.")},
83 { GNM_FUNC_HELP_EXAMPLES, "=VAR(11.4,17.3,21.3,25.9,40.1)" },
84 { GNM_FUNC_HELP_SEEALSO, ("VARP,STDEV,VARA")},
85 { GNM_FUNC_HELP_EXTREF, F_("wiki:en:Variance") },
86 { GNM_FUNC_HELP_EXTREF, F_("wolfram:Variance.html") },
87 { GNM_FUNC_HELP_END }
88 };
89
90 static GnmValue *
91 gnumeric_var (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
92 {
93 return float_range_function (argc, argv, ei,
94 gnm_range_var_est,
95 COLLECT_IGNORE_STRINGS |
96 COLLECT_IGNORE_BOOLS |
97 COLLECT_IGNORE_BLANKS,
98 GNM_ERROR_DIV0);
99 }
100
101 /***************************************************************************/
102
103 static GnmFuncHelp const help_stdev[] = {
104 { GNM_FUNC_HELP_NAME, F_("STDEV:sample standard deviation of the given sample")},
105 { GNM_FUNC_HELP_ARG, F_("area1:first cell area")},
106 { GNM_FUNC_HELP_ARG, F_("area2:second cell area")},
107 { GNM_FUNC_HELP_DESCRIPTION, F_("STDEV is also known as the N-1-standard deviation.")},
108 { GNM_FUNC_HELP_DESCRIPTION, F_("To obtain the population standard deviation of a whole population use STDEVP.")},
109 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.")},
110 { GNM_FUNC_HELP_EXAMPLES, "=STDEV(11.4,17.3,21.3,25.9,40.1)" },
111 { GNM_FUNC_HELP_SEEALSO, ("AVERAGE,DSTDEV,DSTDEVP,STDEVA,STDEVPA,VAR")},
112 { GNM_FUNC_HELP_EXTREF, F_("wiki:en:Standard_deviation") },
113 { GNM_FUNC_HELP_EXTREF, F_("wolfram:StandardDeviation.html") },
114 { GNM_FUNC_HELP_END }
115 };
116
117 static GnmValue *
118 gnumeric_stdev (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
119 {
120 return float_range_function (argc, argv, ei,
121 gnm_range_stddev_est,
122 COLLECT_IGNORE_STRINGS |
123 COLLECT_IGNORE_BOOLS |
124 COLLECT_IGNORE_BLANKS,
125 GNM_ERROR_DIV0);
126 }
127
128 /***************************************************************************/
129
130 static GnmFuncHelp const help_stdevp[] = {
131 { GNM_FUNC_HELP_NAME, F_("STDEVP:population standard deviation of the given population")},
132 { GNM_FUNC_HELP_ARG, F_("area1:first cell area")},
133 { GNM_FUNC_HELP_ARG, F_("area2:second cell area")},
134 { GNM_FUNC_HELP_DESCRIPTION, F_("This is also known as the N-standard deviation")},
135 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.")},
136 { GNM_FUNC_HELP_EXAMPLES, "=STDEVP(11.4,17.3,21.3,25.9,40.1)" },
137 { GNM_FUNC_HELP_SEEALSO, ("STDEV,STDEVA,STDEVPA")},
138 { GNM_FUNC_HELP_EXTREF, F_("wiki:en:Standard_deviation") },
139 { GNM_FUNC_HELP_EXTREF, F_("wolfram:StandardDeviation.html") },
140 { GNM_FUNC_HELP_END }
141 };
142
143 static GnmValue *
144 gnumeric_stdevp (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
145 {
146 return float_range_function (argc, argv, ei,
147 gnm_range_stddev_pop,
148 COLLECT_IGNORE_STRINGS |
149 COLLECT_IGNORE_BOOLS |
150 COLLECT_IGNORE_BLANKS,
151 GNM_ERROR_DIV0);
152 }
153
154 /***************************************************************************/
155
156 static GnmFuncHelp const help_rank[] = {
157 { GNM_FUNC_HELP_NAME, F_("RANK:rank of a number in a list of numbers")},
158 { GNM_FUNC_HELP_ARG, F_("x:number whose rank you want to find")},
159 { GNM_FUNC_HELP_ARG, F_("ref:list of numbers")},
160 { GNM_FUNC_HELP_ARG, F_("order:0 (descending order) or non-zero (ascending order); defaults to 0")},
161 { GNM_FUNC_HELP_NOTE, F_("In case of a tie, RANK returns the largest possible rank.")},
162 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.")},
163 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, \xe2\x80\xa6, A5 contain numbers 11.4, 17.3, 21.3, 25.9, and 25.9.")},
164 { GNM_FUNC_HELP_EXAMPLES, F_("Then RANK(17.3,A1:A5) equals 4.")},
165 { GNM_FUNC_HELP_EXAMPLES, F_("Then RANK(25.9,A1:A5) equals 1.")},
166 { GNM_FUNC_HELP_SEEALSO, ("PERCENTRANK,RANK.AVG")},
167 { GNM_FUNC_HELP_END }
168 };
169
170 static GnmValue *
171 gnumeric_rank (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
172 {
173 gnm_float *xs;
174 int i, r, n;
175 GnmValue *result = NULL;
176 gnm_float x;
177 gboolean increasing;
178
179 x = value_get_as_float (argv[0]);
180 xs = collect_floats_value (argv[1], ei->pos,
181 COLLECT_IGNORE_STRINGS |
182 COLLECT_IGNORE_BOOLS |
183 COLLECT_IGNORE_BLANKS |
184 COLLECT_ORDER_IRRELEVANT,
185 &n, &result);
186 increasing = argv[2] ? value_get_as_checked_bool (argv[2]) : FALSE;
187
188 if (result)
189 goto out;
190
191 for (i = 0, r = 1; i < n; i++) {
192 gnm_float y = xs[i];
193
194 if (increasing ? y < x : y > x)
195 r++;
196 }
197
198 result = value_new_int (r);
199
200 out:
201 g_free (xs);
202
203 return result;
204 }
205
206 /***************************************************************************/
207
208 static GnmFuncHelp const help_rank_avg[] = {
209 { GNM_FUNC_HELP_NAME, F_("RANK.AVG:rank of a number in a list of numbers")},
210 { GNM_FUNC_HELP_ARG, F_("x:number whose rank you want to find")},
211 { GNM_FUNC_HELP_ARG, F_("ref:list of numbers")},
212 { GNM_FUNC_HELP_ARG, F_("order:0 (descending order) or non-zero (ascending order); defaults to 0")},
213 { GNM_FUNC_HELP_NOTE, F_("In case of a tie, RANK.AVG returns the average rank.")},
214 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel 2010 compatible.")},
215 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5 contain numbers 11.4, 17.3, 21.3, 25.9, and 25.9.")},
216 { GNM_FUNC_HELP_EXAMPLES, F_("Then RANK.AVG(17.3,A1:A5) equals 4.")},
217 { GNM_FUNC_HELP_EXAMPLES, F_("Then RANK.AVG(25.9,A1:A5) equals 1.5.")},
218 { GNM_FUNC_HELP_SEEALSO, ("PERCENTRANK,RANK")},
219 { GNM_FUNC_HELP_END }
220 };
221
222 static GnmValue *
223 gnumeric_rank_avg (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
224 {
225 gnm_float *xs;
226 int i, r, n, t;
227 GnmValue *result = NULL;
228 gnm_float x;
229 gboolean increasing;
230
231 x = value_get_as_float (argv[0]);
232 xs = collect_floats_value (argv[1], ei->pos,
233 COLLECT_IGNORE_STRINGS |
234 COLLECT_IGNORE_BOOLS |
235 COLLECT_IGNORE_BLANKS |
236 COLLECT_ORDER_IRRELEVANT,
237 &n, &result);
238 increasing = argv[2] ? value_get_as_checked_bool (argv[2]) : FALSE;
239
240 if (result)
241 goto out;
242
243 for (i = 0, r = 1, t = 0; i < n; i++) {
244 gnm_float y = xs[i];
245
246 if (increasing ? y < x : y > x)
247 r++;
248 if (x == y)
249 t++;
250 }
251
252 if (t > 1)
253 result = value_new_float (r + (t - 1)/2.);
254 else
255 result = value_new_int (r);
256
257 out:
258 g_free (xs);
259
260 return result;
261 }
262
263 /***************************************************************************/
264
265 static GnmFuncHelp const help_trimmean[] = {
266
267 { GNM_FUNC_HELP_NAME, F_("TRIMMEAN:mean of the interior of a data set")},
268 { GNM_FUNC_HELP_ARG, F_("ref:list of numbers whose mean you want to calculate")},
269 { GNM_FUNC_HELP_ARG, F_("fraction:fraction of the data set excluded from the mean")},
270 { GNM_FUNC_HELP_DESCRIPTION, F_("If @{fraction}=0.2 and the data set contains 40 numbers, 8 numbers are trimmed from the data set (40 x 0.2): "
271 "the 4 largest and the 4 smallest. To avoid a bias, the number of points to be excluded is always rounded down to the nearest even number.")},
272 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.")},
273 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5 contain numbers 11.4, 17.3, 21.3, 25.9, and 40.1.")},
274 { GNM_FUNC_HELP_EXAMPLES, F_("Then TRIMMEAN(A1:A5,0.2) equals 23.2 and TRIMMEAN(A1:A5,0.4) equals 21.5.")},
275 { GNM_FUNC_HELP_SEEALSO, ("AVERAGE,GEOMEAN,HARMEAN,MEDIAN,MODE")},
276 { GNM_FUNC_HELP_END }
277 };
278
279 static GnmValue *
280 gnumeric_trimmean (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
281 {
282 int c, tc, n;
283 GnmValue *result = NULL;
284 gnm_float *xs = collect_floats_value (argv[0], ei->pos,
285 COLLECT_IGNORE_STRINGS |
286 COLLECT_IGNORE_BOOLS |
287 COLLECT_IGNORE_BLANKS |
288 COLLECT_SORT,
289 &n, &result);
290 gnm_float p = value_get_as_float (argv[1]);
291 gnm_float res;
292
293 if (result)
294 goto out;
295
296 if (p < 0 || p >= 1) {
297 result = value_new_error_NUM (ei->pos);
298 goto out;
299 }
300
301 tc = (int)gnm_fake_floor ((n * p) / 2);
302 c = n - 2 * tc;
303 if (gnm_range_average (xs + tc, c, &res))
304 result = value_new_error_VALUE (ei->pos);
305 else
306 result = value_new_float (res);
307
308 g_free (xs);
309 out:
310 return result;
311 }
312
313 /***************************************************************************/
314
315 static GnmFuncHelp const help_covar[] = {
316 { GNM_FUNC_HELP_NAME, F_("COVAR:covariance of two data sets")},
317 { GNM_FUNC_HELP_ARG, F_("array1:first data set")},
318 { GNM_FUNC_HELP_ARG, F_("array2:set data set")},
319 { GNM_FUNC_HELP_DESCRIPTION, F_("Strings and empty cells are simply ignored.") },
320 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
321 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5 contain numbers "
322 "11.4, 17.3, 21.3, 25.9, and 40.1, and the cells B1, B2, ... "
323 "B5 23.2, 25.8, 29.9, 33.5, and 42.7.") },
324 { GNM_FUNC_HELP_EXAMPLES, F_("Then COVAR(A1:A5,B1:B5) equals 65.858.") },
325 { GNM_FUNC_HELP_SEEALSO, "CORREL,FISHER,FISHERINV"},
326 { GNM_FUNC_HELP_EXTREF, F_("wiki:en:Covariance") },
327 { GNM_FUNC_HELP_EXTREF, F_("wolfram:Covariance.html") },
328 { GNM_FUNC_HELP_END }
329 };
330
331 static GnmValue *
332 gnumeric_covar (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
333 {
334 return float_range_function2 (argv[0], argv[1],
335 ei,
336 gnm_range_covar_pop,
337 COLLECT_IGNORE_BLANKS |
338 COLLECT_IGNORE_STRINGS |
339 COLLECT_IGNORE_BOOLS,
340 GNM_ERROR_VALUE);
341 }
342
343 /***************************************************************************/
344
345 static GnmFuncHelp const help_covariance_s[] = {
346 { GNM_FUNC_HELP_NAME, F_("COVARIANCE.S:sample covariance of two data sets")},
347 { GNM_FUNC_HELP_ARG, F_("array1:first data set")},
348 { GNM_FUNC_HELP_ARG, F_("array2:set data set")},
349 { GNM_FUNC_HELP_DESCRIPTION, F_("Strings and empty cells are simply ignored.") },
350 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
351 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5 contain numbers "
352 "11.4, 17.3, 21.3, 25.9, and 40.1, and the cells B1, B2, ... "
353 "B5 23.2, 25.8, 29.9, 33.5, and 42.7.") },
354 { GNM_FUNC_HELP_EXAMPLES, F_("Then COVAR(A1:A5,B1:B5) equals 65.858.") },
355 { GNM_FUNC_HELP_SEEALSO, "COVAR,CORREL"},
356 { GNM_FUNC_HELP_EXTREF, F_("wiki:en:Covariance") },
357 { GNM_FUNC_HELP_EXTREF, F_("wolfram:Covariance.html") },
358 { GNM_FUNC_HELP_END }
359 };
360
361 static GnmValue *
362 gnumeric_covariance_s (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
363 {
364 return float_range_function2 (argv[0], argv[1],
365 ei,
366 gnm_range_covar_est,
367 COLLECT_IGNORE_BLANKS |
368 COLLECT_IGNORE_STRINGS |
369 COLLECT_IGNORE_BOOLS,
370 GNM_ERROR_VALUE);
371 }
372
373 /***************************************************************************/
374
375 static GnmFuncHelp const help_correl[] = {
376 { GNM_FUNC_HELP_NAME, F_("CORREL:Pearson correlation coefficient of two data sets")},
377 { GNM_FUNC_HELP_ARG, F_("array1:first data set")},
378 { GNM_FUNC_HELP_ARG, F_("array2:second data set")},
379 { GNM_FUNC_HELP_DESCRIPTION, F_("Strings and empty cells are simply ignored.")},
380 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
381 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5 contain numbers "
382 "11.4, 17.3, 21.3, 25.9, and 40.1, and the cells B1, B2, ... "
383 "B5 23.2, 25.8, 29.9, 33.5, and 42.7.") },
384 { GNM_FUNC_HELP_EXAMPLES, F_("Then CORREL(A1:A5,B1:B5) equals 0.996124788.") },
385 { GNM_FUNC_HELP_SEEALSO, "COVAR,FISHER,FISHERINV"},
386 { GNM_FUNC_HELP_EXTREF, F_("wolfram:CorrelationCoefficient.html") },
387 { GNM_FUNC_HELP_EXTREF, F_("wolfram:Covariance.html") },
388 { GNM_FUNC_HELP_END }
389 };
390
391 static GnmValue *
392 gnumeric_correl (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
393 {
394 return float_range_function2 (argv[0], argv[1],
395 ei,
396 gnm_range_correl_pop,
397 COLLECT_IGNORE_BLANKS |
398 COLLECT_IGNORE_STRINGS |
399 COLLECT_IGNORE_BOOLS,
400 GNM_ERROR_VALUE);
401 }
402
403 /***************************************************************************/
404
405 static GnmFuncHelp const help_negbinomdist[] = {
406 { GNM_FUNC_HELP_NAME, F_("NEGBINOMDIST:probability mass function of the negative binomial distribution")},
407 { GNM_FUNC_HELP_ARG, F_("f:number of failures")},
408 { GNM_FUNC_HELP_ARG, F_("t:threshold number of successes")},
409 { GNM_FUNC_HELP_ARG, F_("p:probability of a success")},
410 { GNM_FUNC_HELP_NOTE, F_("If @{f} or @{t} is a non-integer it is truncated.")},
411 { GNM_FUNC_HELP_NOTE, F_("If (@{f} + @{t} -1) <= 0 this function returns a #NUM! error.")},
412 { GNM_FUNC_HELP_NOTE, F_("If @{p} < 0 or @{p} > 1 this function returns a #NUM! error.")},
413 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
414 { GNM_FUNC_HELP_EXAMPLES, "=NEGBINOMDIST(2,5,0.55)" },
415 { GNM_FUNC_HELP_SEEALSO, "BINOMDIST,COMBIN,FACT,HYPGEOMDIST,PERMUT"},
416 { GNM_FUNC_HELP_END }
417 };
418
419 static GnmValue *
420 gnumeric_negbinomdist (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
421 {
422 gnm_float x = gnm_fake_floor (value_get_as_float (argv[0]));
423 gnm_float r = gnm_fake_floor (value_get_as_float (argv[1]));
424 gnm_float p = value_get_as_float (argv[2]);
425
426 if ((x + r - 1) <= 0 || p < 0 || p > 1)
427 return value_new_error_NUM (ei->pos);
428
429 return value_new_float (dnbinom (x, r, p, FALSE));
430 }
431
432 /***************************************************************************/
433
434 static GnmFuncHelp const help_normsdist[] = {
435 { GNM_FUNC_HELP_NAME, F_("NORMSDIST:cumulative distribution function of the standard normal distribution")},
436 { GNM_FUNC_HELP_ARG, F_("x:number")},
437 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
438 { GNM_FUNC_HELP_ODF, F_("NORMSDIST is the OpenFormula function LEGACY.NORMSDIST.") },
439 { GNM_FUNC_HELP_EXAMPLES, "=NORMSDIST(2)" },
440 { GNM_FUNC_HELP_SEEALSO, "NORMDIST"},
441 { GNM_FUNC_HELP_EXTREF, F_("wiki:en:Normal_distribution") },
442 { GNM_FUNC_HELP_EXTREF, F_("wolfram:NormalDistribution.html") },
443 { GNM_FUNC_HELP_END }
444 };
445
446 static GnmValue *
447 gnumeric_normsdist (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
448 {
449 gnm_float x = value_get_as_float (argv[0]);
450
451 return value_new_float (pnorm (x, 0, 1, TRUE, FALSE));
452 }
453
454 /***************************************************************************/
455
456 static GnmFuncHelp const help_snorm_dist_range[] = {
457 { GNM_FUNC_HELP_NAME, F_("SNORM.DIST.RANGE:probability of the standard normal distribution over an interval") },
458 { GNM_FUNC_HELP_ARG, F_("x1:start of the interval") },
459 { GNM_FUNC_HELP_ARG, F_("x2:end of the interval") },
460 { GNM_FUNC_HELP_DESCRIPTION, F_("This function returns the cumulative probability over a range of the standard normal distribution; that is the integral over the probability density function from @{x1} to @{x2}.") },
461 { GNM_FUNC_HELP_NOTE, F_("If @{x1}>@{x2}, this function returns a negative value.")},
462 { GNM_FUNC_HELP_SEEALSO, "NORMSDIST,R.PNORM,R.QNORM,R.DNORM"},
463 { GNM_FUNC_HELP_EXAMPLES, "=SNORM.DIST.RANGE(0.6,0.6+1e-10)" },
464 { GNM_FUNC_HELP_END }
465 };
466
467 static GnmValue *
468 gnumeric_snorm_dist_range (GnmFuncEvalInfo *ei, GnmValue const * const *args)
469 {
470 gnm_float x1 = value_get_as_float (args[0]);
471 gnm_float x2 = value_get_as_float (args[1]);
472
473 return value_new_float (pnorm2 (x1, x2));
474 }
475
476 /* ------------------------------------------------------------------------- */
477
478 static GnmFuncHelp const help_normsinv[] = {
479 { GNM_FUNC_HELP_NAME, F_("NORMSINV:inverse of the cumulative distribution function of the standard normal distribution")},
480 { GNM_FUNC_HELP_ARG, F_("p:given probability")},
481 { GNM_FUNC_HELP_NOTE, F_("If @{p} < 0 or @{p} > 1 this function returns #NUM! error.")},
482 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
483 { GNM_FUNC_HELP_ODF, F_("NORMSINV is the OpenFormula function LEGACY.NORMSINV.") },
484 { GNM_FUNC_HELP_EXAMPLES, "=NORMSINV(0.2)" },
485 { GNM_FUNC_HELP_SEEALSO, "NORMDIST,NORMINV,NORMSDIST,STANDARDIZE,ZTEST"},
486 { GNM_FUNC_HELP_EXTREF, F_("wiki:en:Normal_distribution") },
487 { GNM_FUNC_HELP_EXTREF, F_("wolfram:NormalDistribution.html") },
488 { GNM_FUNC_HELP_END }
489 };
490
491
492 static GnmValue *
493 gnumeric_normsinv (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
494 {
495 gnm_float p = value_get_as_float (argv[0]);
496
497 if (p < 0 || p > 1)
498 return value_new_error_NUM (ei->pos);
499
500 return value_new_float (qnorm (p, 0, 1, TRUE, FALSE));
501 }
502
503 /***************************************************************************/
504
505 static GnmFuncHelp const help_owent[] = {
506 { GNM_FUNC_HELP_NAME, F_("OWENT:Owen's T function")},
507 { GNM_FUNC_HELP_ARG, F_("h:number")},
508 { GNM_FUNC_HELP_ARG, F_("a:number")},
509 { GNM_FUNC_HELP_EXAMPLES, "=OWENT(0.1,11)" },
510 { GNM_FUNC_HELP_SEEALSO, "R.PSNORM,R.PST"},
511 { GNM_FUNC_HELP_END }
512 };
513
514 static GnmValue *
515 gnumeric_owent (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
516 {
517 gnm_float h = value_get_as_float (argv[0]);
518 gnm_float a = value_get_as_float (argv[1]);
519
520 return value_new_float (gnm_owent (h, a));
521 }
522
523 /***************************************************************************/
524
525 static GnmFuncHelp const help_lognormdist[] = {
526 { GNM_FUNC_HELP_NAME, F_("LOGNORMDIST:cumulative distribution function of the lognormal distribution")},
527 { GNM_FUNC_HELP_ARG, F_("x:number")},
528 { GNM_FUNC_HELP_ARG, F_("mean:mean")},
529 { GNM_FUNC_HELP_ARG, F_("stddev:standard deviation")},
530 { GNM_FUNC_HELP_NOTE, F_("If @{stddev} = 0 LOGNORMDIST returns a #DIV/0! error.")},
531 { GNM_FUNC_HELP_NOTE, F_("If @{x} <= 0, @{mean} < 0 or @{stddev} <= 0 this function returns a #NUM! error.")},
532 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
533 { GNM_FUNC_HELP_EXAMPLES, "=LOGNORMDIST(3,1,2)" },
534 { GNM_FUNC_HELP_SEEALSO, "NORMDIST"},
535 { GNM_FUNC_HELP_EXTREF, F_("wiki:en:Log-normal_distribution") },
536 { GNM_FUNC_HELP_EXTREF, F_("wolfram:LogNormalDistribution.html") },
537 { GNM_FUNC_HELP_END }
538 };
539
540 static GnmValue *
541 gnumeric_lognormdist (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
542 {
543 gnm_float x = value_get_as_float (argv[0]);
544 gnm_float mean = value_get_as_float (argv[1]);
545 gnm_float stddev = value_get_as_float (argv[2]);
546
547 if (x <= 0 || mean < 0 || stddev <= 0)
548 return value_new_error_NUM (ei->pos);
549
550 return value_new_float (plnorm (x, mean, stddev, TRUE, FALSE));
551 }
552
553 /***************************************************************************/
554
555 static GnmFuncHelp const help_loginv[] = {
556 { GNM_FUNC_HELP_NAME, F_("LOGINV:inverse of the cumulative distribution function of the lognormal distribution")},
557 { GNM_FUNC_HELP_ARG, F_("p:probability")},
558 { GNM_FUNC_HELP_ARG, F_("mean:mean")},
559 { GNM_FUNC_HELP_ARG, F_("stddev:standard deviation")},
560 { GNM_FUNC_HELP_NOTE, F_("If @{p} < 0 or @{p} > 1 or @{stddev} <= 0 this function returns #NUM! error.")},
561 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
562 { GNM_FUNC_HELP_EXAMPLES, "=LOGINV(0.5,2,3)" },
563 { GNM_FUNC_HELP_SEEALSO, ("EXP,LN,LOG,LOG10,LOGNORMDIST")},
564 { GNM_FUNC_HELP_EXTREF, F_("wiki:en:Log-normal_distribution") },
565 { GNM_FUNC_HELP_EXTREF, F_("wolfram:LogNormalDistribution.html") },
566 { GNM_FUNC_HELP_END }
567 };
568
569 static GnmValue *
570 gnumeric_loginv (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
571 {
572 gnm_float p, mean, stddev;
573
574 p = value_get_as_float (argv[0]);
575 mean = value_get_as_float (argv[1]);
576 stddev = value_get_as_float (argv[2]);
577
578 if (p < 0 || p > 1 || stddev <= 0)
579 return value_new_error_NUM (ei->pos);
580
581 return value_new_float (qlnorm (p, mean, stddev, TRUE, FALSE));
582 }
583
584 /***************************************************************************/
585
586 static GnmFuncHelp const help_fisherinv[] = {
587 { GNM_FUNC_HELP_NAME, F_("FISHERINV:inverse of the Fisher transformation")},
588 { GNM_FUNC_HELP_ARG, F_("x:number")},
589 { GNM_FUNC_HELP_NOTE, F_("If @{x} is a non-number this function returns a #VALUE! error.")},
590 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
591 { GNM_FUNC_HELP_EXAMPLES, "=FISHERINV(2)" },
592 { GNM_FUNC_HELP_SEEALSO, "FISHER,TANH"},
593 { GNM_FUNC_HELP_END }
594 };
595
596 static GnmValue *
597 gnumeric_fisherinv (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
598 {
599 return value_new_float (gnm_tanh (value_get_as_float (argv[0])));
600 }
601
602 /***************************************************************************/
603
604 static GnmFuncHelp const help_mode[] = {
605 { GNM_FUNC_HELP_NAME, F_("MODE:first most common number in the dataset")},
606 { GNM_FUNC_HELP_ARG, F_("number1:first value")},
607 { GNM_FUNC_HELP_ARG, F_("number2:second value")},
608 { GNM_FUNC_HELP_DESCRIPTION, F_("Strings and empty cells are simply ignored.")},
609 { GNM_FUNC_HELP_DESCRIPTION, F_("If the data set does not contain any duplicates this function returns a #N/A error.")},
610 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
611 { GNM_FUNC_HELP_EXAMPLES, "=MODE(11.4,17.3,11.4,3,25.9,40.1)" },
612 { GNM_FUNC_HELP_SEEALSO, "AVERAGE,MEDIAN,MODE.MULT"},
613 { GNM_FUNC_HELP_EXTREF, F_("wiki:en:Mode_(statistics)") },
614 { GNM_FUNC_HELP_EXTREF, F_("wolfram:Mode.html") },
615 { GNM_FUNC_HELP_END }
616 };
617
618 static GnmValue *
619 gnumeric_mode (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
620 {
621 return float_range_function (argc, argv, ei,
622 gnm_range_mode,
623 COLLECT_IGNORE_STRINGS |
624 COLLECT_IGNORE_BOOLS |
625 COLLECT_IGNORE_BLANKS,
626 GNM_ERROR_NA);
627 }
628
629 /***************************************************************************/
630
631 static GnmFuncHelp const help_mode_mult[] = {
632 { GNM_FUNC_HELP_NAME, F_("MODE.MULT:most common numbers in the dataset")},
633 { GNM_FUNC_HELP_ARG, F_("number1:first value")},
634 { GNM_FUNC_HELP_ARG, F_("number2:second value")},
635 { GNM_FUNC_HELP_DESCRIPTION, F_("Strings and empty cells are simply ignored.")},
636 { GNM_FUNC_HELP_DESCRIPTION, F_("If the data set does not contain any duplicates this function returns a #N/A error.")},
637 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
638 { GNM_FUNC_HELP_EXAMPLES, "=MODE.MULT(11.4,17.3,11.4,3,25.9,40.1)" },
639 { GNM_FUNC_HELP_SEEALSO, "AVERAGE,MEDIAN,MODE"},
640 { GNM_FUNC_HELP_EXTREF, F_("wiki:en:Mode_(statistics)") },
641 { GNM_FUNC_HELP_EXTREF, F_("wolfram:Mode.html") },
642 { GNM_FUNC_HELP_END }
643 };
644
645 static gint
646 gnumeric_mode_mult_cmp (gconstpointer a, gconstpointer b)
647 {
648 return (((*((gnm_float *)a))<(*((gnm_float *)b))) ? -1 : 1);
649 }
650
651 static gboolean
652 gnumeric_mode_mult_rm (gpointer key, gpointer value, gpointer user_data)
653 {
654 return (*((int *)user_data) != *((int *)value));
655 }
656
657 static GnmValue *
658 gnumeric_mode_mult (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
659 {
660 GnmValue *error = NULL;
661 GnmValue *result;
662 gnm_float *vals;
663 int n;
664 gboolean constp;
665
666 vals = collect_floats (argc, argv, ei->pos,
667 COLLECT_IGNORE_STRINGS |
668 COLLECT_IGNORE_BOOLS |
669 COLLECT_IGNORE_BLANKS,
670 &n, &error,
671 NULL, &constp);
672 if (!vals)
673 return error;
674
675 if (n <= 1)
676 result = value_new_error_NA (ei->pos);
677 else {
678 GHashTable *h;
679 int i;
680 int dups = 0;
681
682 h = g_hash_table_new_full ((GHashFunc)gnm_float_hash,
683 (GCompareFunc)gnm_float_equal,
684 NULL,
685 (GDestroyNotify)g_free);
686 for (i = 0; i < n; i++) {
687 gpointer rval;
688 gboolean found = g_hash_table_lookup_extended (h, &vals[i], NULL, &rval);
689 int *pdups;
690
691 if (found) {
692 pdups = (int *)rval;
693 (*pdups)++;
694 } else {
695 pdups = g_new (int, 1);
696 *pdups = 1;
697 g_hash_table_insert (h, (gpointer)(vals + i), pdups);
698 }
699
700 if (*pdups > dups)
701 dups = *pdups;
702 }
703
704 if (dups <= 1)
705 result = value_new_error_NA (ei->pos);
706 else {
707 GList *keys, *l;
708
709 g_hash_table_foreach_remove (h, gnumeric_mode_mult_rm, &dups);
710 keys = g_hash_table_get_keys (h);
711
712 keys = g_list_sort (keys, gnumeric_mode_mult_cmp);
713
714 result = value_new_array (1,g_list_length (keys));
715 i = 0;
716 for (l = keys; l != NULL; l = l->next)
717 value_array_set (result, 0, i++, value_new_float (*((gnm_float *)(l->data))));
718 g_list_free (keys);
719 }
720
721 g_hash_table_destroy (h);
722 }
723
724 if (!constp) g_free (vals);
725
726 return result;
727 }
728 /***************************************************************************/
729
730 static GnmFuncHelp const help_harmean[] = {
731 { GNM_FUNC_HELP_NAME, F_("HARMEAN:harmonic mean")},
732 { GNM_FUNC_HELP_ARG, F_("number1:first value")},
733 { GNM_FUNC_HELP_ARG, F_("number2:second value")},
734 { GNM_FUNC_HELP_DESCRIPTION, F_("The harmonic mean of N data points is N divided by the sum of the reciprocals of the data points).")},
735 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
736 { GNM_FUNC_HELP_EXAMPLES, "=HARMEAN(11.4,17.3,21.3,25.9,40.1)" },
737 { GNM_FUNC_HELP_SEEALSO, "AVERAGE,GEOMEAN,MEDIAN,MODE,TRIMMEAN"},
738 { GNM_FUNC_HELP_EXTREF, F_("wiki:en:Harmonic_mean") },
739 { GNM_FUNC_HELP_EXTREF, F_("wolfram:HarmonicMean.html") },
740 { GNM_FUNC_HELP_END }
741 };
742
743 static GnmValue *
744 gnumeric_harmean (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
745 {
746 return float_range_function (argc, argv, ei,
747 gnm_range_harmonic_mean,
748 COLLECT_IGNORE_STRINGS |
749 COLLECT_IGNORE_BOOLS |
750 COLLECT_IGNORE_BLANKS,
751 GNM_ERROR_NUM);
752 }
753
754 /***************************************************************************/
755
756 static GnmFuncHelp const help_geomean[] = {
757 { GNM_FUNC_HELP_NAME, F_("GEOMEAN:geometric mean")},
758 { GNM_FUNC_HELP_ARG, F_("number1:first value")},
759 { GNM_FUNC_HELP_ARG, F_("number2:second value")},
760 { GNM_FUNC_HELP_DESCRIPTION, F_("The geometric mean is equal to the Nth root of the product of the N values.")},
761 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
762 { GNM_FUNC_HELP_EXAMPLES, "=GEOMEAN(11.4,17.3,21.3,25.9,40.1)" },
763 { GNM_FUNC_HELP_SEEALSO, "AVERAGE,HARMEAN,MEDIAN,MODE,TRIMMEAN"},
764 { GNM_FUNC_HELP_EXTREF, F_("wiki:en:Geometric_mean") },
765 { GNM_FUNC_HELP_EXTREF, F_("wolfram:GeometricMean.html") },
766 { GNM_FUNC_HELP_END }
767 };
768
769 static GnmValue *
770 gnumeric_geomean (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
771 {
772 return float_range_function (argc, argv, ei,
773 gnm_range_geometric_mean,
774 COLLECT_IGNORE_STRINGS |
775 COLLECT_IGNORE_BOOLS |
776 COLLECT_IGNORE_BLANKS,
777 GNM_ERROR_NUM);
778 }
779
780 /***************************************************************************/
781
782 static GnmFuncHelp const help_count[] = {
783 { GNM_FUNC_HELP_NAME, F_("COUNT:total number of integer or floating point arguments passed")},
784 { GNM_FUNC_HELP_ARG, F_("number1:first value")},
785 { GNM_FUNC_HELP_ARG, F_("number2:second value")},
786 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
787 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5 contain numbers 11.4, 17.3, 21.3, 25.9, and 40.1.") },
788 { GNM_FUNC_HELP_EXAMPLES, F_("Then COUNT(A1:A5) equals 5.") },
789 { GNM_FUNC_HELP_SEEALSO, "AVERAGE"},
790 { GNM_FUNC_HELP_END }
791 };
792
793 static GnmValue *
794 gnumeric_count (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
795 {
796 return float_range_function (argc, argv, ei,
797 gnm_range_count,
798 COLLECT_IGNORE_ERRORS |
799 COLLECT_IGNORE_STRINGS |
800 COLLECT_IGNORE_BOOLS |
801 COLLECT_IGNORE_BLANKS,
802 GNM_ERROR_DIV0);
803 }
804
805 /***************************************************************************/
806
807 static GnmFuncHelp const help_counta[] = {
808 { GNM_FUNC_HELP_NAME, F_("COUNTA:number of arguments passed not including empty cells")},
809 { GNM_FUNC_HELP_ARG, F_("number1:first value")},
810 { GNM_FUNC_HELP_ARG, F_("number2:second value")},
811 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
812 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5 contain numbers and strings 11.4, \"missing\", \"missing\", 25.9, and 40.1.") },
813 { GNM_FUNC_HELP_EXAMPLES, F_("Then COUNTA(A1:A5) equals 5.") },
814 { GNM_FUNC_HELP_SEEALSO, "AVERAGE,COUNT,DCOUNT,DCOUNTA,PRODUCT,SUM"},
815 { GNM_FUNC_HELP_END }
816 };
817
818 static GnmValue *
819 gnumeric_counta (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
820 {
821 return float_range_function (argc, argv, ei,
822 gnm_range_count,
823 COLLECT_ZERO_ERRORS |
824 COLLECT_ZERO_STRINGS |
825 COLLECT_ZEROONE_BOOLS |
826 COLLECT_IGNORE_BLANKS,
827 GNM_ERROR_DIV0);
828 }
829
830 /***************************************************************************/
831
832 static GnmFuncHelp const help_average[] = {
833 { GNM_FUNC_HELP_NAME, F_("AVERAGE:average of all the numeric values and cells")},
834 { GNM_FUNC_HELP_ARG, F_("number1:first value")},
835 { GNM_FUNC_HELP_ARG, F_("number2:second value")},
836 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
837 { GNM_FUNC_HELP_EXAMPLES, "=AVERAGE(11.4,17.3,21.3,25.9,40.1)" },
838 { GNM_FUNC_HELP_SEEALSO, ("SUM, COUNT")},
839 { GNM_FUNC_HELP_EXTREF, F_("wiki:en:Arithmetic_mean") },
840 { GNM_FUNC_HELP_EXTREF, F_("wolfram:ArithmeticMean.html") },
841 { GNM_FUNC_HELP_END }
842 };
843
844 static GnmValue *
845 gnumeric_average (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
846 {
847 return float_range_function (argc, argv, ei,
848 gnm_range_average,
849 COLLECT_IGNORE_STRINGS |
850 COLLECT_IGNORE_BOOLS |
851 COLLECT_IGNORE_BLANKS,
852 GNM_ERROR_DIV0);
853 }
854
855 /***************************************************************************/
856
857 static GnmFuncHelp const help_min[] = {
858 { GNM_FUNC_HELP_NAME, F_("MIN:smallest value, with negative numbers considered smaller than positive numbers")},
859 { GNM_FUNC_HELP_ARG, F_("number1:first value")},
860 { GNM_FUNC_HELP_ARG, F_("number2:second value")},
861 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
862 { GNM_FUNC_HELP_EXAMPLES, "=MIN(11.4,17.3,21.3,25.9,40.1)" },
863 { GNM_FUNC_HELP_SEEALSO, "MAX,ABS"},
864 { GNM_FUNC_HELP_END }
865 };
866
867 static int
868 range_min0 (gnm_float const *xs, int n, gnm_float *res)
869 {
870 if (n == 0) {
871 *res = 0;
872 return 0;
873 } else
874 return gnm_range_min (xs, n, res);
875 }
876
877 static GnmValue *
878 gnumeric_min (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
879 {
880 return float_range_function (argc, argv, ei,
881 range_min0,
882 COLLECT_IGNORE_STRINGS |
883 COLLECT_IGNORE_BOOLS |
884 COLLECT_IGNORE_BLANKS |
885 COLLECT_ORDER_IRRELEVANT,
886 GNM_ERROR_VALUE);
887 }
888
889 /***************************************************************************/
890
891 static GnmFuncHelp const help_max[] = {
892 { GNM_FUNC_HELP_NAME, F_("MAX:largest value, with negative numbers considered smaller than positive numbers")},
893 { GNM_FUNC_HELP_ARG, F_("number1:first value")},
894 { GNM_FUNC_HELP_ARG, F_("number2:second value")},
895 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
896 { GNM_FUNC_HELP_EXAMPLES, "=MAX(11.4,17.3,21.3,25.9,40.1)" },
897 { GNM_FUNC_HELP_SEEALSO, "MIN,ABS"},
898 { GNM_FUNC_HELP_END }
899 };
900
901 static int
902 range_max0 (gnm_float const *xs, int n, gnm_float *res)
903 {
904 if (n == 0) {
905 *res = 0;
906 return 0;
907 } else
908 return gnm_range_max (xs, n, res);
909 }
910
911 static GnmValue *
912 gnumeric_max (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
913 {
914 return float_range_function (argc, argv, ei,
915 range_max0,
916 COLLECT_IGNORE_STRINGS |
917 COLLECT_IGNORE_BOOLS |
918 COLLECT_IGNORE_BLANKS |
919 COLLECT_ORDER_IRRELEVANT,
920 GNM_ERROR_VALUE);
921 }
922
923 /***************************************************************************/
924
925 static GnmFuncHelp const help_skew[] = {
926 { GNM_FUNC_HELP_NAME, F_("SKEW:unbiased estimate for skewness of a distribution")},
927 { GNM_FUNC_HELP_ARG, F_("number1:first value")},
928 { GNM_FUNC_HELP_ARG, F_("number2:second value")},
929 { GNM_FUNC_HELP_DESCRIPTION, F_("Strings and empty cells are simply ignored.")},
930 { GNM_FUNC_HELP_NOTE, F_("This is only meaningful if the underlying "
931 "distribution really has a third moment. The skewness of a "
932 "symmetric (e.g., normal) distribution is zero.")},
933 { GNM_FUNC_HELP_NOTE, F_("If less than three numbers are given, this function returns a #DIV/0! error.")},
934 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
935 { GNM_FUNC_HELP_EXAMPLES, "=SKEW(11.4,17.3,21.3,25.9,40.1)" },
936 { GNM_FUNC_HELP_SEEALSO, "AVERAGE,VAR,SKEWP,KURT"},
937 { GNM_FUNC_HELP_END }
938 };
939
940 static GnmValue *
941 gnumeric_skew (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
942 {
943 return float_range_function (argc, argv, ei,
944 gnm_range_skew_est,
945 COLLECT_IGNORE_STRINGS |
946 COLLECT_IGNORE_BOOLS |
947 COLLECT_IGNORE_BLANKS,
948 GNM_ERROR_DIV0);
949 }
950
951 /***************************************************************************/
952
953 static GnmFuncHelp const help_skewp[] = {
954 { GNM_FUNC_HELP_NAME, F_("SKEWP:population skewness of a data set")},
955 { GNM_FUNC_HELP_ARG, F_("number1:first value")},
956 { GNM_FUNC_HELP_ARG, F_("number2:second value")},
957 { GNM_FUNC_HELP_DESCRIPTION, F_("Strings and empty cells are simply ignored.")},
958 { GNM_FUNC_HELP_NOTE, F_("If less than two numbers are given, SKEWP returns a #DIV/0! error.") },
959 { GNM_FUNC_HELP_EXAMPLES, "=SKEWP(11.4,17.3,21.3,25.9,40.1)" },
960 { GNM_FUNC_HELP_SEEALSO, "AVERAGE,VARP,SKEW,KURTP"},
961 { GNM_FUNC_HELP_END }
962 };
963
964 static GnmValue *
965 gnumeric_skewp (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
966 {
967 return float_range_function (argc, argv, ei,
968 gnm_range_skew_pop,
969 COLLECT_IGNORE_STRINGS |
970 COLLECT_IGNORE_BOOLS |
971 COLLECT_IGNORE_BLANKS,
972 GNM_ERROR_DIV0);
973 }
974
975 /***************************************************************************/
976
977 static GnmFuncHelp const help_expondist[] = {
978 { GNM_FUNC_HELP_NAME, F_("EXPONDIST:probability density or cumulative distribution function of the exponential distribution")},
979 { GNM_FUNC_HELP_ARG, F_("x:number")},
980 { GNM_FUNC_HELP_ARG, F_("y:scale parameter")},
981 { GNM_FUNC_HELP_ARG, F_("cumulative:whether to evaluate the density function or the cumulative distribution function")},
982 { GNM_FUNC_HELP_DESCRIPTION, F_("If @{cumulative} is false it will return:\t"
983 "@{y} * exp (-@{y}*@{x}), otherwise it will return\t"
984 "1 - exp (-@{y}*@{x}).")},
985 { GNM_FUNC_HELP_NOTE, F_("If @{x} < 0 or @{y} <= 0 this will return an error.")},
986 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
987 { GNM_FUNC_HELP_EXAMPLES, "=EXPONDIST(2,4,0)" },
988 { GNM_FUNC_HELP_SEEALSO, "POISSON"},
989 { GNM_FUNC_HELP_END }
990 };
991
992 static GnmValue *
993 gnumeric_expondist (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
994 {
995 gnm_float x = value_get_as_float (argv[0]);
996 gnm_float y = value_get_as_float (argv[1]);
997 gboolean cuml = value_get_as_checked_bool (argv[2]);
998
999 if (x < 0.0 || y <= 0.0)
1000 return value_new_error_NUM (ei->pos);
1001
1002 if (cuml)
1003 return value_new_float (pexp (x, 1 / y, TRUE, FALSE));
1004 else
1005 return value_new_float (dexp (x, 1 / y, FALSE));
1006 }
1007
1008 /***************************************************************************/
1009
1010 static GnmFuncHelp const help_bernoulli[] = {
1011 { GNM_FUNC_HELP_NAME, F_("BERNOULLI:probability mass function of a Bernoulli distribution")},
1012 { GNM_FUNC_HELP_ARG, F_("k:integer")},
1013 { GNM_FUNC_HELP_ARG, F_("p:probability of success")},
1014 { GNM_FUNC_HELP_NOTE, F_("If @{k} != 0 and @{k} != 1 this function returns a #NUM! error.")},
1015 { GNM_FUNC_HELP_NOTE, F_("If @{p} < 0 or @{p} > 1 this function returns a #NUM! error.")},
1016 { GNM_FUNC_HELP_EXAMPLES, "=BERNOULLI(0,0.5)" },
1017 { GNM_FUNC_HELP_SEEALSO, "RANDBERNOULLI"},
1018 { GNM_FUNC_HELP_END }
1019 };
1020
1021 static gnm_float
1022 random_bernoulli_pdf (gnm_float k, gnm_float p)
1023 {
1024 if (k == 0)
1025 return 1 - p;
1026 else if (k == 1)
1027 return p;
1028 else
1029 return 0;
1030 }
1031
1032 static GnmValue *
1033 gnumeric_bernoulli (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1034 {
1035 gnm_float k = value_get_as_float (argv[0]);
1036 gnm_float p = value_get_as_float (argv[1]);
1037
1038 if (p < 0 || p > 1 || (k != 0 && k != 1))
1039 return value_new_error_NUM (ei->pos);
1040
1041 return value_new_float (random_bernoulli_pdf (k, p));
1042 }
1043
1044 /***************************************************************************/
1045
1046 static GnmFuncHelp const help_gammadist[] = {
1047 { GNM_FUNC_HELP_NAME, F_("GAMMADIST:probability density or cumulative distribution function of the gamma distribution")},
1048 { GNM_FUNC_HELP_ARG, F_("x:number")},
1049 { GNM_FUNC_HELP_ARG, F_("alpha:scale parameter")},
1050 { GNM_FUNC_HELP_ARG, F_("beta:scale parameter")},
1051 { GNM_FUNC_HELP_ARG, F_("cumulative:whether to evaluate the density function or the cumulative distribution function")},
1052 { GNM_FUNC_HELP_NOTE, F_("If @{x} < 0 this function returns a #NUM! error.")},
1053 { GNM_FUNC_HELP_NOTE, F_("If @{alpha} <= 0 or @{beta} <= 0, this function returns a #NUM! error.")},
1054 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1055 { GNM_FUNC_HELP_EXAMPLES, "=GAMMADIST(1,2,3,0)" },
1056 { GNM_FUNC_HELP_SEEALSO, "GAMMAINV"},
1057 { GNM_FUNC_HELP_END }
1058 };
1059
1060 static GnmValue *
1061 gnumeric_gammadist (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1062 {
1063 gnm_float x = value_get_as_float (argv[0]);
1064 gnm_float alpha = value_get_as_float (argv[1]);
1065 gnm_float beta = value_get_as_float (argv[2]);
1066 gboolean cum = value_get_as_checked_bool (argv[3]);
1067
1068 if (x < 0 || alpha <= 0 || beta <= 0)
1069 return value_new_error_NUM (ei->pos);
1070
1071 if (cum)
1072 return value_new_float (pgamma (x, alpha, beta, TRUE, FALSE));
1073 else
1074 return value_new_float (dgamma (x, alpha, beta, FALSE));
1075 }
1076
1077 /***************************************************************************/
1078
1079 static GnmFuncHelp const help_gammainv[] = {
1080 { GNM_FUNC_HELP_NAME, F_("GAMMAINV:inverse of the cumulative gamma distribution")},
1081 { GNM_FUNC_HELP_ARG, F_("p:probability")},
1082 { GNM_FUNC_HELP_ARG, F_("alpha:scale parameter")},
1083 { GNM_FUNC_HELP_ARG, F_("beta:scale parameter")},
1084 { GNM_FUNC_HELP_NOTE, F_("If @{p} < 0 or @{p} > 1 this function returns a #NUM! error.") },
1085 { GNM_FUNC_HELP_NOTE, F_("If @{alpha} <= 0 or @{beta} <= 0 this function returns a #NUM! error.")},
1086 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1087 { GNM_FUNC_HELP_EXAMPLES, "=GAMMAINV(0.34,2,4)" },
1088 { GNM_FUNC_HELP_SEEALSO, "GAMMADIST"},
1089 { GNM_FUNC_HELP_END }
1090 };
1091
1092 static GnmValue *
1093 gnumeric_gammainv (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1094 {
1095 gnm_float p, alpha, beta;
1096
1097 p = value_get_as_float (argv[0]);
1098 alpha = value_get_as_float (argv[1]);
1099 beta = value_get_as_float (argv[2]);
1100
1101 if (p < 0 || p > 1 || alpha <= 0 || beta <= 0)
1102 return value_new_error_NUM (ei->pos);
1103
1104 return value_new_float (qgamma (p, alpha, beta, TRUE, FALSE));
1105 }
1106
1107 /***************************************************************************/
1108
1109 static GnmFuncHelp const help_chidist[] = {
1110 { GNM_FUNC_HELP_NAME, F_("CHIDIST:survival function of the chi-squared distribution")},
1111 { GNM_FUNC_HELP_ARG, F_("x:number")},
1112 { GNM_FUNC_HELP_ARG, F_("dof:number of degrees of freedom")},
1113 { GNM_FUNC_HELP_DESCRIPTION, F_("The survival function is 1 minus the cumulative distribution function.") },
1114 { GNM_FUNC_HELP_NOTE, F_("If @{dof} is non-integer it is truncated.") },
1115 { GNM_FUNC_HELP_NOTE, F_("If @{dof} < 1 this function returns a #NUM! error.")},
1116 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1117 { GNM_FUNC_HELP_ODF, F_("CHIDIST(@{x},@{dof}) is the OpenFormula function LEGACY.CHIDIST(@{x},@{dof}).") },
1118 { GNM_FUNC_HELP_EXAMPLES, "=CHIDIST(5.3,2)" },
1119 { GNM_FUNC_HELP_SEEALSO, "CHIINV,CHITEST"},
1120 { GNM_FUNC_HELP_END }
1121 };
1122
1123 static GnmValue *
1124 gnumeric_chidist (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1125 {
1126 gnm_float x = value_get_as_float (argv[0]);
1127 gnm_float dof = gnm_fake_floor (value_get_as_float (argv[1]));
1128
1129 if (dof < 1)
1130 return value_new_error_NUM (ei->pos);
1131
1132 return value_new_float (pchisq (x, dof, FALSE, FALSE));
1133 }
1134
1135 /***************************************************************************/
1136
1137 static GnmFuncHelp const help_chiinv[] = {
1138 { GNM_FUNC_HELP_NAME, F_("CHIINV:inverse of the survival function of the chi-squared distribution")},
1139 { GNM_FUNC_HELP_ARG, F_("p:probability")},
1140 { GNM_FUNC_HELP_ARG, F_("dof:number of degrees of freedom")},
1141 { GNM_FUNC_HELP_DESCRIPTION, F_("The survival function is 1 minus the cumulative distribution function.") },
1142 { GNM_FUNC_HELP_NOTE, F_("If @{p} < 0 or @{p} > 1 or @{dof} < 1 this function returns a #NUM! error.")},
1143 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1144 { GNM_FUNC_HELP_ODF, F_("CHIINV(@{p},@{dof}) is the OpenFormula function LEGACY.CHIDIST(@{p},@{dof}).") },
1145 { GNM_FUNC_HELP_EXAMPLES, "=CHIINV(0.98,7)" },
1146 { GNM_FUNC_HELP_SEEALSO, "CHIDIST,CHITEST"},
1147 { GNM_FUNC_HELP_END }
1148 };
1149
1150 static GnmValue *
1151 gnumeric_chiinv (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1152 {
1153 gnm_float p = value_get_as_float (argv[0]);
1154 gnm_float dof = gnm_fake_floor (value_get_as_float (argv[1]));
1155
1156 if (p < 0 || p > 1 || dof < 1)
1157 return value_new_error_NUM (ei->pos);
1158
1159 return value_new_float (qchisq (p, dof, FALSE, FALSE));
1160 }
1161
1162 /***************************************************************************/
1163
1164 static GnmFuncHelp const help_chitest[] = {
1165 { GNM_FUNC_HELP_NAME, F_("CHITEST:p value of the Goodness of Fit Test")},
1166 { GNM_FUNC_HELP_ARG, F_("actual_range:observed data")},
1167 { GNM_FUNC_HELP_ARG, F_("theoretical_range:expected values")},
1168 { GNM_FUNC_HELP_NOTE, F_("If the actual range is not an n by 1 or 1 by n range, "
1169 "but an n by m range, then CHITEST uses (n-1) times (m-1) as "
1170 "degrees of freedom. This is useful if the expected values "
1171 "were calculated from the observed value in a test of "
1172 "independence or test of homogeneity.")},
1173 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1174 { GNM_FUNC_HELP_ODF, F_("CHITEST is the OpenFormula function LEGACY.CHITEST.") },
1175 { GNM_FUNC_HELP_SEEALSO, "CHIDIST,CHIINV"},
1176 { GNM_FUNC_HELP_END }
1177 };
1178
1179 static int
1180 calc_chisq (gnm_float const *xs, gnm_float const *ys, int n, gnm_float *res)
1181 {
1182 gnm_float sum = 0;
1183 int i;
1184 gboolean has_neg = FALSE;
1185
1186 if (n == 0)
1187 return 1;
1188
1189 for (i = 0; i < n; i++) {
1190 gnm_float a = xs[i];
1191 gnm_float e = ys[i];
1192
1193 if (e == 0)
1194 return 1;
1195 else if (e < 0)
1196 has_neg = TRUE;
1197 else
1198 sum += (a - e) / e * (a - e);
1199 }
1200
1201 if (has_neg) {
1202 /* Hack: return -1 as a flag. */
1203 *res = -1;
1204 return 0;
1205 }
1206
1207 *res = sum;
1208 return 0;
1209 }
1210
1211 static GnmValue *
1212 gnumeric_chitest (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1213 {
1214 int w0 = value_area_get_width (argv[0], ei->pos);
1215 int h0 = value_area_get_height (argv[0], ei->pos);
1216 int w1 = value_area_get_width (argv[1], ei->pos);
1217 int h1 = value_area_get_height (argv[1], ei->pos);
1218 GnmValue *v;
1219 gnm_float chisq;
1220 int df;
1221
1222 /* Size error takes precedence over everything else. */
1223 if (w0 * h0 != w1 * h1)
1224 return value_new_error_NA (ei->pos);
1225
1226 v = float_range_function2 (argv[0], argv[1],
1227 ei,
1228 calc_chisq,
1229 COLLECT_IGNORE_BLANKS |
1230 COLLECT_IGNORE_STRINGS |
1231 COLLECT_IGNORE_BOOLS,
1232 GNM_ERROR_DIV0);
1233
1234 if (!VALUE_IS_NUMBER (v))
1235 return v;
1236
1237 chisq = value_get_as_float (v);
1238 value_release (v);
1239
1240 /* See calc_chisq. */
1241 if (chisq == -1)
1242 return value_new_error_NUM (ei->pos);
1243
1244 df = (w0-1) * (h0-1);
1245 df = (df == 0 ? w0 * h0 - 1 : df);
1246 return value_new_float (pchisq (chisq, df, FALSE, FALSE));
1247 }
1248
1249 /***************************************************************************/
1250
1251 static GnmFuncHelp const help_betadist[] = {
1252 { GNM_FUNC_HELP_NAME, F_("BETADIST:cumulative distribution function of the beta distribution")},
1253 { GNM_FUNC_HELP_ARG, F_("x:number")},
1254 { GNM_FUNC_HELP_ARG, F_("alpha:scale parameter")},
1255 { GNM_FUNC_HELP_ARG, F_("beta:scale parameter")},
1256 { GNM_FUNC_HELP_ARG, F_("a:optional lower bound, defaults to 0")},
1257 { GNM_FUNC_HELP_ARG, F_("b:optional upper bound, defaults to 1")},
1258 { GNM_FUNC_HELP_NOTE, F_("If @{x} < @{a} or @{x} > @{b} this function returns a #NUM! error.") },
1259 { GNM_FUNC_HELP_NOTE, F_("If @{alpha} <= 0 or @{beta} <= 0, this function returns a #NUM! error.") },
1260 { GNM_FUNC_HELP_NOTE, F_("If @{a} >= @{b} this function returns a #NUM! error.")},
1261 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1262 { GNM_FUNC_HELP_EXAMPLES, "=BETADIST(0.12,2,3)" },
1263 { GNM_FUNC_HELP_SEEALSO, "BETAINV, BETA.DIST"},
1264 { GNM_FUNC_HELP_END }
1265 };
1266
1267 static GnmValue *
1268 gnumeric_betadist (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1269 {
1270 gnm_float x, alpha, beta, a, b;
1271
1272 x = value_get_as_float (argv[0]);
1273 alpha = value_get_as_float (argv[1]);
1274 beta = value_get_as_float (argv[2]);
1275 a = argv[3] ? value_get_as_float (argv[3]) : 0;
1276 b = argv[4] ? value_get_as_float (argv[4]) : 1;
1277
1278 if (x < a || x > b || a >= b || alpha <= 0 || beta <= 0)
1279 return value_new_error_NUM (ei->pos);
1280
1281 return value_new_float (pbeta ((x - a) / (b - a), alpha, beta, TRUE, FALSE));
1282 }
1283
1284 /***************************************************************************/
1285
1286 static GnmFuncHelp const help_beta_dist[] = {
1287 { GNM_FUNC_HELP_NAME, F_("BETA.DIST:cumulative distribution function of the beta distribution")},
1288 { GNM_FUNC_HELP_ARG, F_("x:number")},
1289 { GNM_FUNC_HELP_ARG, F_("alpha:scale parameter")},
1290 { GNM_FUNC_HELP_ARG, F_("beta:scale parameter")},
1291 { GNM_FUNC_HELP_ARG, F_("cumulative:whether to evaluate the density function or the cumulative "
1292 "distribution function")},
1293 { GNM_FUNC_HELP_ARG, F_("a:optional lower bound, defaults to 0")},
1294 { GNM_FUNC_HELP_ARG, F_("b:optional upper bound, defaults to 1")},
1295 { GNM_FUNC_HELP_NOTE, F_("If @{x} < @{a} or @{x} > @{b} this function returns a #NUM! error.") },
1296 { GNM_FUNC_HELP_NOTE, F_("If @{alpha} <= 0 or @{beta} <= 0, this function returns a #NUM! error.") },
1297 { GNM_FUNC_HELP_NOTE, F_("If @{a} >= @{b} this function returns a #NUM! error.")},
1298 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1299 { GNM_FUNC_HELP_EXAMPLES, "=BETA.DIST(0.12,2,3,FALSE,0,4)" },
1300 { GNM_FUNC_HELP_SEEALSO, "BETAINV,BETADIST"},
1301 { GNM_FUNC_HELP_END }
1302 };
1303
1304 static GnmValue *
1305 gnumeric_beta_dist (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1306 {
1307 gnm_float x, alpha, beta, a, b;
1308 gboolean cuml;
1309
1310 x = value_get_as_float (argv[0]);
1311 alpha = value_get_as_float (argv[1]);
1312 beta = value_get_as_float (argv[2]);
1313 cuml = value_get_as_checked_bool (argv[3]);
1314 a = argv[4] ? value_get_as_float (argv[4]) : 0;
1315 b = argv[5] ? value_get_as_float (argv[5]) : 1;
1316
1317 if (x < a || x > b || a >= b || alpha <= 0 || beta <= 0)
1318 return value_new_error_NUM (ei->pos);
1319
1320 if (cuml)
1321 return value_new_float (pbeta ((x - a) / (b - a), alpha, beta, TRUE, FALSE));
1322 else
1323 return value_new_float (dbeta ((x - a) / (b - a), alpha, beta, FALSE) / (b - a));
1324 }
1325 /***************************************************************************/
1326
1327 /* Note: Excel's this function returns a #NUM! for various values where it
1328 simply gives up computing the result. */
1329 static GnmFuncHelp const help_betainv[] = {
1330 { GNM_FUNC_HELP_NAME, F_("BETAINV:inverse of the cumulative distribution function of the beta distribution")},
1331 { GNM_FUNC_HELP_ARG, F_("p:probability")},
1332 { GNM_FUNC_HELP_ARG, F_("alpha:scale parameter")},
1333 { GNM_FUNC_HELP_ARG, F_("beta:scale parameter")},
1334 { GNM_FUNC_HELP_ARG, F_("a:optional lower bound, defaults to 0")},
1335 { GNM_FUNC_HELP_ARG, F_("b:optional upper bound, defaults to 1")},
1336 { GNM_FUNC_HELP_NOTE, F_("If @{p} < 0 or @{p} > 1 this function returns a #NUM! error.") },
1337 { GNM_FUNC_HELP_NOTE, F_("If @{alpha} <= 0 or @{beta} <= 0, this function returns a #NUM! error.") },
1338 { GNM_FUNC_HELP_NOTE, F_("If @{a} >= @{b} this function returns a #NUM! error.")},
1339 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1340 { GNM_FUNC_HELP_EXAMPLES, "=BETAINV(0.45,1.6,1)" },
1341 { GNM_FUNC_HELP_SEEALSO, "BETADIST,BETA.DIST"},
1342 { GNM_FUNC_HELP_END }
1343 };
1344
1345 static GnmValue *
1346 gnumeric_betainv (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1347 {
1348 gnm_float p, alpha, beta, a, b;
1349
1350 p = value_get_as_float (argv[0]);
1351 alpha = value_get_as_float (argv[1]);
1352 beta = value_get_as_float (argv[2]);
1353 a = argv[3] ? value_get_as_float (argv[3]) : 0;
1354 b = argv[4] ? value_get_as_float (argv[4]) : 1;
1355
1356 if (p < 0 || p > 1 || a >= b || alpha <= 0 || beta <= 0)
1357 return value_new_error_NUM (ei->pos);
1358
1359 return value_new_float ((b - a) * qbeta (p, alpha, beta, TRUE, FALSE) + a);
1360 }
1361
1362 /***************************************************************************/
1363
1364 static GnmFuncHelp const help_tdist[] = {
1365 { GNM_FUNC_HELP_NAME, F_("TDIST:survival function of the Student t-distribution")},
1366 { GNM_FUNC_HELP_ARG, F_("x:number")},
1367 { GNM_FUNC_HELP_ARG, F_("dof:number of degrees of freedom")},
1368 { GNM_FUNC_HELP_ARG, F_("tails:1 or 2")},
1369 { GNM_FUNC_HELP_DESCRIPTION, F_("The survival function is 1 minus the cumulative distribution function.") },
1370 { GNM_FUNC_HELP_NOTE, F_("If @{dof} < 1 this function returns a #NUM! error.") },
1371 { GNM_FUNC_HELP_NOTE, F_("If @{tails} is neither 1 or 2 this function returns a #NUM! error.") },
1372 { GNM_FUNC_HELP_NOTE, F_("The parameterization of this function is different from "
1373 "what is used for, e.g., NORMSDIST. This is a common source of "
1374 "mistakes, but necessary for compatibility.") },
1375 { GNM_FUNC_HELP_DESCRIPTION, F_("This function is Excel compatible for non-negative @{x}.") },
1376 { GNM_FUNC_HELP_EXAMPLES, "=TDIST(2,5,1)" },
1377 { GNM_FUNC_HELP_EXAMPLES, "=TDIST(-2,5,1)" },
1378 { GNM_FUNC_HELP_EXAMPLES, "=TDIST(0,5,2)" },
1379 { GNM_FUNC_HELP_SEEALSO, "TINV,TTEST"},
1380 { GNM_FUNC_HELP_END }
1381 };
1382
1383 static GnmValue *
1384 gnumeric_tdist (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1385 {
1386 gnm_float x = value_get_as_float (argv[0]);
1387 gnm_float dof = value_get_as_float (argv[1]);
1388 gnm_float tails = value_get_as_float (argv[2]);
1389 gnm_float p;
1390
1391 if (dof < 1)
1392 goto bad;
1393
1394 if (tails == 1) {
1395 gboolean lower_tail = FALSE;
1396 if (x < 0) {
1397 lower_tail = TRUE;
1398 x = -x;
1399 }
1400 p = pt (x, dof, lower_tail, FALSE);
1401 } else if (tails == 2) {
1402 if (x < 0)
1403 goto bad;
1404 p = 2 * pt (x, dof, FALSE, FALSE);
1405 } else
1406 goto bad;
1407
1408 return value_new_float (p);
1409
1410 bad:
1411 return value_new_error_NUM (ei->pos);
1412 }
1413
1414 /***************************************************************************/
1415
1416 static GnmFuncHelp const help_tinv[] = {
1417 { GNM_FUNC_HELP_NAME, F_("TINV:two tailed inverse of the Student t-distribution")},
1418 { GNM_FUNC_HELP_ARG, F_("p:probability in both tails")},
1419 { GNM_FUNC_HELP_ARG, F_("dof:number of degrees of freedom")},
1420 { GNM_FUNC_HELP_DESCRIPTION, F_("This function returns the non-negative value x such that the "
1421 "area under the Student t density with @{dof} degrees of freedom "
1422 "to the right of x is @{p}/2.") },
1423 { GNM_FUNC_HELP_NOTE, F_("If @{p} < 0 or @{p} > 1 or @{dof} < 1 this function returns a #NUM! error.")},
1424 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1425 { GNM_FUNC_HELP_NOTE, F_("The parameterization of this function is different from "
1426 "what is used for, e.g., NORMSINV. This is a common source of "
1427 "mistakes, but necessary for compatibility.") },
1428 { GNM_FUNC_HELP_EXAMPLES, "=TINV(0.4,32)" },
1429 { GNM_FUNC_HELP_SEEALSO, "TDIST,TTEST"},
1430 { GNM_FUNC_HELP_END }
1431 };
1432
1433 static GnmValue *
1434 gnumeric_tinv (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1435 {
1436 gnm_float p = value_get_as_float (argv[0]);
1437 gnm_float dof = value_get_as_float (argv[1]);
1438 gnm_float result;
1439
1440 if (p < 0 || p > 1 || dof < 1)
1441 return value_new_error_NUM (ei->pos);
1442
1443 result = qt (p / 2, dof, FALSE, FALSE);
1444
1445 if (result < 0)
1446 return value_new_error_NUM (ei->pos);
1447
1448 return value_new_float (result);
1449 }
1450
1451 /***************************************************************************/
1452
1453 static GnmFuncHelp const help_fdist[] = {
1454 { GNM_FUNC_HELP_NAME, F_("FDIST:survival function of the F distribution")},
1455 { GNM_FUNC_HELP_ARG, F_("x:number")},
1456 { GNM_FUNC_HELP_ARG, F_("dof_of_num:numerator degrees of freedom")},
1457 { GNM_FUNC_HELP_ARG, F_("dof_of_denom:denominator degrees of freedom")},
1458 { GNM_FUNC_HELP_DESCRIPTION, F_("The survival function is 1 minus the cumulative distribution function.") },
1459 { GNM_FUNC_HELP_NOTE, F_("If @{x} < 0 this function returns a #NUM! error.") },
1460 { GNM_FUNC_HELP_NOTE, F_("If @{dof_of_num} < 1 or @{dof_of_denom} < 1, this function returns a #NUM! error.")},
1461 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1462 { GNM_FUNC_HELP_ODF, F_("FDIST is the OpenFormula function LEGACY.FDIST.") },
1463 { GNM_FUNC_HELP_EXAMPLES, "=FDIST(2,5,5)" },
1464 { GNM_FUNC_HELP_SEEALSO, "FINV"},
1465 { GNM_FUNC_HELP_END }
1466 };
1467
1468 static GnmValue *
1469 gnumeric_fdist (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1470 {
1471 gnm_float x = value_get_as_float (argv[0]);
1472 gnm_float dof1 = gnm_fake_floor (value_get_as_float (argv[1]));
1473 gnm_float dof2 = gnm_fake_floor (value_get_as_float (argv[2]));
1474
1475 if (x < 0 || dof1 < 1 || dof2 < 1)
1476 return value_new_error_NUM (ei->pos);
1477
1478 return value_new_float (pf (x, dof1, dof2, FALSE, FALSE));
1479 }
1480
1481 /***************************************************************************/
1482
1483 static GnmFuncHelp const help_landau[] = {
1484 { GNM_FUNC_HELP_NAME, F_("LANDAU:approximate probability density function of the Landau distribution")},
1485 { GNM_FUNC_HELP_ARG, F_("x:number")},
1486 { GNM_FUNC_HELP_EXAMPLES, "=LANDAU(0.34)" },
1487 { GNM_FUNC_HELP_SEEALSO, "RANDLANDAU"},
1488 { GNM_FUNC_HELP_END }
1489 };
1490
1491 /* From the GNU Scientific Library 1.1.1 (randist/landau.c)
1492 *
1493 * Copyright (C) 2001 David Morrison
1494 */
1495 static gnm_float
1496 random_landau_pdf (gnm_float x)
1497 {
1498 static const gnm_float P1[5] = {
1499 0.4259894875E0, -0.1249762550E0, 0.3984243700E-1,
1500 -0.6298287635E-2, 0.1511162253E-2
1501 };
1502 static const gnm_float P2[5] = {
1503 0.1788541609E0, 0.1173957403E0, 0.1488850518E-1,
1504 -0.1394989411E-2, 0.1283617211E-3
1505 };
1506 static const gnm_float P3[5] = {
1507 0.1788544503E0, 0.9359161662E-1, 0.6325387654E-2,
1508 0.6611667319E-4, -0.2031049101E-5
1509 };
1510 static const gnm_float P4[5] = {
1511 0.9874054407E0, 0.1186723273E3, 0.8492794360E3,
1512 -0.7437792444E3, 0.4270262186E3
1513 };
1514 static const gnm_float P5[5] = {
1515 0.1003675074E1, 0.1675702434E3, 0.4789711289E4,
1516 0.2121786767E5, -0.2232494910E5
1517 };
1518 static const gnm_float P6[5] = {
1519 0.1000827619E1, 0.6649143136E3, 0.6297292665E5,
1520 0.4755546998E6, -0.5743609109E7
1521 };
1522
1523 static const gnm_float Q1[5] = {
1524 1.0, -0.3388260629E0, 0.9594393323E-1,
1525 -0.1608042283E-1, 0.3778942063E-2
1526 };
1527 static const gnm_float Q2[5] = {
1528 1.0, 0.7428795082E0, 0.3153932961E0,
1529 0.6694219548E-1, 0.8790609714E-2
1530 };
1531 static const gnm_float Q3[5] = {
1532 1.0, 0.6097809921E0, 0.2560616665E0,
1533 0.4746722384E-1, 0.6957301675E-2
1534 };
1535 static const gnm_float Q4[5] = {
1536 1.0, 0.1068615961E3, 0.3376496214E3,
1537 0.2016712389E4, 0.1597063511E4
1538 };
1539 static const gnm_float Q5[5] = {
1540 1.0, 0.1569424537E3, 0.3745310488E4,
1541 0.9834698876E4, 0.6692428357E5
1542 };
1543 static const gnm_float Q6[5] = {
1544 1.0, 0.6514101098E3, 0.5697473333E5,
1545 0.1659174725E6, -0.2815759939E7
1546 };
1547
1548 static const gnm_float A1[3] = {
1549 0.4166666667E-1, -0.1996527778E-1, 0.2709538966E-1
1550 };
1551 static const gnm_float A2[2] = {
1552 -0.1845568670E1, -0.4284640743E1
1553 };
1554
1555 gnm_float U, V, DENLAN;
1556
1557 V = x;
1558 if (V < -5.5) {
1559 U = gnm_exp (V + 1.0);
1560 DENLAN = 0.3989422803 * (gnm_exp ( -1 / U) / gnm_sqrt (U)) *
1561 (1 + (A1[0] + (A1[1] + A1[2] * U) * U) * U);
1562 } else if (V < -1) {
1563 U = gnm_exp (-V - 1);
1564 DENLAN = gnm_exp ( -U) * gnm_sqrt (U) *
1565 (P1[0] + (P1[1] + (P1[2] + (P1[3] + P1[4] * V) * V)
1566 * V) * V) /
1567 (Q1[0] + (Q1[1] + (Q1[2] + (Q1[3] + Q1[4] * V) * V)
1568 * V) * V);
1569 } else if (V < 1) {
1570 DENLAN = (P2[0] + (P2[1] + (P2[2] + (P2[3] + P2[4] * V) * V)
1571 * V) * V) /
1572 (Q2[0] + (Q2[1] + (Q2[2] + (Q2[3] + Q2[4] * V) * V)
1573 * V) * V);
1574 } else if (V < 5) {
1575 DENLAN = (P3[0] + (P3[1] + (P3[2] + (P3[3] + P3[4] * V) * V)
1576 * V) * V) /
1577 (Q3[0] + (Q3[1] + (Q3[2] + (Q3[3] + Q3[4] * V) * V)
1578 * V) * V);
1579 } else if (V < 12) {
1580 U = 1 / V;
1581 DENLAN = U * U *
1582 (P4[0] + (P4[1] + (P4[2] + (P4[3] + P4[4] * U) * U)
1583 * U) * U) /
1584 (Q4[0] + (Q4[1] + (Q4[2] + (Q4[3] + Q4[4] * U) * U)
1585 * U) * U);
1586 } else if (V < 50) {
1587 U = 1 / V;
1588 DENLAN = U * U *
1589 (P5[0] + (P5[1] + (P5[2] + (P5[3] + P5[4] * U) * U)
1590 * U) * U) /
1591 (Q5[0] + (Q5[1] + (Q5[2] + (Q5[3] + Q5[4] * U) * U)
1592 * U) * U);
1593 } else if (V < 300) {
1594 U = 1 / V;
1595 DENLAN = U * U *
1596 (P6[0] + (P6[1] + (P6[2] + (P6[3] + P6[4] * U) * U)
1597 * U) * U) /
1598 (Q6[0] + (Q6[1] + (Q6[2] + (Q6[3] + Q6[4] * U) * U)
1599 * U) * U);
1600 } else {
1601 U = 1 / (V - V * log(V) / (V + 1));
1602 DENLAN = U * U * (1 + (A2[0] + A2[1] * U) * U);
1603 }
1604
1605 return DENLAN;
1606 }
1607
1608 static GnmValue *
1609 gnumeric_landau (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1610 {
1611 gnm_float x = value_get_as_float (argv[0]);
1612
1613 return value_new_float (random_landau_pdf (x));
1614 }
1615
1616
1617 /***************************************************************************/
1618
1619 static GnmFuncHelp const help_finv[] = {
1620 { GNM_FUNC_HELP_NAME, F_("FINV:inverse of the survival function of the F distribution")},
1621 { GNM_FUNC_HELP_ARG, F_("p:probability")},
1622 { GNM_FUNC_HELP_ARG, F_("dof_of_num:numerator degrees of freedom")},
1623 { GNM_FUNC_HELP_ARG, F_("dof_of_denom:denominator degrees of freedom")},
1624 { GNM_FUNC_HELP_DESCRIPTION, F_("The survival function is 1 minus the cumulative distribution function.") },
1625 { GNM_FUNC_HELP_NOTE, F_("If @{p} < 0 or @{p} > 1 this function returns a #NUM! error.") },
1626 { GNM_FUNC_HELP_NOTE, F_("If @{dof_of_num} < 1 or @{dof_of_denom} < 1 this function returns a #NUM! error.")},
1627 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1628 { GNM_FUNC_HELP_ODF, F_("FINV is the OpenFormula function LEGACY.FINV.") },
1629 { GNM_FUNC_HELP_EXAMPLES, "=FINV(0.2,2,4)" },
1630 { GNM_FUNC_HELP_SEEALSO, "FDIST"},
1631 { GNM_FUNC_HELP_END }
1632 };
1633
1634 static GnmValue *
1635 gnumeric_finv (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1636 {
1637 gnm_float p = value_get_as_float (argv[0]);
1638 gnm_float dof1 = gnm_fake_floor (value_get_as_float (argv[1]));
1639 gnm_float dof2 = gnm_fake_floor (value_get_as_float (argv[2]));
1640
1641 if (p < 0 || p > 1 || dof1 < 1 || dof2 < 1)
1642 return value_new_error_NUM (ei->pos);
1643
1644 return value_new_float (qf (p, dof1, dof2, FALSE, FALSE));
1645 }
1646
1647 /***************************************************************************/
1648
1649 static GnmFuncHelp const help_binomdist[] = {
1650 { GNM_FUNC_HELP_NAME, F_("BINOMDIST:probability mass or cumulative distribution function of the binomial distribution")},
1651 { GNM_FUNC_HELP_ARG, F_("n:number of successes")},
1652 { GNM_FUNC_HELP_ARG, F_("trials:number of trials")},
1653 { GNM_FUNC_HELP_ARG, F_("p:probability of success in each trial")},
1654 { GNM_FUNC_HELP_ARG, F_("cumulative:whether to evaluate the mass function or the cumulative distribution function")},
1655 { GNM_FUNC_HELP_NOTE, F_("If @{n} or @{trials} are non-integer they are truncated.") },
1656 { GNM_FUNC_HELP_NOTE, F_("If @{n} < 0 or @{trials} < 0 this function returns a #NUM! error.") },
1657 { GNM_FUNC_HELP_NOTE, F_("If @{n} > @{trials} this function returns a #NUM! error.") },
1658 { GNM_FUNC_HELP_NOTE, F_("If @{p} < 0 or @{p} > 1 this function returns a #NUM! error.")},
1659 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1660 { GNM_FUNC_HELP_EXAMPLES, "=BINOMDIST(3,5,0.8,0)" },
1661 { GNM_FUNC_HELP_SEEALSO, "POISSON"},
1662 { GNM_FUNC_HELP_END }
1663 };
1664
1665 static GnmValue *
1666 gnumeric_binomdist (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1667 {
1668 gnm_float n = gnm_fake_floor (value_get_as_float (argv[0]));
1669 gnm_float trials = gnm_fake_floor (value_get_as_float (argv[1]));
1670 gnm_float p = value_get_as_float (argv[2]);
1671 gboolean cuml = value_get_as_checked_bool (argv[3]);
1672
1673 if (n < 0 || trials < 0 || p < 0 || p > 1 || n > trials)
1674 return value_new_error_NUM (ei->pos);
1675
1676 if (cuml)
1677 return value_new_float (pbinom (n, trials, p, TRUE, FALSE));
1678 else
1679 return value_new_float (dbinom (n, trials, p, FALSE));
1680 }
1681
1682 /***************************************************************************/
1683
1684 static GnmFuncHelp const help_binom_dist_range[] = {
1685 { GNM_FUNC_HELP_NAME, F_("BINOM.DIST.RANGE:probability of the binomial distribution over an interval")},
1686 { GNM_FUNC_HELP_ARG, F_("trials:number of trials")},
1687 { GNM_FUNC_HELP_ARG, F_("p:probability of success in each trial")},
1688 { GNM_FUNC_HELP_ARG, F_("start:start of the interval")},
1689 { GNM_FUNC_HELP_ARG, F_("end:end of the interval, defaults to @{start}")},
1690 { GNM_FUNC_HELP_NOTE, F_("If @{start}, @{end} or @{trials} are non-integer they are truncated.") },
1691 { GNM_FUNC_HELP_NOTE, F_("If @{trials} < 0 this function returns a #NUM! error.") },
1692 { GNM_FUNC_HELP_NOTE, F_("If @{p} < 0 or @{p} > 1 this function returns a #NUM! error.")},
1693 { GNM_FUNC_HELP_NOTE, F_("If @{start} > @{end} this function returns 0.")},
1694 { GNM_FUNC_HELP_ODF, F_("This function is OpenFormula compatible.") },
1695 { GNM_FUNC_HELP_EXAMPLES, "=BINOM.DIST.RANGE(5,0.8,3,4)" },
1696 { GNM_FUNC_HELP_SEEALSO, "BINOMDIST,R.PBINOM"},
1697 { GNM_FUNC_HELP_END }
1698 };
1699
1700 static GnmValue *
1701 gnumeric_binom_dist_range (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1702 {
1703 gnm_float trials = gnm_fake_floor (value_get_as_float (argv[0]));
1704 gnm_float p = value_get_as_float (argv[1]);
1705 gnm_float start = gnm_fake_floor (value_get_as_float (argv[2]));
1706 gnm_float end = argv[3] ? gnm_fake_floor (value_get_as_float (argv[3])) : start;
1707
1708 if (trials < 0 || p < 0 || p > 1)
1709 return value_new_error_NUM (ei->pos);
1710
1711 return value_new_float (pbinom2 (start, end, trials, p));
1712 }
1713
1714 /***************************************************************************/
1715 static GnmFuncHelp const help_cauchy[] = {
1716 { GNM_FUNC_HELP_NAME, F_("CAUCHY:probability density or cumulative distribution function of the Cauchy, "
1717 "Lorentz or Breit-Wigner distribution")},
1718 { GNM_FUNC_HELP_ARG, F_("x:number")},
1719 { GNM_FUNC_HELP_ARG, F_("a:scale parameter")},
1720 { GNM_FUNC_HELP_ARG, F_("cumulative:whether to evaluate the density function or the cumulative distribution function")},
1721 { GNM_FUNC_HELP_NOTE, F_("If @{a} < 0 this function returns a #NUM! error.") },
1722 { GNM_FUNC_HELP_NOTE, F_("If @{cumulative} is neither TRUE nor FALSE this function returns a #VALUE! error.") },
1723 { GNM_FUNC_HELP_EXAMPLES, "=CAUCHY(0.43,1,TRUE)" },
1724 { GNM_FUNC_HELP_SEEALSO, "RANDCAUCHY"},
1725 { GNM_FUNC_HELP_END }
1726 };
1727
1728 static GnmValue *
1729 gnumeric_cauchy (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1730 {
1731 gnm_float x = value_get_as_float (argv[0]);
1732 gnm_float a = value_get_as_float (argv[1]);
1733 gboolean cuml = value_get_as_checked_bool (argv[2]);
1734
1735 if (a < 0)
1736 return value_new_error_NUM (ei->pos);
1737
1738 if (cuml)
1739 return value_new_float (pcauchy (x, 0, a, FALSE, FALSE));
1740 else
1741 return value_new_float (dcauchy (x, 0, a, FALSE));
1742 }
1743
1744 /***************************************************************************/
1745
1746 static GnmFuncHelp const help_critbinom[] = {
1747 { GNM_FUNC_HELP_NAME, F_("CRITBINOM:right-tailed critical value of the binomial distribution")},
1748 { GNM_FUNC_HELP_ARG, F_("trials:number of trials")},
1749 { GNM_FUNC_HELP_ARG, F_("p:probability of success in each trial")},
1750 { GNM_FUNC_HELP_ARG, F_("alpha:significance level (area of the tail)")},
1751 { GNM_FUNC_HELP_NOTE, F_("If @{trials} is a non-integer it is truncated.") },
1752 { GNM_FUNC_HELP_NOTE, F_("If @{trials} < 0 this function returns a #NUM! error.") },
1753 { GNM_FUNC_HELP_NOTE, F_("If @{p} < 0 or @{p} > 1 this function returns a #NUM! error.") },
1754 { GNM_FUNC_HELP_NOTE, F_("If @{alpha} < 0 or @{alpha} > 1 this function returns a #NUM! error.")},
1755 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1756 { GNM_FUNC_HELP_EXAMPLES, "=CRITBINOM(10,0.5,0.75)" },
1757 { GNM_FUNC_HELP_SEEALSO, "BINOMDIST"},
1758 { GNM_FUNC_HELP_END }
1759 };
1760
1761 static GnmValue *
1762 gnumeric_critbinom (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1763 {
1764 gnm_float trials = gnm_fake_floor (value_get_as_float (argv[0]));
1765 gnm_float p = value_get_as_float (argv[1]);
1766 gnm_float alpha = value_get_as_float (argv[2]);
1767
1768 if (trials < 0 || p < 0 || p > 1 || alpha < 0 || alpha > 1)
1769 return value_new_error_NUM (ei->pos);
1770
1771 return value_new_float (qbinom (alpha, trials, p, TRUE, FALSE));
1772 }
1773
1774 /***************************************************************************/
1775
1776 static GnmFuncHelp const help_permut[] = {
1777 { GNM_FUNC_HELP_NAME, F_("PERMUT:number of @{k}-permutations of a @{n}-set")},
1778 { GNM_FUNC_HELP_ARG, F_("n:size of the base set")},
1779 { GNM_FUNC_HELP_ARG, F_("k:number of elements in each permutation")},
1780 { GNM_FUNC_HELP_NOTE, F_("If @{n} = 0 this function returns a #NUM! error.") },
1781 { GNM_FUNC_HELP_NOTE, F_("If @{n} < @{k} this function returns a #NUM! error.")},
1782 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1783 { GNM_FUNC_HELP_EXAMPLES, "=PERMUT(7,3)" },
1784 { GNM_FUNC_HELP_SEEALSO, "COMBIN"},
1785 { GNM_FUNC_HELP_END }
1786 };
1787
1788 static GnmValue *
1789 gnumeric_permut (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1790 {
1791 gnm_float n = gnm_fake_floor (value_get_as_float (argv[0]));
1792 gnm_float k = gnm_fake_floor (value_get_as_float (argv[1]));
1793
1794 if (0 <= k && k <= n)
1795 return value_new_float (permut (n, k));
1796 else
1797 return value_new_error_NUM (ei->pos);
1798 }
1799
1800 /***************************************************************************/
1801
1802 static GnmFuncHelp const help_hypgeomdist[] = {
1803 { GNM_FUNC_HELP_NAME, F_("HYPGEOMDIST:probability mass or cumulative distribution function of the hypergeometric distribution")},
1804 { GNM_FUNC_HELP_ARG, F_("x:number of successes")},
1805 { GNM_FUNC_HELP_ARG, F_("n:sample size")},
1806 { GNM_FUNC_HELP_ARG, F_("M:number of possible successes in the population")},
1807 { GNM_FUNC_HELP_ARG, F_("N:population size")},
1808 { GNM_FUNC_HELP_ARG, F_("cumulative:whether to evaluate the mass function or the cumulative distribution function")},
1809 { GNM_FUNC_HELP_NOTE, F_("If @{x},@{n},@{M} or @{N} is a non-integer it is truncated.") },
1810 { GNM_FUNC_HELP_NOTE, F_("If @{x},@{n},@{M} or @{N} < 0 this function returns a #NUM! error.") },
1811 { GNM_FUNC_HELP_NOTE, F_("If @{x} > @{M} or @{n} > @{N} this function returns a #NUM! error.")},
1812 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1813 { GNM_FUNC_HELP_EXAMPLES, "=HYPGEOMDIST(1,2,3,10)" },
1814 { GNM_FUNC_HELP_SEEALSO, "BINOMDIST,POISSON"},
1815 { GNM_FUNC_HELP_END }
1816 };
1817
1818 static GnmValue *
1819 gnumeric_hypgeomdist (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1820 {
1821 gnm_float x = gnm_fake_floor (value_get_as_float (argv[0]));
1822 gnm_float n = gnm_fake_floor (value_get_as_float (argv[1]));
1823 gnm_float M = gnm_fake_floor (value_get_as_float (argv[2]));
1824 gnm_float N = gnm_fake_floor (value_get_as_float (argv[3]));
1825 gboolean cum = argv[4] ? value_get_as_checked_bool (argv[4]) : FALSE;
1826
1827 if (x < 0 || n < 0 || M < 0 || N < 0 || x > M || n > N)
1828 return value_new_error_NUM (ei->pos);
1829
1830 if (cum)
1831 return value_new_float (phyper (x, M, N - M, n, TRUE, FALSE));
1832 else
1833 return value_new_float (dhyper (x, M, N - M, n, FALSE));
1834 }
1835
1836 /***************************************************************************/
1837
1838 static GnmFuncHelp const help_confidence[] = {
1839 { GNM_FUNC_HELP_NAME, F_("CONFIDENCE:margin of error of a confidence interval for the population mean")},
1840 { GNM_FUNC_HELP_ARG, F_("alpha:significance level")},
1841 { GNM_FUNC_HELP_ARG, F_("stddev:population standard deviation")},
1842 { GNM_FUNC_HELP_ARG, F_("size:sample size")},
1843 { GNM_FUNC_HELP_NOTE, F_("This function requires the usually unknown population standard deviation.") },
1844 { GNM_FUNC_HELP_NOTE, F_("If @{size} is non-integer it is truncated.") },
1845 { GNM_FUNC_HELP_NOTE, F_("If @{size} < 0 this function returns a #NUM! error.") },
1846 { GNM_FUNC_HELP_NOTE, F_("If @{size} is 0 this function returns a #DIV/0! error.")},
1847 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1848 { GNM_FUNC_HELP_EXAMPLES, "=CONFIDENCE(0.05,1,33)" },
1849 { GNM_FUNC_HELP_SEEALSO, "AVERAGE,CONFIDENCE.T"},
1850 { GNM_FUNC_HELP_END }
1851 };
1852
1853 static GnmValue *
1854 gnumeric_confidence (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1855 {
1856 gnm_float x = value_get_as_float (argv[0]);
1857 gnm_float stddev = value_get_as_float (argv[1]);
1858 gnm_float size = gnm_fake_floor (value_get_as_float (argv[2]));
1859
1860 if (size == 0.)
1861 return value_new_error_DIV0 (ei->pos);
1862 if (size <= 0 || stddev <= 0)
1863 return value_new_error_NUM (ei->pos);
1864
1865 return value_new_float (-qnorm (x / 2, 0, 1, TRUE, FALSE) * (stddev / gnm_sqrt (size)));
1866 }
1867
1868 /***************************************************************************/
1869
1870 static GnmFuncHelp const help_confidence_t[] = {
1871 { GNM_FUNC_HELP_NAME, F_("CONFIDENCE.T:margin of error of a confidence interval for the "
1872 "population mean using the Student's t-distribution")},
1873 { GNM_FUNC_HELP_ARG, F_("alpha:significance level")},
1874 { GNM_FUNC_HELP_ARG, F_("stddev:sample standard deviation")},
1875 { GNM_FUNC_HELP_ARG, F_("size:sample size")},
1876 { GNM_FUNC_HELP_NOTE, F_("If @{stddev} < 0 or = 0 this function returns a #NUM! error.") },
1877 { GNM_FUNC_HELP_NOTE, F_("If @{size} is non-integer it is truncated.") },
1878 { GNM_FUNC_HELP_NOTE, F_("If @{size} < 1 this function returns a #NUM! error.") },
1879 { GNM_FUNC_HELP_NOTE, F_("If @{size} is 1 this function returns a #DIV/0! error.")},
1880 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1881 { GNM_FUNC_HELP_EXAMPLES, "=CONFIDENCE.T(0.05,1,33)" },
1882 { GNM_FUNC_HELP_SEEALSO, "AVERAGE,CONFIDENCE"},
1883 { GNM_FUNC_HELP_END }
1884 };
1885
1886 static GnmValue *
1887 gnumeric_confidence_t (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1888 {
1889 gnm_float x = value_get_as_float (argv[0]);
1890 gnm_float stddev = value_get_as_float (argv[1]);
1891 gnm_float size = gnm_fake_floor (value_get_as_float (argv[2]));
1892
1893 if (size == 1.)
1894 return value_new_error_DIV0 (ei->pos);
1895 if (size <= 1 || stddev <= 0)
1896 return value_new_error_NUM (ei->pos);
1897
1898 return value_new_float (-qt (x / 2, size - 1, TRUE, FALSE) * (stddev / gnm_sqrt (size)));
1899 }
1900
1901 /***************************************************************************/
1902
1903 static GnmFuncHelp const help_standardize[] = {
1904 { GNM_FUNC_HELP_NAME, F_("STANDARDIZE:z-score of a value")},
1905 { GNM_FUNC_HELP_ARG, F_("x:value")},
1906 { GNM_FUNC_HELP_ARG, F_("mean:mean of the original distribution")},
1907 { GNM_FUNC_HELP_ARG, F_("stddev:standard deviation of the original distribution")},
1908 { GNM_FUNC_HELP_NOTE, F_("If @{stddev} is 0 this function returns a #DIV/0! error.")},
1909 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1910 { GNM_FUNC_HELP_EXAMPLES, "=STANDARDIZE(3,2,4)" },
1911 { GNM_FUNC_HELP_SEEALSO, "AVERAGE"},
1912 { GNM_FUNC_HELP_END }
1913 };
1914
1915 static GnmValue *
1916 gnumeric_standardize (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1917 {
1918 gnm_float x = value_get_as_float (argv[0]);
1919 gnm_float mean = value_get_as_float (argv[1]);
1920 gnm_float stddev = value_get_as_float (argv[2]);
1921
1922 if (stddev <= 0)
1923 return value_new_error_NUM (ei->pos);
1924
1925 return value_new_float ((x - mean) / stddev);
1926 }
1927
1928 /***************************************************************************/
1929
1930 static GnmFuncHelp const help_weibull[] = {
1931 { GNM_FUNC_HELP_NAME, F_("WEIBULL:probability density or cumulative distribution function of the Weibull distribution")},
1932 { GNM_FUNC_HELP_ARG, F_("x:number")},
1933 { GNM_FUNC_HELP_ARG, F_("alpha:scale parameter")},
1934 { GNM_FUNC_HELP_ARG, F_("beta:scale parameter")},
1935 { GNM_FUNC_HELP_ARG, F_("cumulative:whether to evaluate the density function or the cumulative distribution function")},
1936 { GNM_FUNC_HELP_DESCRIPTION, F_("If the @{cumulative} boolean is true it will return: "
1937 "1 - exp (-(@{x}/@{beta})^@{alpha}), otherwise it will return "
1938 "(@{alpha}/@{beta}^@{alpha}) * @{x}^(@{alpha}-1) * exp(-(@{x}/@{beta}^@{alpha})).") },
1939 { GNM_FUNC_HELP_NOTE, F_("If @{x} < 0 this function returns a #NUM! error.") },
1940 { GNM_FUNC_HELP_NOTE, F_("If @{alpha} <= 0 or @{beta} <= 0 this function returns a #NUM! error.")},
1941 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1942 { GNM_FUNC_HELP_EXAMPLES, "=WEIBULL(3,2,4,0)" },
1943 { GNM_FUNC_HELP_SEEALSO, "POISSON"},
1944 { GNM_FUNC_HELP_END }
1945 };
1946
1947 static GnmValue *
1948 gnumeric_weibull (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1949 {
1950 gnm_float x = value_get_as_float (argv[0]);
1951 gnm_float alpha = value_get_as_float (argv[1]);
1952 gnm_float beta = value_get_as_float (argv[2]);
1953 gboolean cuml = value_get_as_checked_bool (argv[3]);
1954
1955 if (x < 0 || alpha <= 0 || beta <= 0)
1956 return value_new_error_NUM (ei->pos);
1957
1958 if (cuml)
1959 return value_new_float (pweibull (x, alpha, beta, TRUE, FALSE));
1960 else
1961 return value_new_float (dweibull (x, alpha, beta, FALSE));
1962 }
1963
1964 /***************************************************************************/
1965
1966 static GnmFuncHelp const help_normdist[] = {
1967 { GNM_FUNC_HELP_NAME, F_("NORMDIST:probability density or cumulative distribution function of a normal distribution")},
1968 { GNM_FUNC_HELP_ARG, F_("x:number")},
1969 { GNM_FUNC_HELP_ARG, F_("mean:mean of the distribution")},
1970 { GNM_FUNC_HELP_ARG, F_("stddev:standard deviation of the distribution")},
1971 { GNM_FUNC_HELP_ARG, F_("cumulative:whether to evaluate the density function or the cumulative distribution function")},
1972 { GNM_FUNC_HELP_NOTE, F_("If @{stddev} is 0 this function returns a #DIV/0! error.")},
1973 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
1974 { GNM_FUNC_HELP_EXAMPLES, "=NORMDIST(2,1,2,0)" },
1975 { GNM_FUNC_HELP_SEEALSO, "POISSON"},
1976 { GNM_FUNC_HELP_END }
1977 };
1978
1979
1980 static GnmValue *
1981 gnumeric_normdist (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1982 {
1983 gnm_float x = value_get_as_float (argv[0]);
1984 gnm_float mean = value_get_as_float (argv[1]);
1985 gnm_float stddev = value_get_as_float (argv[2]);
1986 gboolean cuml = value_get_as_checked_bool (argv[3]);
1987
1988 if (stddev <= 0)
1989 return value_new_error_NUM (ei->pos);
1990
1991 if (cuml)
1992 return value_new_float (pnorm (x, mean, stddev, TRUE, FALSE));
1993 else
1994 return value_new_float (dnorm (x, mean, stddev, FALSE));
1995 }
1996
1997 /***************************************************************************/
1998
1999 static GnmFuncHelp const help_norminv[] = {
2000 { GNM_FUNC_HELP_NAME, F_("NORMINV:inverse of the cumulative distribution function of a normal distribution")},
2001 { GNM_FUNC_HELP_ARG, F_("p:probability")},
2002 { GNM_FUNC_HELP_ARG, F_("mean:mean of the distribution")},
2003 { GNM_FUNC_HELP_ARG, F_("stddev:standard deviation of the distribution")},
2004 { GNM_FUNC_HELP_NOTE, F_("If @{p} < 0 or @{p} > 1 or @{stddev} <= 0 this function returns a #NUM! error.")},
2005 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
2006 { GNM_FUNC_HELP_EXAMPLES, "=NORMINV(0.76,2,3)" },
2007 { GNM_FUNC_HELP_SEEALSO, "NORMDIST,NORMSDIST,NORMSINV,STANDARDIZE,ZTEST"},
2008 { GNM_FUNC_HELP_END }
2009 };
2010
2011 static GnmValue *
2012 gnumeric_norminv (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2013 {
2014 gnm_float p = value_get_as_float (argv[0]);
2015 gnm_float mean = value_get_as_float (argv[1]);
2016 gnm_float stddev = value_get_as_float (argv[2]);
2017
2018 if (p < 0 || p > 1 || stddev <= 0)
2019 return value_new_error_NUM (ei->pos);
2020
2021 return value_new_float (qnorm (p, mean, stddev, TRUE, FALSE));
2022 }
2023
2024
2025 /***************************************************************************/
2026
2027 static GnmFuncHelp const help_kurt[] = {
2028 { GNM_FUNC_HELP_NAME, F_("KURT:unbiased estimate of the kurtosis of a data set")},
2029 { GNM_FUNC_HELP_ARG, F_("number1:first value")},
2030 { GNM_FUNC_HELP_ARG, F_("number2:second value")},
2031 { GNM_FUNC_HELP_DESCRIPTION, F_("Strings and empty cells are simply ignored.") },
2032 { GNM_FUNC_HELP_NOTE, F_("This is only meaningful if the underlying "
2033 "distribution really has a fourth moment. The kurtosis is "
2034 "offset by three such that a normal distribution will have zero "
2035 "kurtosis.") },
2036 { GNM_FUNC_HELP_NOTE, F_("If fewer than four numbers are given or all of them are equal "
2037 "this function returns a #DIV/0! error.")},
2038 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
2039 { GNM_FUNC_HELP_EXAMPLES, "=KURT(11.4,17.3,21.3,25.9,40.1)" },
2040 { GNM_FUNC_HELP_SEEALSO, "AVERAGE,VAR,SKEW,KURTP"},
2041 { GNM_FUNC_HELP_END }
2042 };
2043
2044 static GnmValue *
2045 gnumeric_kurt (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
2046 {
2047 return float_range_function (argc, argv, ei,
2048 gnm_range_kurtosis_m3_est,
2049 COLLECT_IGNORE_STRINGS |
2050 COLLECT_IGNORE_BOOLS |
2051 COLLECT_IGNORE_BLANKS,
2052 GNM_ERROR_DIV0);
2053 }
2054
2055 /***************************************************************************/
2056
2057 static GnmFuncHelp const help_kurtp[] = {
2058 { GNM_FUNC_HELP_NAME, F_("KURTP:population kurtosis of a data set")},
2059 { GNM_FUNC_HELP_ARG, F_("number1:first value")},
2060 { GNM_FUNC_HELP_ARG, F_("number2:second value")},
2061 { GNM_FUNC_HELP_DESCRIPTION, F_("Strings and empty cells are simply ignored.") },
2062 { GNM_FUNC_HELP_NOTE, F_("If fewer than two numbers are given or all of them are equal "
2063 "this function returns a #DIV/0! error.") },
2064 { GNM_FUNC_HELP_EXAMPLES, "=KURTP(11.4,17.3,21.3,25.9,40.1)" },
2065 { GNM_FUNC_HELP_SEEALSO, "AVERAGE,VARP,SKEWP,KURT"},
2066 { GNM_FUNC_HELP_END }
2067 };
2068
2069 static GnmValue *
2070 gnumeric_kurtp (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
2071 {
2072 return float_range_function (argc, argv, ei,
2073 gnm_range_kurtosis_m3_pop,
2074 COLLECT_IGNORE_STRINGS |
2075 COLLECT_IGNORE_BOOLS |
2076 COLLECT_IGNORE_BLANKS,
2077 GNM_ERROR_DIV0);
2078 }
2079
2080 /***************************************************************************/
2081
2082 static GnmFuncHelp const help_avedev[] = {
2083 { GNM_FUNC_HELP_NAME, F_("AVEDEV:average of the absolute deviations of a data set")},
2084 { GNM_FUNC_HELP_ARG, F_("number1:first value")},
2085 { GNM_FUNC_HELP_ARG, F_("number2:second value")},
2086 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
2087 { GNM_FUNC_HELP_EXAMPLES, "=AVEDEV(11.4,17.3,21.3,25.9,40.1)" },
2088 { GNM_FUNC_HELP_SEEALSO, "STDEV"},
2089 { GNM_FUNC_HELP_END }
2090 };
2091
2092 static GnmValue *
2093 gnumeric_avedev (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
2094 {
2095 return float_range_function (argc, argv, ei,
2096 gnm_range_avedev,
2097 COLLECT_IGNORE_STRINGS |
2098 COLLECT_IGNORE_BOOLS |
2099 COLLECT_IGNORE_BLANKS,
2100 GNM_ERROR_NUM);
2101 }
2102
2103 /***************************************************************************/
2104
2105 static GnmFuncHelp const help_devsq[] = {
2106 { GNM_FUNC_HELP_NAME, F_("DEVSQ:sum of squares of deviations of a data set")},
2107 { GNM_FUNC_HELP_ARG, F_("number1:first value")},
2108 { GNM_FUNC_HELP_ARG, F_("number2:second value")},
2109 { GNM_FUNC_HELP_DESCRIPTION, F_("Strings and empty cells are simply ignored.")},
2110 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
2111 { GNM_FUNC_HELP_EXAMPLES, "=DEVSQ(11.4,17.3,21.3,25.9,40.1)" },
2112 { GNM_FUNC_HELP_SEEALSO, "STDEV"},
2113 { GNM_FUNC_HELP_END }
2114 };
2115
2116 static GnmValue *
2117 gnumeric_devsq (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
2118 {
2119 return float_range_function (argc, argv, ei,
2120 gnm_range_devsq,
2121 COLLECT_IGNORE_STRINGS |
2122 COLLECT_IGNORE_BOOLS |
2123 COLLECT_IGNORE_BLANKS,
2124 GNM_ERROR_VALUE);
2125 }
2126
2127 /***************************************************************************/
2128
2129 static GnmFuncHelp const help_fisher[] = {
2130 { GNM_FUNC_HELP_NAME, F_("FISHER:Fisher transformation")},
2131 { GNM_FUNC_HELP_ARG, F_("x:number")},
2132 { GNM_FUNC_HELP_NOTE, F_("If @{x} is not a number, this function returns a #VALUE! error.") },
2133 { GNM_FUNC_HELP_NOTE, F_("If @{x} <= -1 or @{x} >= 1, this function returns a #NUM! error.")},
2134 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
2135 { GNM_FUNC_HELP_EXAMPLES, "=FISHER(0.332)" },
2136 { GNM_FUNC_HELP_SEEALSO, "FISHERINV,ATANH"},
2137 { GNM_FUNC_HELP_END }
2138 };
2139
2140 static GnmValue *
2141 gnumeric_fisher (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2142 {
2143 gnm_float x = value_get_as_float (argv[0]);
2144
2145 if (x <= -1.0 || x >= 1.0)
2146 return value_new_error_NUM (ei->pos);
2147
2148 return value_new_float (gnm_atanh (x));
2149 }
2150
2151 /***************************************************************************/
2152
2153 static GnmFuncHelp const help_poisson[] = {
2154 { GNM_FUNC_HELP_NAME, F_("POISSON:probability mass or cumulative distribution function of the Poisson distribution")},
2155 { GNM_FUNC_HELP_ARG, F_("x:number of events")},
2156 { GNM_FUNC_HELP_ARG, F_("mean:mean of the distribution")},
2157 { GNM_FUNC_HELP_ARG, F_("cumulative:whether to evaluate the mass function or the cumulative distribution function")},
2158 { GNM_FUNC_HELP_NOTE, F_("If @{x} is a non-integer it is truncated.") },
2159 { GNM_FUNC_HELP_NOTE, F_("If @{x} < 0 this function returns a #NUM! error.") },
2160 { GNM_FUNC_HELP_NOTE, F_("If @{mean} <= 0 POISSON returns the #NUM! error.")},
2161 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
2162 { GNM_FUNC_HELP_EXAMPLES, "=POISSON(3,6,0)"},
2163 { GNM_FUNC_HELP_SEEALSO, ("NORMDIST,WEIBULL")},
2164 { GNM_FUNC_HELP_END }
2165 };
2166
2167 static GnmValue *
2168 gnumeric_poisson (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2169 {
2170 gnm_float x = gnm_fake_floor (value_get_as_float (argv[0]));
2171 gnm_float mean = value_get_as_float (argv[1]);
2172 gboolean cuml = value_get_as_checked_bool (argv[2]);
2173
2174 if (x < 0 || mean <= 0)
2175 return value_new_error_NUM (ei->pos);
2176
2177 if (cuml)
2178 return value_new_float (ppois (x, mean, TRUE, FALSE));
2179 else
2180 return value_new_float (dpois (x, mean, FALSE));
2181 }
2182
2183 /***************************************************************************/
2184
2185 static GnmFuncHelp const help_pearson[] = {
2186 { GNM_FUNC_HELP_NAME, F_("PEARSON:Pearson correlation coefficient of the paired set of data")},
2187 { GNM_FUNC_HELP_ARG, F_("array1:first component values")},
2188 { GNM_FUNC_HELP_ARG, F_("array2:second component values")},
2189 { GNM_FUNC_HELP_DESCRIPTION, F_("Strings and empty cells are simply ignored.")},
2190 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
2191 { GNM_FUNC_HELP_EXAMPLES, "=PEARSON({1,2,3},{3,4,4})" },
2192 { GNM_FUNC_HELP_SEEALSO, "INTERCEPT,LINEST,RSQ,SLOPE,STEYX"},
2193 { GNM_FUNC_HELP_END }
2194 };
2195
2196 static GnmValue *
2197 gnumeric_pearson (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2198 {
2199 return gnumeric_correl (ei, argv);
2200 }
2201
2202
2203 /***************************************************************************/
2204
2205 static GnmFuncHelp const help_rsq[] = {
2206 { GNM_FUNC_HELP_NAME, F_("RSQ:square of the Pearson correlation coefficient of the paired set of data")},
2207 { GNM_FUNC_HELP_ARG, F_("array1:first component values")},
2208 { GNM_FUNC_HELP_ARG, F_("array2:second component values")},
2209 { GNM_FUNC_HELP_DESCRIPTION, F_("Strings and empty cells are simply ignored.")},
2210 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
2211 { GNM_FUNC_HELP_EXAMPLES, "=RSQ({1,2,3},{3,4,4})" },
2212 { GNM_FUNC_HELP_SEEALSO, ("CORREL,COVAR,INTERCEPT,LINEST,LOGEST,PEARSON,SLOPE,STEYX,TREND")},
2213 { GNM_FUNC_HELP_END }
2214 };
2215
2216 static GnmValue *
2217 gnumeric_rsq (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2218 {
2219 return float_range_function2 (argv[0], argv[1],
2220 ei,
2221 gnm_range_rsq_pop,
2222 COLLECT_IGNORE_BLANKS |
2223 COLLECT_IGNORE_STRINGS |
2224 COLLECT_IGNORE_BOOLS,
2225 GNM_ERROR_VALUE);
2226 }
2227
2228 /***************************************************************************/
2229
2230 static GnmFuncHelp const help_median[] = {
2231 { GNM_FUNC_HELP_NAME, F_("MEDIAN:median of a data set")},
2232 { GNM_FUNC_HELP_ARG, F_("number1:first value")},
2233 { GNM_FUNC_HELP_ARG, F_("number2:second value")},
2234 { GNM_FUNC_HELP_DESCRIPTION, F_("Strings and empty cells are simply ignored.") },
2235 { GNM_FUNC_HELP_NOTE, F_("If even numbers are given MEDIAN returns the average of the two "
2236 "numbers in the center.")},
2237 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
2238 { GNM_FUNC_HELP_EXAMPLES, "=MEDIAN(11.4,17.3,11.4,3,25.9,40.1)" },
2239 { GNM_FUNC_HELP_SEEALSO, "AVERAGE,COUNT,COUNTA,DAVERAGE,MODE,SSMEDIAN,SUM"},
2240 { GNM_FUNC_HELP_EXTREF, F_("wiki:en:Median") },
2241 { GNM_FUNC_HELP_EXTREF, F_("wolfram:StatisticalMedian.html") },
2242 { GNM_FUNC_HELP_END }
2243 };
2244
2245 static GnmValue *
2246 gnumeric_median (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
2247 {
2248 return float_range_function (argc, argv, ei,
2249 gnm_range_median_inter_sorted,
2250 COLLECT_IGNORE_STRINGS |
2251 COLLECT_IGNORE_BOOLS |
2252 COLLECT_IGNORE_BLANKS |
2253 COLLECT_SORT,
2254 GNM_ERROR_NUM);
2255 }
2256
2257 /***************************************************************************/
2258
2259 static GnmFuncHelp const help_ssmedian[] = {
2260 { GNM_FUNC_HELP_NAME, F_("SSMEDIAN:median for grouped data")},
2261 { GNM_FUNC_HELP_ARG, F_("array:data set")},
2262 { GNM_FUNC_HELP_ARG, F_("interval:length of each grouping interval, defaults to 1")},
2263 { GNM_FUNC_HELP_DESCRIPTION, F_("The data are assumed to be grouped into intervals of width @{interval}. "
2264 "Each data point in @{array} is the midpoint of the interval containing the true value. "
2265 "The median is calculated by interpolation within the median interval "
2266 "(the interval containing the median value), "
2267 "assuming that the true values within that interval are distributed uniformly:\n"
2268 "median = L + @{interval}*(N/2 - CF)/F\n"
2269 "where:\n"
2270 "L = the lower limit of the median interval\n"
2271 "N = the total number of data points\n"
2272 "CF = the number of data points below the median interval\n"
2273 "F = the number of data points in the median interval") },
2274 { GNM_FUNC_HELP_NOTE, F_("If @{array} is empty, this function returns a #NUM! error.") },
2275 { GNM_FUNC_HELP_NOTE, F_("If @{interval} <= 0, this function returns a #NUM! error. "
2276 "SSMEDIAN does not check whether the data points are "
2277 "at least @{interval} apart.") },
2278 { GNM_FUNC_HELP_EXAMPLES, "=SSMEDIAN(ARRAY(7,7,8,9), 1)" },
2279 { GNM_FUNC_HELP_EXAMPLES, "=SSMEDIAN(ARRAY(7,7,8,8,9), 1)" },
2280 { GNM_FUNC_HELP_EXAMPLES, "=SSMEDIAN(ARRAY(7,7,8,8,8,9), 1)" },
2281 { GNM_FUNC_HELP_SEEALSO, "MEDIAN"},
2282 { GNM_FUNC_HELP_END }
2283 };
2284
2285 static gnm_float
2286 gnumeric_ssmedian_calc (gnm_float const *sorted_data, int len,
2287 gnm_float mid_val, gnm_float interval)
2288 {
2289 gnm_float L_lower = mid_val - interval / 2;
2290 gnm_float L_upper = mid_val + interval / 2;
2291 int f_below = 0;
2292 int f_within = 0;
2293 int i;
2294
2295 for (i = 0; i < len; i++) {
2296 if (*sorted_data < L_lower)
2297 f_below++;
2298 else if (*sorted_data <= L_upper)
2299 f_within++;
2300 else
2301 break;
2302 sorted_data++;
2303 }
2304
2305 return L_lower + (len / 2e0 - f_below) * interval / f_within;
2306 }
2307
2308 static GnmValue *
2309 gnumeric_ssmedian (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2310 {
2311 gnm_float *data;
2312 GnmValue *result = NULL;
2313 gnm_float interval;
2314 int n;
2315
2316 data = collect_floats_value (argv[0], ei->pos,
2317 COLLECT_IGNORE_STRINGS |
2318 COLLECT_IGNORE_BOOLS |
2319 COLLECT_IGNORE_BLANKS |
2320 COLLECT_SORT,
2321 &n, &result);
2322 if (result)
2323 goto done;
2324 interval = argv[1] ? value_get_as_float (argv[1]) : 1.0;
2325
2326 if (interval <= 0 || n == 0) {
2327 result = value_new_error_NUM (ei->pos);
2328 goto done;
2329 }
2330
2331 switch (n) {
2332 case (1):
2333 result = value_new_float (data[0]);
2334 break;
2335 case (2):
2336 result = value_new_float ((data[0] + data[1]) / 2);
2337 break;
2338 default:
2339 if (n % 2 == 0) {
2340 gnm_float m1 = data[n / 2];
2341 gnm_float m0 = data[n / 2 - 1];
2342 result = value_new_float
2343 (m0 == m1
2344 ? gnumeric_ssmedian_calc (data, n,
2345 m1, interval)
2346 : (m0 + m1) / 2);
2347 } else {
2348 result = value_new_float
2349 (gnumeric_ssmedian_calc
2350 (data, n, data[n / 2], interval));
2351 }
2352 }
2353
2354 done:
2355 g_free (data);
2356 return result;
2357 }
2358
2359 /***************************************************************************/
2360
2361 static GnmFuncHelp const help_large[] = {
2362 { GNM_FUNC_HELP_NAME, F_("LARGE:@{k}-th largest value in a data set")},
2363 { GNM_FUNC_HELP_ARG, F_("data:data set")},
2364 { GNM_FUNC_HELP_ARG, F_("k:which value to find")},
2365 { GNM_FUNC_HELP_NOTE, F_("If data set is empty this function returns a #NUM! error.") },
2366 { GNM_FUNC_HELP_NOTE, F_("If @{k} <= 0 or @{k} is greater than the number of data items given "
2367 "this function returns a #NUM! error.")},
2368 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
2369 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5 contain numbers "
2370 "11.4, 17.3, 21.3, 25.9, and 40.1.") },
2371 { GNM_FUNC_HELP_EXAMPLES, F_("Then LARGE(A1:A5,2) equals 25.9. "
2372 "LARGE(A1:A5,4) equals 17.3.") },
2373 { GNM_FUNC_HELP_SEEALSO, "PERCENTILE,PERCENTRANK,QUARTILE,SMALL"},
2374 { GNM_FUNC_HELP_END }
2375 };
2376
2377 static GnmValue *
2378 gnumeric_large (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2379 {
2380 int n;
2381 GnmValue *res = NULL;
2382 gnm_float *xs = collect_floats_value (argv[0], ei->pos,
2383 COLLECT_IGNORE_STRINGS |
2384 COLLECT_IGNORE_BOOLS |
2385 COLLECT_IGNORE_BLANKS |
2386 COLLECT_SORT,
2387 &n, &res);
2388 gnm_float k = value_get_as_float (argv[1]);
2389 if (res)
2390 return res;
2391
2392 k = gnm_fake_ceil (k);
2393 if (k >= 1 && k <= n)
2394 res = value_new_float (xs[n - (int)k]);
2395 else
2396 res = value_new_error_NUM (ei->pos);
2397
2398 g_free (xs);
2399 return res;
2400 }
2401
2402 /***************************************************************************/
2403
2404 static GnmFuncHelp const help_small[] = {
2405 { GNM_FUNC_HELP_NAME, F_("SMALL:@{k}-th smallest value in a data set")},
2406 { GNM_FUNC_HELP_ARG, F_("data:data set")},
2407 { GNM_FUNC_HELP_ARG, F_("k:which value to find")},
2408 { GNM_FUNC_HELP_NOTE, F_("If data set is empty this function returns a #NUM! error.") },
2409 { GNM_FUNC_HELP_NOTE, F_("If @{k} <= 0 or @{k} is greater than the number of data items given "
2410 "this function returns a #NUM! error.")},
2411 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
2412 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5 contain numbers "
2413 "11.4, 17.3, 21.3, 25.9, and 40.1.") },
2414 { GNM_FUNC_HELP_EXAMPLES, F_("Then SMALL(A1:A5,2) equals 17.3. "
2415 "SMALL(A1:A5,4) equals 25.9.") },
2416 { GNM_FUNC_HELP_SEEALSO, "PERCENTILE,PERCENTRANK,QUARTILE,LARGE"},
2417 { GNM_FUNC_HELP_END }
2418 };
2419
2420
2421 static GnmValue *
2422 gnumeric_small (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2423 {
2424 int n;
2425 GnmValue *res = NULL;
2426 gnm_float *xs = collect_floats_value (argv[0], ei->pos,
2427 COLLECT_IGNORE_STRINGS |
2428 COLLECT_IGNORE_BOOLS |
2429 COLLECT_IGNORE_BLANKS |
2430 COLLECT_SORT,
2431 &n, &res);
2432 gnm_float k = value_get_as_float (argv[1]);
2433 if (res)
2434 return res;
2435
2436 k = gnm_fake_ceil (k);
2437 if (k >= 1 && k <= n)
2438 res = value_new_float (xs[(int)k - 1]);
2439 else
2440 res = value_new_error_NUM (ei->pos);
2441
2442 g_free (xs);
2443 return res;
2444 }
2445
2446 /***********************************************************************/
2447
2448 static GnmFuncHelp const help_prob[] = {
2449 { GNM_FUNC_HELP_NAME, F_("PROB:probability of an interval for a discrete (and finite) probability distribution")},
2450 { GNM_FUNC_HELP_ARG, F_("x_range:possible values")},
2451 { GNM_FUNC_HELP_ARG, F_("prob_range:probabilities of the corresponding values")},
2452 { GNM_FUNC_HELP_ARG, F_("lower_limit:lower interval limit")},
2453 { GNM_FUNC_HELP_ARG, F_("upper_limit:upper interval limit, defaults to @{lower_limit}")},
2454 { GNM_FUNC_HELP_NOTE, F_("If the sum of the probabilities in @{prob_range} is not equal to 1 "
2455 "this function returns a #NUM! error.") },
2456 { GNM_FUNC_HELP_NOTE, F_("If any value in @{prob_range} is <=0 or > 1, this function returns a #NUM! "
2457 "error.") },
2458 { GNM_FUNC_HELP_NOTE, F_("If @{x_range} and @{prob_range} contain a different number of data "
2459 "entries, this function returns a #N/A error.")},
2460 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
2461 { GNM_FUNC_HELP_SEEALSO, "BINOMDIST,CRITBINOM"},
2462 { GNM_FUNC_HELP_END }
2463 };
2464
2465 static GnmValue *
2466 gnumeric_prob (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2467 {
2468 GnmValue *res;
2469 GnmValue *error = NULL;
2470 int i, prob_n, x_n;
2471 gnm_float *prob_vals = NULL, *x_vals = NULL;
2472 gnm_float lower_limit, upper_limit;
2473 gnm_float total_sum = 0, sum = 0;
2474
2475 lower_limit = value_get_as_float (argv[2]);
2476 upper_limit = argv[3] ? value_get_as_float (argv[3]) : lower_limit;
2477
2478 x_vals = collect_floats_value
2479 (argv[0], ei->pos,
2480 COLLECT_IGNORE_STRINGS | COLLECT_IGNORE_BOOLS |
2481 COLLECT_IGNORE_BLANKS,
2482 &x_n, &error);
2483 if (error) {
2484 res = error;
2485 goto out;
2486 }
2487
2488 prob_vals = collect_floats_value
2489 (argv[1], ei->pos,
2490 COLLECT_IGNORE_STRINGS | COLLECT_IGNORE_BOOLS |
2491 COLLECT_IGNORE_BLANKS,
2492 &prob_n, &error);
2493 if (error) {
2494 res = error;
2495 goto out;
2496 }
2497
2498 if (x_n != prob_n) {
2499 res = value_new_error_NA (ei->pos);
2500 goto out;
2501 }
2502
2503 for (i = 0; i < x_n; i++) {
2504 gnm_float x = x_vals[i];
2505 gnm_float prob = prob_vals[i];
2506
2507 /* FIXME: Check "0" behaviour with Excel and comment. */
2508 if (prob <= 0 || prob > 1) {
2509 res = value_new_error_NUM (ei->pos);
2510 goto out;
2511 }
2512
2513 total_sum += prob;
2514
2515 if (x >= lower_limit && x <= upper_limit)
2516 sum += prob;
2517 }
2518
2519 if (gnm_abs (total_sum - 1) > x_n * 2 * GNM_EPSILON) {
2520 res = value_new_error_NUM (ei->pos);
2521 goto out;
2522 }
2523
2524 res = value_new_float (sum);
2525
2526 out:
2527 g_free (x_vals);
2528 g_free (prob_vals);
2529 return res;
2530 }
2531
2532 /***************************************************************************/
2533
2534 static GnmFuncHelp const help_steyx[] = {
2535 { GNM_FUNC_HELP_NAME, F_("STEYX:standard error of the predicted y-value in the regression")},
2536 { GNM_FUNC_HELP_ARG, F_("known_ys:known y-values")},
2537 { GNM_FUNC_HELP_ARG, F_("known_xs:known x-values")},
2538 { GNM_FUNC_HELP_NOTE, F_("If @{known_ys} and @{known_xs} are empty or have a different "
2539 "number of arguments then this function returns a #N/A error.")},
2540 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
2541 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5 contain numbers "
2542 "11.4, 17.3, 21.3, 25.9, and 40.1, and the cells B1, B2, ... "
2543 "B5 23.2, 25.8, 29.9, 33.5, and 42.7.") },
2544 { GNM_FUNC_HELP_EXAMPLES, F_("Then STEYX(A1:A5,B1:B5) equals 1.101509979.") },
2545 { GNM_FUNC_HELP_SEEALSO, "PEARSON,RSQ,SLOPE"},
2546 { GNM_FUNC_HELP_END }
2547 };
2548
2549 static int
2550 range_steyx (gnm_float const *xs, gnm_float const *ys, int n, gnm_float *res)
2551 {
2552 gnm_float linres[2];
2553 int dim = 1;
2554 GORegressionResult regres;
2555 gnm_regression_stat_t *extra_stat;
2556 gboolean affine = TRUE;
2557
2558 extra_stat = gnm_regression_stat_new ();
2559 regres = gnm_linear_regression ((gnm_float **)&xs, dim,
2560 ys, n, affine, linres, extra_stat);
2561 *res = gnm_sqrt (extra_stat->var);
2562 gnm_regression_stat_destroy (extra_stat);
2563
2564 switch (regres) {
2565 case GO_REG_ok:
2566 case GO_REG_near_singular_good:
2567 return 0;
2568 default:
2569 return 1;
2570 }
2571 }
2572
2573 static GnmValue *
2574 gnumeric_steyx (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2575 {
2576 return float_range_function2 (argv[1], argv[0],
2577 ei,
2578 range_steyx,
2579 COLLECT_IGNORE_BLANKS |
2580 COLLECT_IGNORE_STRINGS |
2581 COLLECT_IGNORE_BOOLS,
2582 GNM_ERROR_VALUE);
2583 }
2584
2585 /***************************************************************************/
2586
2587 static GnmFuncHelp const help_ztest[] = {
2588 { GNM_FUNC_HELP_NAME, F_("ZTEST:the probability of observing a sample mean as large as "
2589 "or larger than the mean of the given sample")},
2590 { GNM_FUNC_HELP_ARG, F_("ref:data set (sample)")},
2591 { GNM_FUNC_HELP_ARG, F_("x:population mean")},
2592 { GNM_FUNC_HELP_ARG, F_("stddev:population standard deviation, defaults to the sample standard deviation")},
2593 { GNM_FUNC_HELP_DESCRIPTION, F_("ZTEST calculates the probability of observing a sample mean as large as "
2594 "or larger than the mean of the given sample for samples drawn "
2595 "from a normal distribution with mean @{x} and standard deviation @{stddev}.")},
2596 { GNM_FUNC_HELP_NOTE, F_("If @{ref} contains less than two data items ZTEST "
2597 "returns #DIV/0! error.")},
2598 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
2599 { GNM_FUNC_HELP_ODF, F_("This function is OpenFormula compatible.") },
2600 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5 contain numbers "
2601 "11.4, 17.3, 21.3, 25.9, and 40.1.") },
2602 { GNM_FUNC_HELP_EXAMPLES, F_("Then ZTEST(A1:A5,20) equals 0.254717826.")},
2603 { GNM_FUNC_HELP_SEEALSO, ("CONFIDENCE,NORMDIST,NORMINV,NORMSDIST,NORMSINV,STANDARDIZE")},
2604 { GNM_FUNC_HELP_END }
2605 };
2606
2607 static GnmValue *
2608 gnumeric_ztest (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2609 {
2610 int n;
2611 gnm_float *xs;
2612 GnmValue *result = NULL;
2613 gnm_float x, s, m, p;
2614
2615 xs = collect_floats_value (argv[0], ei->pos,
2616 COLLECT_IGNORE_STRINGS |
2617 COLLECT_IGNORE_BOOLS |
2618 COLLECT_IGNORE_BLANKS,
2619 &n, &result);
2620 if (result)
2621 goto done;
2622
2623 x = value_get_as_float (argv[1]);
2624
2625 if (gnm_range_average (xs, n, &m)) {
2626 result = value_new_error_DIV0 (ei->pos);
2627 goto done;
2628 }
2629
2630 if (argv[2])
2631 s = value_get_as_float (argv[2]);
2632 else if (gnm_range_stddev_est (xs, n, &s)) {
2633 result = value_new_error_DIV0 (ei->pos);
2634 goto done;
2635 }
2636
2637 if (s <= 0) {
2638 result = value_new_error_DIV0 (ei->pos);
2639 goto done;
2640 }
2641
2642 p = pnorm (x, m, s / gnm_sqrt (n), TRUE, FALSE);
2643 result = value_new_float (p);
2644
2645 done:
2646 g_free (xs);
2647 return result;
2648 }
2649
2650 /***************************************************************************/
2651
2652 static GnmFuncHelp const help_averagea[] = {
2653 { GNM_FUNC_HELP_NAME, F_("AVERAGEA:average of all the values and cells")},
2654 { GNM_FUNC_HELP_ARG, F_("number1:first value")},
2655 { GNM_FUNC_HELP_ARG, F_("number2:second value")},
2656 HELP_DESCRIPTION_TEXT_INCLUSION,
2657 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
2658 { GNM_FUNC_HELP_EXAMPLES, "=AVERAGE(11.4,17.3,21.3,3,25.9,40.1)" },
2659 { GNM_FUNC_HELP_SEEALSO, "AVERAGE"},
2660 { GNM_FUNC_HELP_END }
2661 };
2662
2663 static GnmValue *
2664 gnumeric_averagea (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
2665 {
2666 return float_range_function (argc, argv, ei,
2667 gnm_range_average,
2668 COLLECT_ZERO_STRINGS |
2669 COLLECT_ZEROONE_BOOLS |
2670 COLLECT_IGNORE_BLANKS,
2671 GNM_ERROR_DIV0);
2672 }
2673
2674 /***************************************************************************/
2675
2676 static GnmFuncHelp const help_maxa[] = {
2677 { GNM_FUNC_HELP_NAME, F_("MAXA:largest value, with negative numbers considered smaller than positive numbers")},
2678 { GNM_FUNC_HELP_ARG, F_("number1:first value")},
2679 { GNM_FUNC_HELP_ARG, F_("number2:second value")},
2680 HELP_DESCRIPTION_TEXT_INCLUSION,
2681 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
2682 { GNM_FUNC_HELP_EXAMPLES, "=MAXA(11.4,17.3,21.3,3,25.9,40.1)" },
2683 { GNM_FUNC_HELP_SEEALSO, "MAX,MINA"},
2684 { GNM_FUNC_HELP_END }
2685 };
2686
2687 static GnmValue *
2688 gnumeric_maxa (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
2689 {
2690 return float_range_function (argc, argv, ei,
2691 range_max0,
2692 COLLECT_ZERO_STRINGS |
2693 COLLECT_ZEROONE_BOOLS |
2694 COLLECT_IGNORE_BLANKS |
2695 COLLECT_ORDER_IRRELEVANT,
2696 GNM_ERROR_VALUE);
2697 }
2698
2699 /***************************************************************************/
2700
2701 static GnmFuncHelp const help_mina[] = {
2702 { GNM_FUNC_HELP_NAME, F_("MINA:smallest value, with negative numbers considered smaller than positive numbers")},
2703 { GNM_FUNC_HELP_ARG, F_("number1:first value")},
2704 { GNM_FUNC_HELP_ARG, F_("number2:second value")},
2705 HELP_DESCRIPTION_TEXT_INCLUSION,
2706 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
2707 { GNM_FUNC_HELP_EXAMPLES, "=MINA(11.4,17.3,21.3,3,25.9,40.1)" },
2708 { GNM_FUNC_HELP_SEEALSO, "MIN,MAXA"},
2709 { GNM_FUNC_HELP_END }
2710 };
2711
2712 static GnmValue *
2713 gnumeric_mina (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
2714 {
2715 return float_range_function (argc, argv, ei,
2716 range_min0,
2717 COLLECT_ZERO_STRINGS |
2718 COLLECT_ZEROONE_BOOLS |
2719 COLLECT_IGNORE_BLANKS |
2720 COLLECT_ORDER_IRRELEVANT,
2721 GNM_ERROR_VALUE);
2722 }
2723
2724 /***************************************************************************/
2725
2726 static GnmFuncHelp const help_vara[] = {
2727 { GNM_FUNC_HELP_NAME, F_("VARA:sample variance of the given sample")},
2728 { GNM_FUNC_HELP_ARG, F_("area1:first cell area")},
2729 { GNM_FUNC_HELP_ARG, F_("area2:second cell area")},
2730 { GNM_FUNC_HELP_DESCRIPTION, F_("VARA is also known as the N-1-variance.")},
2731 { GNM_FUNC_HELP_DESCRIPTION, F_("To get the true variance of a complete population use VARPA.") },
2732 HELP_DESCRIPTION_TEXT_INCLUSION,
2733 { GNM_FUNC_HELP_NOTE, F_("Since the N-1-variance includes Bessel's correction, whereas the N-variance calculated by VARPA or VARP does not, "
2734 "under reasonable conditions the N-1-variance is an unbiased estimator of the variance of the population "
2735 "from which the sample is drawn.")},
2736 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
2737 { GNM_FUNC_HELP_EXAMPLES, "=VARA(11.4,17.3,21.3,3,25.9,40.1)" },
2738 { GNM_FUNC_HELP_SEEALSO, "VAR,VARPA"},
2739 { GNM_FUNC_HELP_END }
2740 };
2741
2742 static GnmValue *
2743 gnumeric_vara (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
2744 {
2745 return float_range_function (argc, argv, ei,
2746 gnm_range_var_est,
2747 COLLECT_ZERO_STRINGS |
2748 COLLECT_ZEROONE_BOOLS |
2749 COLLECT_IGNORE_BLANKS,
2750 GNM_ERROR_VALUE);
2751 }
2752
2753 /***************************************************************************/
2754
2755 static GnmFuncHelp const help_varpa[] = {
2756 { GNM_FUNC_HELP_NAME, F_("VARPA:variance of an entire population")},
2757 { GNM_FUNC_HELP_ARG, F_("area1:first cell area")},
2758 { GNM_FUNC_HELP_ARG, F_("area2:second cell area")},
2759 { GNM_FUNC_HELP_DESCRIPTION, F_("VARPA is also known as the N-variance.") },
2760 HELP_DESCRIPTION_TEXT_INCLUSION,
2761 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
2762 { GNM_FUNC_HELP_EXAMPLES, "=VARPA(11.4,17.3,21.3,3,25.9,40.1)" },
2763 { GNM_FUNC_HELP_SEEALSO, "VARA,VARP"},
2764 { GNM_FUNC_HELP_END }
2765 };
2766
2767 static GnmValue *
2768 gnumeric_varpa (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
2769 {
2770 return float_range_function (argc, argv, ei,
2771 gnm_range_var_pop,
2772 COLLECT_ZERO_STRINGS |
2773 COLLECT_ZEROONE_BOOLS |
2774 COLLECT_IGNORE_BLANKS,
2775 GNM_ERROR_VALUE);
2776 }
2777
2778 /***************************************************************************/
2779
2780 static GnmFuncHelp const help_stdeva[] = {
2781 { GNM_FUNC_HELP_NAME, F_("STDEVA:sample standard deviation of the given "
2782 "sample")},
2783 { GNM_FUNC_HELP_ARG, F_("area1:first cell area")},
2784 { GNM_FUNC_HELP_ARG, F_("area2:second cell area")},
2785 { GNM_FUNC_HELP_DESCRIPTION, F_("STDEVA is also known as the N-1-standard deviation.")},
2786 { GNM_FUNC_HELP_DESCRIPTION, F_("To obtain the population standard deviation of a whole population "
2787 "use STDEVPA.")},
2788 HELP_DESCRIPTION_TEXT_INCLUSION,
2789 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
2790 { GNM_FUNC_HELP_EXAMPLES, "=STDEVA(11.4,17.3,21.3,3,25.9,40.1)" },
2791 { GNM_FUNC_HELP_SEEALSO, "STDEV,STDEVPA"},
2792 { GNM_FUNC_HELP_END }
2793 };
2794
2795 static GnmValue *
2796 gnumeric_stdeva (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
2797 {
2798 return float_range_function (argc, argv, ei,
2799 gnm_range_stddev_est,
2800 COLLECT_ZERO_STRINGS |
2801 COLLECT_ZEROONE_BOOLS |
2802 COLLECT_IGNORE_BLANKS,
2803 GNM_ERROR_VALUE);
2804 }
2805
2806 /***************************************************************************/
2807
2808 static GnmFuncHelp const help_stdevpa[] = {
2809 { GNM_FUNC_HELP_NAME, F_("STDEVPA:population standard deviation of an entire population")},
2810 { GNM_FUNC_HELP_ARG, F_("area1:first cell area")},
2811 { GNM_FUNC_HELP_ARG, F_("area2:second cell area")},
2812 { GNM_FUNC_HELP_DESCRIPTION, F_("This is also known as the N-standard deviation")},
2813 HELP_DESCRIPTION_TEXT_INCLUSION,
2814 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
2815 { GNM_FUNC_HELP_EXAMPLES, "=STDEVPA(11.4,17.3,21.3,3,25.9,40.1)" },
2816 { GNM_FUNC_HELP_SEEALSO, "STDEVA,STDEVP"},
2817 { GNM_FUNC_HELP_END }
2818 };
2819
2820 static GnmValue *
2821 gnumeric_stdevpa (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
2822 {
2823 return float_range_function (argc, argv, ei,
2824 gnm_range_stddev_pop,
2825 COLLECT_ZERO_STRINGS |
2826 COLLECT_ZEROONE_BOOLS |
2827 COLLECT_IGNORE_BLANKS,
2828 GNM_ERROR_VALUE);
2829 }
2830
2831 /***************************************************************************/
2832
2833 static GnmFuncHelp const help_percentrank[] = {
2834 { GNM_FUNC_HELP_NAME, F_("PERCENTRANK:rank of a data point in a data set (Hyndman-Fan method 7: N-1 basis)")},
2835 { GNM_FUNC_HELP_ARG, F_("array:range of numeric values")},
2836 { GNM_FUNC_HELP_ARG, F_("x:data point to be ranked")},
2837 { GNM_FUNC_HELP_ARG, F_("significance:number of significant digits, defaults to 3")},
2838 { GNM_FUNC_HELP_NOTE, F_("If @{array} contains no data points, this function returns a #NUM! "
2839 "error.") },
2840 { GNM_FUNC_HELP_NOTE, F_("If @{significance} is less than one, this function returns a #NUM! "
2841 "error.") },
2842 { GNM_FUNC_HELP_NOTE, F_("If @{x} exceeds the largest value or is less than the smallest "
2843 "value in @{array}, this function returns an #N/A error.") },
2844 { GNM_FUNC_HELP_NOTE, F_("If @{x} does not match any of the values in @{array} or @{x} matches "
2845 "more than once, this function interpolates the returned value.") },
2846 { GNM_FUNC_HELP_SEEALSO, "LARGE,MAX,MEDIAN,MIN,PERCENTILE,QUARTILE,SMALL"},
2847 { GNM_FUNC_HELP_END }
2848 };
2849
2850 static GnmValue *
2851 gnumeric_percentrank (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2852 {
2853 gnm_float *data, x, significance, r;
2854 GnmValue *result = NULL;
2855 int i, n;
2856 int n_equal, n_smaller, n_larger;
2857 gnm_float x_larger, x_smaller;
2858
2859 data = collect_floats_value (argv[0], ei->pos,
2860 COLLECT_IGNORE_STRINGS |
2861 COLLECT_IGNORE_BOOLS |
2862 COLLECT_IGNORE_BLANKS |
2863 COLLECT_ORDER_IRRELEVANT,
2864 &n, &result);
2865 x = value_get_as_float (argv[1]);
2866 significance = argv[2] ? value_get_as_float (argv[2]) : 3;
2867
2868 if (result)
2869 goto done;
2870
2871 if (n == 0) {
2872 result = value_new_error_NUM (ei->pos);
2873 goto done;
2874 }
2875
2876 n_equal = n_smaller = n_larger = 0;
2877 x_larger = x_smaller = 42;
2878 for (i = 0; i < n; i++) {
2879 gnm_float y = data[i];
2880
2881 if (y < x) {
2882 if (n_smaller == 0 || x_smaller < y)
2883 x_smaller = y;
2884 n_smaller++;
2885 } else if (y > x) {
2886 if (n_larger == 0 || x_larger > y)
2887 x_larger = y;
2888 n_larger++;
2889 } else
2890 n_equal++;
2891 }
2892
2893 if (n_smaller + n_equal == 0 || n_larger + n_equal == 0) {
2894 result = value_new_error_NA (ei->pos);
2895 goto done;
2896 }
2897
2898 if (n == 1)
2899 r = 1;
2900 else {
2901 gnm_float s10;
2902
2903 if (n_equal > 0)
2904 r = n_smaller / (gnm_float)(n - 1);
2905 else {
2906 gnm_float r1 = (n_smaller - 1) / (gnm_float)(n - 1);
2907 gnm_float r2 = n_smaller / (gnm_float)(n - 1);
2908 r = (r1 * (x_larger - x) +
2909 r2 * (x - x_smaller)) / (x_larger - x_smaller);
2910 }
2911
2912 /* A strange place to check, but n==1 is special. */
2913 if (significance < 1) {
2914 result = value_new_error_NUM (ei->pos);
2915 goto done;
2916 }
2917
2918 s10 = gnm_pow10 (-significance);
2919 if (s10 <= 0) {
2920 result = value_new_error_DIV0 (ei->pos);
2921 goto done;
2922 }
2923
2924 r = gnm_fake_trunc (r / s10) * s10;
2925 }
2926 result = value_new_float (r);
2927
2928 done:
2929 g_free (data);
2930 return result;
2931 }
2932
2933 /***************************************************************************/
2934
2935
2936
2937 static GnmFuncHelp const help_percentrank_exc[] = {
2938 { GNM_FUNC_HELP_NAME, F_("PERCENTRANK.EXC:rank of a data point in a data set (Hyndman-Fan method 6: N+1 basis)")},
2939 { GNM_FUNC_HELP_ARG, F_("array:range of numeric values")},
2940 { GNM_FUNC_HELP_ARG, F_("x:data point to be ranked")},
2941 { GNM_FUNC_HELP_ARG, F_("significance:number of significant digits, defaults to 3")},
2942 { GNM_FUNC_HELP_NOTE, F_("If @{array} contains no data points, this function returns a #NUM! "
2943 "error.") },
2944 { GNM_FUNC_HELP_NOTE, F_("If @{significance} is less than one, this function returns a #NUM! "
2945 "error.") },
2946 { GNM_FUNC_HELP_NOTE, F_("If @{x} exceeds the largest value or is less than the smallest "
2947 "value in @{array}, this function returns an #N/A error.") },
2948 { GNM_FUNC_HELP_NOTE, F_("If @{x} does not match any of the values in @{array} or @{x} matches "
2949 "more than once, this function interpolates the returned value.") },
2950 { GNM_FUNC_HELP_SEEALSO, "LARGE,MAX,MEDIAN,MIN,PERCENTILE,PERCENTILE.EXC,QUARTILE,QUARTILE.EXC,SMALL"},
2951 { GNM_FUNC_HELP_END }
2952 };
2953
2954 static GnmValue *
2955 gnumeric_percentrank_exc (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2956 {
2957 gnm_float *data, x, significance, r;
2958 GnmValue *result = NULL;
2959 int i, n;
2960 int n_equal, n_smaller, n_larger;
2961 gnm_float x_larger, x_smaller;
2962
2963 data = collect_floats_value (argv[0], ei->pos,
2964 COLLECT_IGNORE_STRINGS |
2965 COLLECT_IGNORE_BOOLS |
2966 COLLECT_IGNORE_BLANKS |
2967 COLLECT_ORDER_IRRELEVANT,
2968 &n, &result);
2969 x = value_get_as_float (argv[1]);
2970 significance = argv[2] ? value_get_as_float (argv[2]) : 3;
2971
2972 if (result)
2973 goto done;
2974
2975 if (n == 0) {
2976 result = value_new_error_NUM (ei->pos);
2977 goto done;
2978 }
2979
2980 n_equal = n_smaller = n_larger = 0;
2981 x_larger = x_smaller = 42;
2982 for (i = 0; i < n; i++) {
2983 gnm_float y = data[i];
2984
2985 if (y < x) {
2986 if (n_smaller == 0 || x_smaller < y)
2987 x_smaller = y;
2988 n_smaller++;
2989 } else if (y > x) {
2990 if (n_larger == 0 || x_larger > y)
2991 x_larger = y;
2992 n_larger++;
2993 } else
2994 n_equal++;
2995 }
2996
2997 if (n_smaller + n_equal == 0 || n_larger + n_equal == 0) {
2998 result = value_new_error_NA (ei->pos);
2999 goto done;
3000 }
3001
3002 if (n == 1)
3003 r = 1;
3004 else {
3005 gnm_float s10;
3006
3007 /* A strange place to check, but n==1 is special. */
3008 if (significance < 1) {
3009 result = value_new_error_NUM (ei->pos);
3010 goto done;
3011 }
3012 s10 = gnm_pow10 (-significance);
3013 if (s10 <= 0) {
3014 result = value_new_error_DIV0 (ei->pos);
3015 goto done;
3016 }
3017
3018 if (n_equal > 0)
3019 r = (n_smaller + 1) / (gnm_float)(n + 1);
3020 else {
3021 gnm_float r1 = n_smaller / (gnm_float)(n + 1);
3022 gnm_float r2 = (n_smaller + 1) / (gnm_float)(n + 1);
3023 r = (r1 * (x_larger - x) +
3024 r2 * (x - x_smaller)) / (x_larger - x_smaller);
3025 }
3026
3027 r = gnm_fake_trunc (r / s10) * s10;
3028 }
3029 result = value_new_float (r);
3030
3031 done:
3032 g_free (data);
3033 return result;
3034 }
3035
3036 /***************************************************************************/
3037
3038 static GnmFuncHelp const help_percentile[] = {
3039 { GNM_FUNC_HELP_NAME, F_("PERCENTILE:determines the 100*@{k}-th percentile of the given data points (Hyndman-Fan method 7: N-1 basis)")},
3040 { GNM_FUNC_HELP_ARG, F_("array:data points")},
3041 { GNM_FUNC_HELP_ARG, F_("k:which percentile to calculate")},
3042 { GNM_FUNC_HELP_NOTE, F_("If @{array} is empty, this function returns a #NUM! error.") },
3043 { GNM_FUNC_HELP_NOTE, F_("If @{k} < 0 or @{k} > 1, this function returns a #NUM! error.")},
3044 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
3045 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5 contain numbers 11.4, 17.3, 21.3, 25.9, and 40.1.") },
3046 { GNM_FUNC_HELP_EXAMPLES, F_("Then PERCENTILE(A1:A5,0.42) equals 20.02.") },
3047 { GNM_FUNC_HELP_SEEALSO, "QUARTILE"},
3048 { GNM_FUNC_HELP_END }
3049 };
3050
3051 static GnmValue *
3052 gnumeric_percentile (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3053 {
3054 gnm_float *data;
3055 GnmValue *result = NULL;
3056 int n;
3057
3058 data = collect_floats_value (argv[0], ei->pos,
3059 COLLECT_IGNORE_STRINGS |
3060 COLLECT_IGNORE_BOOLS |
3061 COLLECT_IGNORE_BLANKS |
3062 COLLECT_SORT,
3063 &n, &result);
3064 if (!result) {
3065 gnm_float p = value_get_as_float (argv[1]);
3066 gnm_float res;
3067
3068 if (gnm_range_fractile_inter_sorted (data, n, &res, p))
3069 result = value_new_error_NUM (ei->pos);
3070 else
3071 result = value_new_float (res);
3072 }
3073
3074 g_free (data);
3075 return result;
3076 }
3077
3078 /***************************************************************************/
3079
3080 static GnmFuncHelp const help_percentile_exc[] = {
3081 { GNM_FUNC_HELP_NAME, F_("PERCENTILE.EXC:determines the 100*@{k}-th percentile of the given data points (Hyndman-Fan method 6: N+1 basis)")},
3082 { GNM_FUNC_HELP_ARG, F_("array:data points")},
3083 { GNM_FUNC_HELP_ARG, F_("k:which percentile to calculate")},
3084 { GNM_FUNC_HELP_NOTE, F_("If @{array} is empty, this function returns a #NUM! error.") },
3085 { GNM_FUNC_HELP_NOTE, F_("If @{k} < 0 or @{k} > 1, this function returns a #NUM! error.")},
3086 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
3087 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5 contain numbers 11.4, 17.3, 21.3, 25.9, and 40.1.") },
3088 { GNM_FUNC_HELP_EXAMPLES, F_("Then PERCENTILE.EXC(A1:A5,0.42) equals 20.02.") },
3089 { GNM_FUNC_HELP_SEEALSO, "PERCENTILE,QUARTILE,QUARTILE.EXC"},
3090 { GNM_FUNC_HELP_END }
3091 };
3092
3093 static GnmValue *
3094 gnumeric_percentile_exc (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3095 {
3096 gnm_float *data;
3097 GnmValue *result = NULL;
3098 int n;
3099
3100 data = collect_floats_value (argv[0], ei->pos,
3101 COLLECT_IGNORE_STRINGS |
3102 COLLECT_IGNORE_BOOLS |
3103 COLLECT_IGNORE_BLANKS |
3104 COLLECT_SORT,
3105 &n, &result);
3106 if (!result) {
3107 if (n > 1) {
3108 gnm_float p = value_get_as_float (argv[1]);
3109 gnm_float res;
3110 gnm_float fr = (p * (n + 1) - 1)/(n-1);
3111
3112 if (gnm_range_fractile_inter_sorted (data, n, &res, fr))
3113 result = value_new_error_NUM (ei->pos);
3114 else
3115 result = value_new_float (res);
3116 } else
3117 result = value_new_error_NUM (ei->pos);
3118 }
3119
3120 g_free (data);
3121 return result;
3122 }
3123 /***************************************************************************/
3124
3125 static GnmFuncHelp const help_quartile[] = {
3126 { GNM_FUNC_HELP_NAME, F_("QUARTILE:the @{k}-th quartile of the data points (Hyndman-Fan method 7: N-1 basis)")},
3127 { GNM_FUNC_HELP_ARG, F_("array:data points")},
3128 { GNM_FUNC_HELP_ARG, F_("quart:a number from 0 to 4, indicating which quartile to calculate")},
3129 { GNM_FUNC_HELP_NOTE, F_("If @{array} is empty, this function returns a #NUM! error.") },
3130 { GNM_FUNC_HELP_NOTE, F_("If @{quart} < 0 or @{quart} > 4, this function returns a #NUM! error. If @{quart} = 0, the smallest value of @{array} to be returned.") },
3131 { GNM_FUNC_HELP_NOTE, F_("If @{quart} is not an integer, it is truncated.")},
3132 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
3133 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5 contain numbers 11.4, 17.3, 21.3, 25.9, and 40.1.") },
3134 { GNM_FUNC_HELP_EXAMPLES, F_("Then QUARTILE(A1:A5,1) equals 17.3.") },
3135 { GNM_FUNC_HELP_SEEALSO, "LARGE,MAX,MEDIAN,MIN,PERCENTILE,QUARTILE.EXC,SMALL"},
3136 { GNM_FUNC_HELP_END }
3137 };
3138
3139 static GnmValue *
3140 gnumeric_quartile (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3141 {
3142 gnm_float *data;
3143 GnmValue *result = NULL;
3144 int n;
3145
3146 data = collect_floats_value (argv[0], ei->pos,
3147 COLLECT_IGNORE_STRINGS |
3148 COLLECT_IGNORE_BOOLS |
3149 COLLECT_IGNORE_BLANKS |
3150 COLLECT_SORT,
3151 &n, &result);
3152 if (!result) {
3153 gnm_float q = gnm_fake_floor (value_get_as_float (argv[1]));
3154 gnm_float res;
3155
3156 if (gnm_range_fractile_inter_sorted (data, n, &res, q / 4.0))
3157 result = value_new_error_NUM (ei->pos);
3158 else
3159 result = value_new_float (res);
3160 }
3161
3162 g_free (data);
3163 return result;
3164 }
3165
3166 /***************************************************************************/
3167
3168 static GnmFuncHelp const help_quartile_exc[] = {
3169 { GNM_FUNC_HELP_NAME, F_("QUARTILE.EXC:the @{k}-th quartile of the data points (Hyndman-Fan method 6: N+1 basis)")},
3170 { GNM_FUNC_HELP_ARG, F_("array:data points")},
3171 { GNM_FUNC_HELP_ARG, F_("quart:a number from 1 to 3, indicating which quartile to calculate")},
3172 { GNM_FUNC_HELP_NOTE, F_("If @{array} is empty, this function returns a #NUM! error.") },
3173 { GNM_FUNC_HELP_NOTE, F_("If @{quart} < 0 or @{quart} > 4, this function returns a #NUM! error. If @{quart} = 0, the smallest value of @{array} to be returned.") },
3174 { GNM_FUNC_HELP_NOTE, F_("If @{quart} is not an integer, it is truncated.")},
3175 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
3176 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5 contain numbers 11.4, 17.3, 21.3, 25.9, and 40.1.") },
3177 { GNM_FUNC_HELP_EXAMPLES, F_("Then QUARTILE.EXC(A1:A5,1) equals 14.35.") },
3178 { GNM_FUNC_HELP_SEEALSO, "LARGE,MAX,MEDIAN,MIN,PERCENTILE,PERCENTILE.EXC,QUARTILE,SMALL"},
3179 { GNM_FUNC_HELP_END }
3180 };
3181
3182 static GnmValue *
3183 gnumeric_quartile_exc (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3184 {
3185 gnm_float *data;
3186 GnmValue *result = NULL;
3187 int n;
3188
3189 data = collect_floats_value (argv[0], ei->pos,
3190 COLLECT_IGNORE_STRINGS |
3191 COLLECT_IGNORE_BOOLS |
3192 COLLECT_IGNORE_BLANKS |
3193 COLLECT_SORT,
3194 &n, &result);
3195 if (!result) {
3196 if (n > 1) {
3197 gnm_float q = gnm_fake_floor (value_get_as_float (argv[1]));
3198 gnm_float res;
3199 gnm_float fr = ((q / 4.0) * (n + 1) - 1)/(n-1);
3200
3201 if (gnm_range_fractile_inter_sorted (data, n, &res, fr))
3202 result = value_new_error_NUM (ei->pos);
3203 else
3204 result = value_new_float (res);
3205 } else
3206 result = value_new_error_NUM (ei->pos);
3207 }
3208
3209 g_free (data);
3210 return result;
3211 }
3212 /***************************************************************************/
3213
3214 static GnmFuncHelp const help_ftest[] = {
3215 { GNM_FUNC_HELP_NAME, F_("FTEST:p-value for the two-tailed hypothesis test comparing the "
3216 "variances of two populations")},
3217 { GNM_FUNC_HELP_ARG, F_("array1:sample from the first population")},
3218 { GNM_FUNC_HELP_ARG, F_("array2:sample from the second population")},
3219 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
3220 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5 contain numbers "
3221 "11.4, 17.3, 21.3, 25.9, and 40.1, and the cells B1, B2, ... "
3222 "B5 23.2, 25.8, 29.9, 33.5, and 42.7.") },
3223 { GNM_FUNC_HELP_EXAMPLES, F_("Then FTEST(A1:A5,B1:B5) equals 0.510815017.") },
3224 { GNM_FUNC_HELP_SEEALSO, "FDIST,FINV"},
3225 { GNM_FUNC_HELP_END }
3226 };
3227
3228 static GnmValue *
3229 gnumeric_ftest (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3230 {
3231 CollectFlags flags = COLLECT_IGNORE_STRINGS | COLLECT_IGNORE_BOOLS |
3232 COLLECT_IGNORE_BLANKS;
3233 gnm_float *xs = NULL, *ys = NULL;
3234 int nx, ny;
3235 GnmValue *res = NULL;
3236 gnm_float p, varx, vary;
3237
3238 xs = collect_floats_value (argv[0], ei->pos, flags, &nx, &res);
3239 if (res)
3240 goto out;
3241
3242 ys = collect_floats_value (argv[1], ei->pos, flags, &ny, &res);
3243 if (res)
3244 goto out;
3245
3246 if (gnm_range_var_est (xs, nx, &varx) ||
3247 gnm_range_var_est (ys, ny, &vary) ||
3248 vary == 0) {
3249 res = value_new_error_DIV0 (ei->pos);
3250 goto out;
3251 }
3252
3253 p = pf (varx / vary, nx - 1, ny - 1, FALSE, FALSE);
3254 if (p > 0.5) {
3255 /*
3256 * We need the other tail and 1-p might not be very accurate.
3257 */
3258 p = pf (varx / vary, nx - 1, ny - 1, TRUE, FALSE);
3259 }
3260
3261 res = value_new_float (2 * p);
3262
3263 out:
3264 g_free (xs);
3265 g_free (ys);
3266 return res;
3267 }
3268
3269 /***************************************************************************/
3270
3271 static GnmFuncHelp const help_ttest[] = {
3272 { GNM_FUNC_HELP_NAME, F_("TTEST:p-value for a hypothesis test comparing the means of two populations using "
3273 "the Student t-distribution")},
3274 { GNM_FUNC_HELP_ARG, F_("array1:sample from the first population")},
3275 { GNM_FUNC_HELP_ARG, F_("array2:sample from the second population")},
3276 { GNM_FUNC_HELP_ARG, F_("tails:number of tails to consider")},
3277 { GNM_FUNC_HELP_ARG, F_("type:Type of test to perform. 1 indicates a test for paired variables, "
3278 "2 a test of unpaired variables with equal variances, and "
3279 "3 a test of unpaired variables with unequal variances")},
3280 { GNM_FUNC_HELP_NOTE, F_("If the data sets contain a different number of data points and "
3281 "the test is paired (@{type} one), TTEST returns the #N/A error.") },
3282 { GNM_FUNC_HELP_NOTE, F_("@{tails} and @{type} are truncated to integers.") },
3283 { GNM_FUNC_HELP_NOTE, F_("If @{tails} is not one or two, this function returns a #NUM! error.") },
3284 { GNM_FUNC_HELP_NOTE, F_("If @{type} is any other than one, two, or three, this function returns a #NUM! error.")},
3285 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
3286 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5 contain numbers "
3287 "11.4, 17.3, 21.3, 25.9, and 40.1, and the cells B1, B2, ... "
3288 "B5 23.2, 25.8, 29.9, 33.5, and 42.7.") },
3289 { GNM_FUNC_HELP_EXAMPLES, F_("Then TTEST(A1:A5,B1:B5,1,1) equals 0.003127619. "
3290 "TTEST(A1:A5,B1:B5,2,1) equals 0.006255239. "
3291 "TTEST(A1:A5,B1:B5,1,2) equals 0.111804322. "
3292 "TTEST(A1:A5,B1:B5,1,3) equals 0.113821797.") },
3293 { GNM_FUNC_HELP_SEEALSO, "FDIST,FINV"},
3294 { GNM_FUNC_HELP_END }
3295 };
3296
3297 static int barf_ttest_dof;
3298
3299 static int
3300 calc_ttest_paired (gnm_float const *xs, gnm_float const *ys, int n,
3301 gnm_float *res)
3302 {
3303 gnm_float *zs, zm, zsig;
3304 gboolean err;
3305 int i;
3306
3307 if (n == 0)
3308 return 1;
3309
3310 /* zs = xs - ys */
3311 zs = g_memdup (xs, n * sizeof (*xs));
3312 for (i = 0; i < n; i++)
3313 zs[i] -= ys[i];
3314
3315 err = (gnm_range_average (zs, n, &zm) ||
3316 gnm_range_stddev_est (zs, n, &zsig) ||
3317 zsig == 0);
3318 g_free (zs);
3319
3320 if (err)
3321 return 1;
3322 else {
3323 *res = gnm_sqrt (n) * (zm / zsig);
3324 /* We need this n out of here. For now, hack it. */
3325 barf_ttest_dof = n - 1;
3326 return 0;
3327 }
3328 }
3329
3330 static GnmValue *
3331 ttest_paired (GnmFuncEvalInfo *ei,
3332 GnmValue const *r0, GnmValue const *r1, int tails)
3333 {
3334 int w0 = value_area_get_width (r0, ei->pos);
3335 int h0 = value_area_get_height (r0, ei->pos);
3336 int w1 = value_area_get_width (r1, ei->pos);
3337 int h1 = value_area_get_height (r1, ei->pos);
3338 GnmValue *v;
3339 gnm_float x;
3340
3341 /* Size error takes precedence over everything else. */
3342 if (w0 * h0 != w1 * h1)
3343 return value_new_error_NA (ei->pos);
3344
3345 v = float_range_function2 (r0, r1, ei, calc_ttest_paired,
3346 COLLECT_IGNORE_BLANKS |
3347 COLLECT_IGNORE_STRINGS |
3348 COLLECT_IGNORE_BOOLS,
3349 GNM_ERROR_DIV0);
3350
3351 if (!VALUE_IS_NUMBER (v))
3352 return v;
3353
3354 x = value_get_as_float (v);
3355 value_release (v);
3356
3357 return value_new_float (tails * pt (gnm_abs (x), barf_ttest_dof,
3358 FALSE, FALSE));
3359 }
3360
3361 static GnmValue *
3362 ttest_equal_unequal (GnmFuncEvalInfo *ei,
3363 GnmValue const *rx, GnmValue const *ry, int tails,
3364 gboolean unequal)
3365 {
3366 CollectFlags flags = COLLECT_IGNORE_STRINGS | COLLECT_IGNORE_BOOLS |
3367 COLLECT_IGNORE_BLANKS;
3368 gnm_float *xs = NULL, *ys = NULL;
3369 int nx, ny;
3370 GnmValue *res = NULL;
3371 gnm_float mx, vx, my, vy, dof, t;
3372
3373 xs = collect_floats_value (rx, ei->pos, flags, &nx, &res);
3374 if (res)
3375 goto out;
3376
3377 ys = collect_floats_value (ry, ei->pos, flags, &ny, &res);
3378 if (res)
3379 goto out;
3380
3381 if (gnm_range_average (xs, nx, &mx) ||
3382 gnm_range_var_est (xs, nx, &vx) ||
3383 gnm_range_average (ys, ny, &my) ||
3384 gnm_range_var_est (ys, ny, &vy)) {
3385 res = value_new_error_DIV0 (ei->pos);
3386 goto out;
3387 }
3388
3389 if (vx == 0 && vy == 0) {
3390 /* Note sure here. */
3391 res = value_new_error_DIV0 (ei->pos);
3392 goto out;
3393 }
3394
3395 if (unequal) {
3396 gnm_float S = (vx / nx + vy / ny);
3397 gnm_float c = (vx / nx) / S;
3398 gnm_float cC = (vy / ny) / S;
3399 dof = 1.0 / (c * c / (nx - 1) + cC * cC / (ny - 1));
3400 t = gnm_abs (mx - my) / gnm_sqrt (S);
3401 } else {
3402 dof = nx + ny - 2;
3403 t = (gnm_abs (mx - my) *
3404 gnm_sqrt (dof * nx * ny /
3405 ((nx + ny) * ((nx - 1) * vx + (ny - 1) * vy))));
3406 }
3407 res = value_new_float (tails * pt (t, dof, FALSE, FALSE));
3408
3409 out:
3410 g_free (xs);
3411 g_free (ys);
3412 return res;
3413 }
3414
3415 static GnmValue *
3416 gnumeric_ttest (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3417 {
3418 gnm_float tails = value_get_as_float (argv[2]);
3419 gnm_float type = value_get_as_float (argv[3]);
3420 int itails;
3421
3422 if (tails != 1 && tails != 2)
3423 return value_new_error_NUM (ei->pos);
3424 itails = (int)tails;
3425
3426 if (type != 1 && type != 2 && type != 3)
3427 return value_new_error_NUM (ei->pos);
3428
3429 switch ((int)type) {
3430 case 1:
3431 return ttest_paired (ei, argv[0], argv[1], itails);
3432
3433 case 2:
3434 return ttest_equal_unequal (ei, argv[0], argv[1], itails, FALSE);
3435
3436 case 3:
3437 return ttest_equal_unequal (ei, argv[0], argv[1], itails, TRUE);
3438
3439 default:
3440 return value_new_error_NUM (ei->pos);
3441 }
3442 }
3443
3444 /***************************************************************************/
3445
3446 static GnmFuncHelp const help_frequency[] = {
3447 { GNM_FUNC_HELP_NAME, F_("FREQUENCY:frequency table")},
3448 { GNM_FUNC_HELP_ARG, F_("data_array:data values")},
3449 { GNM_FUNC_HELP_ARG, F_("bins_array:array of cutoff values")},
3450 { GNM_FUNC_HELP_DESCRIPTION, F_("The results are given as an array.") },
3451 { GNM_FUNC_HELP_DESCRIPTION, F_("If the @{bins_array} is empty, this function "
3452 "returns the number of data points "
3453 "in @{data_array}.")},
3454 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
3455 { GNM_FUNC_HELP_END }
3456 };
3457
3458
3459 static GnmValue *
3460 gnumeric_frequency (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3461 {
3462 CollectFlags flags = COLLECT_IGNORE_STRINGS | COLLECT_IGNORE_BOOLS |
3463 COLLECT_IGNORE_BLANKS;
3464 GnmValue *error = NULL, *res;
3465 int *counts;
3466 int i, nvalues, nbins;
3467 gnm_float *values = NULL, *bins = NULL;
3468
3469 values = collect_floats_value (argv[0], ei->pos, flags,
3470 &nvalues, &error);
3471 if (error) {
3472 res = error;
3473 goto out;
3474 }
3475
3476 bins = collect_floats_value (argv[1], ei->pos, flags | COLLECT_SORT,
3477 &nbins, &error);
3478 if (error) {
3479 res = error;
3480 goto out;
3481 }
3482
3483 /* Special case. */
3484 if (nbins == 0) {
3485 res = value_new_int (nvalues);
3486 goto out;
3487 }
3488
3489 counts = g_new0 (int, nbins + 1);
3490
3491 /* Stupid code. */
3492 for (i = 0; i < nvalues; i++) {
3493 int j;
3494 for (j = 0; j < nbins; j++)
3495 if (values[i] <= bins[j])
3496 break;
3497 counts[j]++;
3498 }
3499
3500 res = value_new_array_non_init (1, nbins + 1);
3501 res->v_array.vals[0] = g_new (GnmValue *, nbins + 1);
3502 for (i = 0; i < nbins + 1; i++)
3503 res->v_array.vals[0][i] = value_new_float (counts[i]);
3504 g_free (counts);
3505
3506 out:
3507 g_free (values);
3508 g_free (bins);
3509 return res;
3510 }
3511
3512 /***************************************************************************/
3513
3514 static GnmFuncHelp const help_leverage[] = {
3515 { GNM_FUNC_HELP_NAME, F_("LEVERAGE:calculate regression leverage")},
3516 { GNM_FUNC_HELP_ARG, F_("A:a matrix")},
3517 { GNM_FUNC_HELP_DESCRIPTION,
3518 F_("Returns the diagonal of @{A} (@{A}^T @{A})^-1 @{A}^T as a column vector.") },
3519 { GNM_FUNC_HELP_NOTE, F_("If the matrix is singular, #VALUE! is returned.") },
3520 { GNM_FUNC_HELP_END}
3521 };
3522
3523
3524 static GnmValue *
3525 gnumeric_leverage (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3526 {
3527 GnmMatrix *A = NULL;
3528 GnmValue *res = NULL;
3529 GORegressionResult regres;
3530 gnm_float *x;
3531
3532 A = gnm_matrix_from_value (argv[0], &res, ei->pos);
3533 if (!A) goto out;
3534
3535 if (gnm_matrix_is_empty (A)) {
3536 res = value_new_error_VALUE (ei->pos);
3537 goto out;
3538 }
3539
3540 x = g_new (gnm_float, A->rows);
3541
3542 regres = gnm_linear_regression_leverage (A->data, x, A->rows, A->cols);
3543
3544 if (regres != GO_REG_ok && regres != GO_REG_near_singular_good) {
3545 res = value_new_error_NUM (ei->pos);
3546 } else {
3547 int x_rows = A->rows, x_cols = 1;
3548 int c, r;
3549
3550 res = value_new_array_non_init (x_cols, x_rows);
3551 for (c = 0; c < x_cols; c++) {
3552 res->v_array.vals[c] = g_new (GnmValue *, x_rows);
3553 for (r = 0; r < x_rows; r++)
3554 res->v_array.vals[c][r] =
3555 value_new_float (x[r]);
3556 }
3557 }
3558
3559 g_free (x);
3560
3561 out:
3562 if (A) gnm_matrix_unref (A);
3563 return res;
3564 }
3565
3566 /***************************************************************************/
3567
3568 /* Notes for now, to be incorporated into help when it actually works:
3569 *
3570 * Entered as linest(Yrange, [Xrange, [Intercept, [Stat]]]). Intercept and Stat
3571 * work as above. According to playing with Excel, if Yrange is an array, Xrange
3572 * must be an array. They claim that you can use semicolons to separate rows in
3573 * an array, but I've never gotten that to work. If Yrange is a single column,
3574 * every column in the Xrange (which must be a cell range) is interpreted as a
3575 * separate variable. Similar for a single row. If Y is a blob, X is interpreted
3576 * as a single variable blob. Experiments suggest that multivariable blobs don't work.
3577 * Currently everything should be implemented except for inputting arrays. X's must
3578 * be contiguous so far as I can tell.
3579 */
3580
3581 typedef enum {
3582 gnm_reg_type_rect = 0,
3583 gnm_reg_type_vertical,
3584 gnm_reg_type_horizontal
3585 } GnmRegType_t;
3586
3587 typedef struct {
3588 gnm_float *ys;
3589 int n;
3590 gnm_float **xss;
3591 int dim;
3592 GnmRegType_t type;
3593 } GnmRegData;
3594
3595 static void
3596 gnm_reg_data_free (GnmRegData *data)
3597 {
3598 int i;
3599
3600 g_free (data->ys);
3601 for (i = 0; i < data->dim; i++)
3602 g_free (data->xss[i]);
3603 g_free (data->xss);
3604
3605 memset (data, 0, sizeof (*data));
3606 }
3607
3608 static gnm_float *
3609 gnm_reg_get_var (GnmValue const *xval, int x, int y, int dx, int dy,
3610 int n, GnmEvalPos const *ep)
3611 {
3612 gnm_float *res = g_new (gnm_float, n);
3613 int i;
3614
3615 for (i = 0; i < n; i++) {
3616 GnmValue const *v = value_area_fetch_x_y (xval, x, y, ep);
3617
3618 if (!VALUE_IS_FLOAT (v)) {
3619 /* Anything else is an error. */
3620 g_free (res);
3621 return NULL;
3622 }
3623
3624 res[i] = value_get_as_float (v);
3625 x += dx;
3626 y += dy;
3627 }
3628
3629 return res;
3630 }
3631
3632
3633
3634 static GnmValue *
3635 gnm_reg_data_collect (GnmValue const *yval, GnmValue const *xval,
3636 GnmRegData *data, GnmEvalPos const *ep)
3637 {
3638 int yh = value_area_get_height (yval, ep);
3639 int yw = value_area_get_width (yval, ep);
3640 int ny;
3641 GnmValue *result = NULL;
3642
3643 memset (data, 0, sizeof (*data));
3644
3645 /* Blanks, bools, strings, errors all are forbidden. */
3646 data->ys = collect_floats_value (yval, ep, 0,
3647 &ny, &result);
3648 if (result || ny <= 0)
3649 goto error;
3650 data->n = ny;
3651
3652 if (VALUE_IS_EMPTY (xval)) {
3653 int i;
3654
3655 data->dim = 1;
3656 data->xss = g_new (gnm_float *, data->dim);
3657 data->xss[0] = g_new (gnm_float, ny);
3658 data->type = gnm_reg_type_rect;
3659 for (i = 0; i < ny; i++)
3660 data->xss[0][i] = i + 1;
3661 } else {
3662 int xh = value_area_get_height (xval, ep);
3663 int xw = value_area_get_width (xval, ep);
3664 int i, nx;
3665
3666 if (yw == 1) {
3667 /* X's columns are the variables. */
3668 if (xh != yh)
3669 goto ref_error;
3670 data->dim = xw;
3671 data->xss = g_new0 (gnm_float *, data->dim);
3672 data->type = gnm_reg_type_vertical;
3673 for (i = 0; i < data->dim; i++) {
3674 data->xss[i] = gnm_reg_get_var
3675 (xval, i, 0, 0, +1, xh, ep);
3676 if (!data->xss[i])
3677 goto error;
3678 }
3679 } else if (yh == 1) {
3680 /* X's rows are the variables. */
3681 if (xw != yw)
3682 goto ref_error;
3683 data->dim = xh;
3684 data->xss = g_new0 (gnm_float *, data->dim);
3685 data->type = gnm_reg_type_horizontal;
3686 for (i = 0; i < data->dim; i++) {
3687 data->xss[i] = gnm_reg_get_var
3688 (xval, 0, i, +1, 0, xw, ep);
3689 if (!data->xss[i])
3690 goto error;
3691 }
3692 } else {
3693 if (xh != yh || xw != yw)
3694 goto ref_error;
3695 data->dim = 1;
3696 data->xss = g_new0 (gnm_float *, data->dim);
3697 data->xss[0] = collect_floats_value (xval, ep, 0,
3698 &nx, &result);
3699 data->type = gnm_reg_type_rect;
3700 if (result)
3701 goto error;
3702 }
3703 }
3704
3705 return NULL;
3706
3707 error:
3708 value_release (result);
3709 /* Always this kind of error. */
3710 result = value_new_error_VALUE (ep);
3711 gnm_reg_data_free (data);
3712 return result;
3713
3714 ref_error:
3715 /* If the areas have the wrong shape, we get #REF! */
3716 gnm_reg_data_free (data);
3717 return value_new_error_REF (ep);
3718 }
3719
3720
3721
3722 static GnmFuncHelp const help_linest[] = {
3723 { GNM_FUNC_HELP_NAME, F_("LINEST:multiple linear regression coefficients and statistics") },
3724 { GNM_FUNC_HELP_ARG, F_("known_ys:vector of values of dependent variable") },
3725 { GNM_FUNC_HELP_ARG, F_("known_xs:array of values of independent variables, defaults to a single vector {1,\xe2\x80\xa6,n}") },
3726 { GNM_FUNC_HELP_ARG, F_("affine:if true, the model contains a constant term, defaults to true") },
3727 { GNM_FUNC_HELP_ARG, F_("stats:if true, some additional statistics are provided, defaults to false") },
3728 { GNM_FUNC_HELP_DESCRIPTION, F_("This function returns an array with the first row giving the regression "
3729 "coefficients for the independent variables "
3730 "x_m, x_(m-1),\xe2\x80\xa6,x_2, x_1 followed by the y-intercept if @{affine} is true.")},
3731 { GNM_FUNC_HELP_DESCRIPTION, F_("If @{stats} is true, the second row contains the corresponding standard "
3732 "errors of the regression coefficients. "
3733 "In this case, the third row contains the R^2 value and the standard error "
3734 "for the predicted value. "
3735 "The fourth row contains the observed F value and its degrees of freedom. "
3736 "Finally, the fifth row contains the regression sum of squares and the "
3737 "residual sum of squares.") },
3738 { GNM_FUNC_HELP_DESCRIPTION, F_("If @{affine} is false, R^2 is the uncentered version of the coefficient "
3739 "of determination; "
3740 "that is the proportion of the sum of squares explained by the model.")},
3741 { GNM_FUNC_HELP_NOTE, F_("If the length of @{known_ys} does not match the corresponding length of @{known_xs}, "
3742 "this function returns a #NUM! error.")},
3743 { GNM_FUNC_HELP_SEEALSO, "LOGEST,TREND" },
3744 { GNM_FUNC_HELP_END }
3745 };
3746
3747 static GnmValue *
3748 gnumeric_linest (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3749 {
3750 GnmRegData data;
3751 gnm_regression_stat_t *extra_stat = NULL;
3752 GORegressionResult regres;
3753 GnmValue *result;
3754 gnm_float *linres = NULL;
3755 gboolean affine, withstat;
3756 int i, dim;
3757
3758 result = gnm_reg_data_collect (argv[0], argv[1], &data, ei->pos);
3759 if (result)
3760 return result;
3761 dim = data.dim;
3762
3763 affine = argv[2] ? value_get_as_checked_bool (argv[2]) : TRUE;
3764 withstat = argv[3] ? value_get_as_checked_bool (argv[3]) : FALSE;
3765
3766 linres = g_new (gnm_float, dim + 1);
3767 extra_stat = gnm_regression_stat_new ();
3768 regres = gnm_linear_regression (data.xss, dim,
3769 data.ys, data.n, affine,
3770 linres, extra_stat);
3771 switch (regres) {
3772 case GO_REG_ok:
3773 case GO_REG_near_singular_good:
3774 break;
3775 default:
3776 result = value_new_error_NUM (ei->pos);
3777 goto out;
3778 }
3779
3780 if (withstat) {
3781 result = value_new_array (dim + 1, 5);
3782
3783 for (i = 2; i <= dim; i++) {
3784 value_array_set (result, i, 2,
3785 value_new_error_NA (ei->pos));
3786 value_array_set (result, i, 3,
3787 value_new_error_NA (ei->pos));
3788 value_array_set (result, i, 4,
3789 value_new_error_NA (ei->pos));
3790 }
3791
3792 value_array_set (result, 0, 2,
3793 value_new_float (extra_stat->sqr_r));
3794 value_array_set (result, 1, 2,
3795 value_new_float (gnm_sqrt (extra_stat->var)));
3796 value_array_set (result, 0, 3,
3797 value_new_float (extra_stat->F));
3798 value_array_set (result, 1, 3,
3799 value_new_float (extra_stat->df_resid));
3800 value_array_set (result, 0, 4,
3801 value_new_float (extra_stat->ss_reg));
3802 value_array_set (result, 1, 4,
3803 value_new_float (extra_stat->ss_resid));
3804 for (i = 0; i < dim; i++)
3805 value_array_set (result, dim - i - 1, 1,
3806 value_new_float (extra_stat->se[i+affine]));
3807 value_array_set (result, dim, 1,
3808 affine ? value_new_float (extra_stat->se[0])
3809 : value_new_error_NA (ei->pos));
3810 } else
3811 result = value_new_array (dim + 1, 1);
3812
3813 value_array_set (result, dim, 0, value_new_float (linres[0]));
3814 for (i = 0; i < dim; i++)
3815 value_array_set (result, dim - i - 1, 0,
3816 value_new_float (linres[i + 1]));
3817
3818 out:
3819 gnm_reg_data_free (&data);
3820 g_free (linres);
3821 gnm_regression_stat_destroy (extra_stat);
3822
3823 return result;
3824 }
3825
3826 /***************************************************************************/
3827
3828 static GnmFuncHelp const help_logreg[] = {
3829 { GNM_FUNC_HELP_NAME, F_("LOGREG:the logarithmic regression")},
3830 { GNM_FUNC_HELP_ARG, F_("known_ys:known y-values")},
3831 { GNM_FUNC_HELP_ARG, F_("known_xs:known x-values; defaults to the array {1, 2, 3, \xe2\x80\xa6}")},
3832 { GNM_FUNC_HELP_ARG, F_("affine:if true, the model contains a constant term, defaults to true")},
3833 { GNM_FUNC_HELP_ARG, F_("stat:if true, extra statistical information will be returned; defaults to FALSE")},
3834 { GNM_FUNC_HELP_DESCRIPTION, F_("LOGREG function transforms your x's to z=ln(x) and "
3835 "applies the \xe2\x80\x9cleast squares\xe2\x80\x9d method to fit the linear equation "
3836 "y = m * z + b "
3837 "to your y's and z's --- equivalent to fitting the equation "
3838 "y = m * ln(x) + b "
3839 "to y's and x's. "
3840 "LOGREG returns an array having two columns and one row. "
3841 "m is given in the first column and b in the second.") },
3842 { GNM_FUNC_HELP_DESCRIPTION, F_("Any extra statistical information is written below m and b in the "
3843 "result array. This extra statistical information consists of four "
3844 "rows of data: In the first row the standard error values for the "
3845 "coefficients m, b are given. The second row "
3846 "contains the square of R and the standard error for the y "
3847 "estimate. The third row contains the F-observed value and the "
3848 "degrees of freedom. The last row contains the regression sum "
3849 "of squares and the residual sum of squares. "
3850 "The default of @{stat} is FALSE.") },
3851 { GNM_FUNC_HELP_NOTE, F_("If @{known_ys} and @{known_xs} have unequal number of data points, "
3852 "this function returns a #NUM! error.") },
3853 { GNM_FUNC_HELP_SEEALSO, "LOGFIT,LINEST,LOGEST"},
3854 { GNM_FUNC_HELP_END }
3855 };
3856 /* The following is a copy of "gnumeric_linest"
3857 * with "linear_regression" replaced by "logarithmic_regression".
3858 *
3859 * In Excel, this functionality is not available as a function, but only
3860 * as a "trend curve" within graphs.
3861 *
3862 * The function "logarithmic_regression" transforms x's logarithmically
3863 * before calling "general_linear_regression" written by others.
3864 *
3865 * I do not know if in statistical praxis logarithmically transformed x-data
3866 * is useful for *multidimensional* regression, and also if extra statistical
3867 * data is useful in this case, but since "general_linear_regression" written
3868 * by others provides it I have passed this functionality to the user.
3869 * But see comment to "gnumeric_linest" for problem with reading more than
3870 * one x-range.
3871 */
3872 static GnmValue *
3873 gnumeric_logreg (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3874 {
3875 GnmRegData data;
3876 gnm_regression_stat_t *extra_stat = NULL;
3877 GORegressionResult regres;
3878 GnmValue *result;
3879 gnm_float *logres = NULL;
3880 gboolean affine, withstat;
3881 int i, dim;
3882
3883 result = gnm_reg_data_collect (argv[0], argv[1], &data, ei->pos);
3884 if (result)
3885 return result;
3886 dim = data.dim;
3887
3888 affine = argv[2] ? value_get_as_checked_bool (argv[2]) : TRUE;
3889 withstat = argv[3] ? value_get_as_checked_bool (argv[3]) : FALSE;
3890
3891 logres = g_new (gnm_float, dim + 1);
3892 extra_stat = gnm_regression_stat_new ();
3893 regres = gnm_logarithmic_regression (data.xss, dim,
3894 data.ys, data.n, affine,
3895 logres, extra_stat);
3896 switch (regres) {
3897 case GO_REG_ok:
3898 case GO_REG_near_singular_good:
3899 break;
3900 default:
3901 result = value_new_error_NUM (ei->pos);
3902 goto out;
3903 }
3904
3905 if (withstat) {
3906 result = value_new_array (dim + 1, 5);
3907
3908 value_array_set (result, 0, 2,
3909 value_new_float (extra_stat->sqr_r));
3910 value_array_set (result, 1, 2,
3911 value_new_float (gnm_sqrt (extra_stat->var)));
3912 value_array_set (result, 0, 3,
3913 value_new_float (extra_stat->F));
3914 value_array_set (result, 1, 3,
3915 value_new_float (extra_stat->df_resid));
3916 value_array_set (result, 0, 4,
3917 value_new_float (extra_stat->ss_reg));
3918 value_array_set (result, 1, 4,
3919 value_new_float (extra_stat->ss_resid));
3920 for (i = 0; i < dim; i++)
3921 value_array_set (result, dim - i - 1, 1,
3922 value_new_float (extra_stat->se[i+affine]));
3923 value_array_set (result, dim, 1,
3924 affine ? value_new_float (extra_stat->se[0])
3925 : value_new_error_NA (ei->pos));
3926 } else
3927 result = value_new_array (dim + 1, 1);
3928
3929 value_array_set (result, dim, 0, value_new_float (logres[0]));
3930 for (i = 0; i < dim; i++)
3931 value_array_set (result, dim - i - 1, 0,
3932 value_new_float (logres[i + 1]));
3933
3934 out:
3935 gnm_reg_data_free (&data);
3936 g_free (logres);
3937 gnm_regression_stat_destroy (extra_stat);
3938
3939 return result;
3940 }
3941
3942 /***************************************************************************/
3943
3944 static GnmFuncHelp const help_logfit[] = {
3945 { GNM_FUNC_HELP_NAME, F_("LOGFIT:logarithmic least square fit (using a trial and error method)")},
3946 { GNM_FUNC_HELP_ARG, F_("known_ys:known y-values")},
3947 { GNM_FUNC_HELP_ARG, F_("known_xs:known x-values")},
3948 { GNM_FUNC_HELP_DESCRIPTION, F_(
3949 "LOGFIT function applies the \xe2\x80\x9cleast squares\xe2\x80\x9d method to fit "
3950 "the logarithmic equation "
3951 "y = a + b * ln(sign * (x - c)) , sign = +1 or -1 "
3952 "to your data. The graph of the equation is a logarithmic curve "
3953 "moved horizontally by c and possibly mirrored across the y-axis "
3954 "(if sign = -1).")},
3955 { GNM_FUNC_HELP_DESCRIPTION, F_("LOGFIT returns an array having five columns and one row. "
3956 "`Sign' is given in the first column, `a', `b', and `c' are "
3957 "given in columns 2 to 4. Column 5 holds the sum of squared "
3958 "residuals.")},
3959 { GNM_FUNC_HELP_NOTE, F_("An error is returned when there are less than 3 different x's "
3960 "or y's, or when the shape of the point cloud is too different "
3961 "from a ``logarithmic'' one.")},
3962 { GNM_FUNC_HELP_NOTE, F_("You can use the above formula "
3963 "= a + b * ln(sign * (x - c)) "
3964 "or rearrange it to "
3965 "= (exp((y - a) / b)) / sign + c "
3966 "to compute unknown y's or x's, respectively.")},
3967 { GNM_FUNC_HELP_NOTE, F_("This is non-linear fitting by trial-and-error. "
3968 "The accuracy of `c' is: width of x-range -> rounded to the "
3969 "next smaller (10^integer), times 0.000001. There might be cases "
3970 "in which the returned fit is not the best possible.") },
3971 { GNM_FUNC_HELP_SEEALSO, "LOGREG,LINEST,LOGEST"},
3972 { GNM_FUNC_HELP_END }
3973 };
3974
3975 /* This function is not available in Excel.
3976 * It is intended for calculation of unknowns from a calibration curve.
3977 * It adapts well to some types of scientific data.
3978 * It does not do multidimensional regression or extra statistics.
3979 *
3980 * One could do this kind of non-linear fitting with a general solver, too,
3981 * but then the success depends on the choosing of suitable starting values.
3982 * Also, determination of `sign' would be complicated.
3983 */
3984 static GnmValue *
3985 gnumeric_logfit (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3986 {
3987 gnm_float *xs = NULL, *ys = NULL;
3988 GnmValue *result = NULL;
3989 int nx, ny, i;
3990 gnm_float *logfit_res = NULL;
3991
3992 if (argv[0] == NULL || !VALUE_IS_CELLRANGE (argv[0]))
3993 goto out;
3994 ys = collect_floats_value (argv[0], ei->pos,
3995 COLLECT_IGNORE_BLANKS | /* zeroing blanks
3996 is prone to produce unwanted results */
3997 COLLECT_IGNORE_STRINGS | /* dangerous, user
3998 should use validation tool to prevent erroneously inputting strings instead of
3999 numbers */
4000 COLLECT_IGNORE_BOOLS,
4001 &ny, &result);
4002 if (result)
4003 goto out;
4004 if (argv[1] == NULL || !VALUE_IS_CELLRANGE (argv[1]))
4005 goto out;
4006 xs = collect_floats_value (argv[1], ei->pos,
4007 COLLECT_IGNORE_BLANKS |
4008 COLLECT_IGNORE_STRINGS |
4009 COLLECT_IGNORE_BOOLS,
4010 &nx, &result);
4011 if (result)
4012 goto out;
4013 if (nx != ny || nx < 3) {
4014 result = value_new_error_VALUE (ei->pos);
4015 goto out;
4016 }
4017
4018 logfit_res = g_new (gnm_float, 5);
4019
4020 if (gnm_logarithmic_fit (xs, ys, nx, logfit_res) != GO_REG_ok) {
4021 result = value_new_error_NUM (ei->pos);
4022 goto out;
4023 }
4024
4025 result = value_new_array (5, 1);
4026 for (i=0; i<5; i++)
4027 value_array_set (result, i, 0,
4028 value_new_float (logfit_res[i]));
4029
4030 out:
4031 g_free (xs);
4032 g_free (ys);
4033 g_free (logfit_res);
4034 return result;
4035 }
4036
4037 /***************************************************************************/
4038
4039 static GnmFuncHelp const help_trend[] = {
4040 { GNM_FUNC_HELP_NAME, F_("TREND:estimates future values of a given data set using a least squares approximation")},
4041 { GNM_FUNC_HELP_ARG, F_("known_ys:vector of values of dependent variable")},
4042 { GNM_FUNC_HELP_ARG, F_("known_xs:array of values of independent variables, defaults to a single vector {1,\xe2\x80\xa6,n}")},
4043 { GNM_FUNC_HELP_ARG, F_("new_xs:array of x-values for which to estimate the y-values; defaults to @{known_xs}")},
4044 { GNM_FUNC_HELP_ARG, F_("affine:if true, the model contains a constant term, defaults to true")},
4045 { GNM_FUNC_HELP_NOTE, F_("If the length of @{known_ys} does not match the corresponding length of @{known_xs}, "
4046 "this function returns a #NUM! error.") },
4047 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, \xe2\x80\xa6, A5 contain numbers "
4048 "11.4, 17.3, 21.3, 25.9, and 40.1, and the cells B1, B2, ... "
4049 "B5 23.2, 25.8, 29.9, 33.5, and 42.7.") },
4050 { GNM_FUNC_HELP_EXAMPLES, F_("Then TREND(A1:A5,B1:B5) equals {12.1, 15.7, 21.6, 26.7, 39.7}.") },
4051 { GNM_FUNC_HELP_SEEALSO, "LINEST"},
4052 { GNM_FUNC_HELP_END }
4053 };
4054
4055 static GnmValue *
4056 gnumeric_trend (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
4057 {
4058 GnmRegData data;
4059 gnm_regression_stat_t *extra_stat = NULL;
4060 GORegressionResult regres;
4061 GnmValue *result;
4062 gnm_float *linres = NULL;
4063 gboolean affine;
4064 int i, j, dim, new_x_n, new_x_m;
4065 GnmValue const *new_x = NULL;
4066 gnm_float *new_x_val = NULL;
4067
4068 result = gnm_reg_data_collect (argv[0], argv[1], &data, ei->pos);
4069 if (result)
4070 return result;
4071 dim = data.dim;
4072
4073 affine = argv[3] ? value_get_as_checked_bool (argv[3]) : TRUE;
4074
4075 linres = g_new (gnm_float, dim + 1);
4076 extra_stat = gnm_regression_stat_new ();
4077 regres = gnm_linear_regression (data.xss, dim,
4078 data.ys, data.n, affine,
4079 linres, extra_stat);
4080 switch (regres) {
4081 case GO_REG_ok:
4082 case GO_REG_near_singular_good:
4083 break;
4084 default:
4085 result = value_new_error_NUM (ei->pos);
4086 goto out;
4087 }
4088
4089 if (argv[2] != NULL)
4090 new_x = argv[2];
4091 else if (argv[1] != NULL)
4092 new_x = argv[1];
4093
4094 if (dim == 1)
4095 data.type = gnm_reg_type_rect;
4096
4097 if (new_x == NULL) {
4098 result = value_new_array (1, data.n);
4099 for (i = 0; i < data.n; i++) {
4100 gnm_float res = linres[0];
4101 res += (i + 1) * linres[1];
4102 value_array_set (result, 0, i,
4103 value_new_float (res));
4104 }
4105 } else
4106 switch (data.type) {
4107 case gnm_reg_type_rect:
4108 new_x_n = value_area_get_height (new_x, ei->pos);
4109 new_x_m = value_area_get_width (new_x, ei->pos);
4110 result = value_new_array (new_x_m, new_x_n);
4111 for (i = 0; i < new_x_n; i++) {
4112 for (j = 0; j < new_x_m; j++) {
4113 gnm_float res = linres[0];
4114 new_x_val = gnm_reg_get_var
4115 (new_x, j, i, 0, 0, 1, ei->pos);
4116 if (new_x_val != NULL) {
4117 res += new_x_val[0] * linres[1];
4118 value_array_set
4119 (result, j, i,
4120 value_new_float (res));
4121 g_free (new_x_val);
4122 } else
4123 value_array_set
4124 (result, j, i,
4125 value_new_error_NA (ei->pos));
4126 }
4127 }
4128 break;
4129 case gnm_reg_type_vertical:
4130 if (dim != value_area_get_width (new_x, ei->pos)) {
4131 result = value_new_error_NUM (ei->pos);
4132 goto out;
4133 }
4134 new_x_n = value_area_get_height (new_x, ei->pos);
4135 result = value_new_array (1, new_x_n);
4136 for (i = 0; i < new_x_n; i++) {
4137 gnm_float res = linres[0];
4138 new_x_val = gnm_reg_get_var
4139 (new_x, 0, i, +1, 0, dim, ei->pos);
4140 if (new_x_val != NULL) {
4141 for (j = 0; j < dim; j++)
4142 res += new_x_val[j] * linres[j+1];
4143 value_array_set (result, 0, i,
4144 value_new_float (res));
4145 g_free (new_x_val);
4146 } else
4147 value_array_set (result, 0, i,
4148 value_new_error_NA (ei->pos));
4149 }
4150 break;
4151 case gnm_reg_type_horizontal:
4152 if (dim != value_area_get_height (new_x, ei->pos)) {
4153 result = value_new_error_NUM (ei->pos);
4154 goto out;
4155 }
4156 new_x_n = value_area_get_width (new_x, ei->pos);
4157 result = value_new_array (new_x_n, 1);
4158 for (i = 0; i < new_x_n; i++) {
4159 gnm_float res = linres[0];
4160 new_x_val = gnm_reg_get_var
4161 (new_x, i, 0, 0, +1, dim, ei->pos);
4162 if (new_x_val != NULL) {
4163 for (j = 0; j < dim; j++)
4164 res += new_x_val[j] * linres[j+1];
4165 value_array_set (result, i, 0,
4166 value_new_float (res));
4167 g_free (new_x_val);
4168 } else
4169 value_array_set (result, i, 0,
4170 value_new_error_NA (ei->pos));
4171 }
4172 break;
4173 }
4174
4175
4176 out:
4177 gnm_reg_data_free (&data);
4178 g_free (linres);
4179 gnm_regression_stat_destroy (extra_stat);
4180
4181 return result;
4182 }
4183
4184 /***************************************************************************/
4185
4186 static GnmFuncHelp const help_logest[] = {
4187 { GNM_FUNC_HELP_NAME, F_("LOGEST:exponential least square fit")},
4188 { GNM_FUNC_HELP_ARG, F_("known_ys:known y-values")},
4189 { GNM_FUNC_HELP_ARG, F_("known_xs:known x-values; default to an array {1, 2, 3, \xe2\x80\xa6}")},
4190 { GNM_FUNC_HELP_ARG, F_("affine:if true, the model contains a constant term, defaults to true")},
4191 { GNM_FUNC_HELP_ARG, F_("stat:if true, extra statistical information will be returned; defaults to FALSE")},
4192 { GNM_FUNC_HELP_DESCRIPTION, F_("LOGEST function applies the "
4193 "\xe2\x80\x9cleast squares\xe2\x80\x9d method to fit "
4194 "an exponential curve of the form\t"
4195 "y = b * m{1}^x{1} * m{2}^x{2}... to your data.") },
4196 { GNM_FUNC_HELP_DESCRIPTION, F_("LOGEST returns an array { m{n},m{n-1}, ...,m{1},b }.") },
4197 { GNM_FUNC_HELP_NOTE, F_("Extra statistical information is written below the regression "
4198 "line coefficients in the result array. Extra statistical "
4199 "information consists of four rows of data. In the first row "
4200 "the standard error values for the coefficients m1, (m2, ...), b "
4201 "are represented. The second row contains the square of R and "
4202 "the standard error for the y estimate. The third row contains "
4203 "the F-observed value and the degrees of freedom. The last row "
4204 "contains the regression sum of squares and the residual sum "
4205 "of squares.") },
4206 { GNM_FUNC_HELP_NOTE, F_("If @{known_ys} and @{known_xs} have unequal number of data points, "
4207 "this function returns a #NUM! error.") },
4208 { GNM_FUNC_HELP_SEEALSO, "GROWTH,TREND"},
4209 { GNM_FUNC_HELP_END }
4210 };
4211
4212 static GnmValue *
4213 gnumeric_logest (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
4214 {
4215 GnmRegData data;
4216 gnm_regression_stat_t *extra_stat = NULL;
4217 GORegressionResult regres;
4218 GnmValue *result;
4219 gnm_float *expres = NULL;
4220 gboolean affine, withstat;
4221 int i, dim;
4222
4223 result = gnm_reg_data_collect (argv[0], argv[1], &data, ei->pos);
4224 if (result)
4225 return result;
4226 dim = data.dim;
4227
4228 affine = argv[2] ? value_get_as_checked_bool (argv[2]) : TRUE;
4229 withstat = argv[3] ? value_get_as_checked_bool (argv[3]) : FALSE;
4230
4231 expres = g_new (gnm_float, dim + 1);
4232 extra_stat = gnm_regression_stat_new ();
4233 regres = gnm_exponential_regression (data.xss, dim,
4234 data.ys, data.n, affine,
4235 expres, extra_stat);
4236 switch (regres) {
4237 case GO_REG_ok:
4238 case GO_REG_near_singular_good:
4239 break;
4240 default:
4241 result = value_new_error_NUM (ei->pos);
4242 goto out;
4243 }
4244
4245 if (withstat) {
4246 result = value_new_array (dim + 1, 5);
4247
4248 value_array_set (result, 0, 2,
4249 value_new_float (extra_stat->sqr_r));
4250 value_array_set (result, 1, 2,
4251 value_new_float (gnm_sqrt (extra_stat->var)));
4252 value_array_set (result, 0, 3,
4253 value_new_float (extra_stat->F));
4254 value_array_set (result, 1, 3,
4255 value_new_float (extra_stat->df_resid));
4256 value_array_set (result, 0, 4,
4257 value_new_float (extra_stat->ss_reg));
4258 value_array_set (result, 1, 4,
4259 value_new_float (extra_stat->ss_resid));
4260 for (i = 0; i < dim; i++)
4261 value_array_set (result, dim - i - 1, 1,
4262 value_new_float (extra_stat->se[i+affine]));
4263 value_array_set (result, dim, 1,
4264 affine ? value_new_float (extra_stat->se[0])
4265 : value_new_error_NA (ei->pos));
4266 } else
4267 result = value_new_array (dim + 1, 1);
4268
4269 value_array_set (result, dim, 0, value_new_float (expres[0]));
4270 for (i = 0; i < dim; i++)
4271 value_array_set (result, dim - i - 1, 0,
4272 value_new_float (expres[i + 1]));
4273
4274 out:
4275 gnm_reg_data_free (&data);
4276 g_free (expres);
4277 gnm_regression_stat_destroy (extra_stat);
4278
4279 return result;
4280 }
4281
4282 /***************************************************************************/
4283
4284 static GnmFuncHelp const help_growth[] = {
4285 { GNM_FUNC_HELP_NAME, F_("GROWTH:exponential growth prediction")},
4286 { GNM_FUNC_HELP_ARG, F_("known_ys:known y-values")},
4287 { GNM_FUNC_HELP_ARG, F_("known_xs:known x-values; defaults to the array {1, 2, 3, \xe2\x80\xa6}")},
4288 { GNM_FUNC_HELP_ARG, F_("new_xs:x-values for which to estimate the y-values; defaults to @{known_xs}")},
4289 { GNM_FUNC_HELP_ARG, F_("affine:if true, the model contains a constant term, defaults to true")},
4290 { GNM_FUNC_HELP_DESCRIPTION, F_("GROWTH function applies the \xe2\x80\x9cleast "
4291 "squares\xe2\x80\x9d method to fit an "
4292 "exponential curve to your data and predicts "
4293 "the exponential "
4294 "growth by using this curve.")},
4295 { GNM_FUNC_HELP_DESCRIPTION, F_("GROWTH returns an array having one column and a row for each "
4296 "data point in @{new_xs}.") },
4297 { GNM_FUNC_HELP_EXAMPLES, "=GROWTH({1,2,3},{3,4,4},{6})" },
4298 { GNM_FUNC_HELP_NOTE, F_("If @{known_ys} and @{known_xs} have unequal number of data points, "
4299 "this function returns a #NUM! error.") },
4300 { GNM_FUNC_HELP_SEEALSO, "LOGEST,GROWTH,TREND"},
4301 { GNM_FUNC_HELP_END }
4302 };
4303
4304 static GnmValue *
4305 gnumeric_growth (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
4306 {
4307 gnm_float *ys, *xs, *nxs;
4308 int i, n, nnx;
4309 GnmValue *res = NULL;
4310 int dim = 1;
4311 gboolean affine;
4312 GORegressionResult regres;
4313 gnm_float expres[2];
4314 gboolean constp = FALSE;
4315
4316 if (argv[1]) {
4317 res = collect_float_pairs (argv[0], argv[1], ei->pos,
4318 COLLECT_IGNORE_BLANKS |
4319 COLLECT_IGNORE_STRINGS |
4320 COLLECT_IGNORE_BOOLS,
4321 &ys, &xs, &n, &constp);
4322 if (res)
4323 return res;
4324 } else {
4325 ys = collect_floats_value (argv[0], ei->pos,
4326 COLLECT_IGNORE_BLANKS |
4327 COLLECT_IGNORE_STRINGS |
4328 COLLECT_IGNORE_BOOLS,
4329 &n, &res);
4330 if (res)
4331 return res;
4332 xs = g_new (gnm_float, n);
4333 for (i = 0; i < n; i++)
4334 xs[i] = i + 1;
4335 }
4336
4337 if (argv[2] != NULL) {
4338 nxs = collect_floats_value (argv[2], ei->pos,
4339 COLLECT_IGNORE_BLANKS |
4340 COLLECT_IGNORE_STRINGS |
4341 COLLECT_IGNORE_BOOLS,
4342 &nnx, &res);
4343 if (res)
4344 goto out;
4345 } else {
4346 /* @{new_x}'s is assumed to be the same as @{known_x}'s */
4347 nxs = g_memdup (xs, n * sizeof (gnm_float));
4348 nnx = n;
4349 }
4350
4351 affine = argv[3] ? value_get_as_checked_bool (argv[3]) : TRUE;
4352
4353 if (nnx <= 0) {
4354 res = value_new_error_NUM (ei->pos);
4355 goto out;
4356 }
4357
4358 regres = gnm_exponential_regression (&xs, dim,
4359 ys, n, affine, expres, NULL);
4360 switch (regres) {
4361 case GO_REG_ok:
4362 case GO_REG_near_singular_good:
4363 break;
4364 default:
4365 res = value_new_error_NUM (ei->pos);
4366 goto out;
4367 }
4368
4369 res = value_new_array (1, nnx);
4370 for (i = 0; i < nnx; i++)
4371 value_array_set (res, 0, i,
4372 value_new_float (gnm_pow (expres[1], nxs[i]) *
4373 expres[0]));
4374
4375 out:
4376 if (!constp) {
4377 g_free (xs);
4378 g_free (ys);
4379 }
4380 g_free (nxs);
4381 return res;
4382 }
4383
4384 /***************************************************************************/
4385
4386 static GnmFuncHelp const help_forecast[] = {
4387 { GNM_FUNC_HELP_NAME, F_("FORECAST:estimates a future value according to existing values "
4388 "using simple linear regression")},
4389 { GNM_FUNC_HELP_ARG, F_("x:x-value whose matching y-value should be forecast")},
4390 { GNM_FUNC_HELP_ARG, F_("known_ys:known y-values")},
4391 { GNM_FUNC_HELP_ARG, F_("known_xs:known x-values")},
4392 { GNM_FUNC_HELP_DESCRIPTION, F_("This function estimates a future value according to "
4393 "existing values using simple linear regression.") },
4394 { GNM_FUNC_HELP_NOTE, F_("If @{known_xs} or @{known_ys} contains no data entries or different "
4395 "number of data entries, this function returns a #N/A error.") },
4396 { GNM_FUNC_HELP_NOTE, F_("If the variance of the @{known_xs} is zero, this function returns a #DIV/0 "
4397 "error.")},
4398 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
4399 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5 contain numbers "
4400 "11.4, 17.3, 21.3, 25.9, and 40.1, and the cells B1, B2, ... "
4401 "B5 23.2, 25.8, 29.9, 33.5, and 42.7.") },
4402 { GNM_FUNC_HELP_EXAMPLES, F_("Then FORECAST(7,A1:A5,B1:B5) equals -10.859397661.") },
4403 { GNM_FUNC_HELP_SEEALSO, "INTERCEPT,TREND"},
4404 { GNM_FUNC_HELP_END }
4405 };
4406
4407 static int
4408 range_forecast (gnm_float const *xs, gnm_float const *ys, int n, gnm_float *res, gpointer user)
4409 {
4410 gnm_float const *px = user;
4411 gnm_float linres[2];
4412 int dim = 1;
4413 gboolean affine = TRUE;
4414 GORegressionResult regres;
4415
4416 regres = gnm_linear_regression ((gnm_float **)&xs, dim,
4417 ys, n, affine, linres, NULL);
4418 switch (regres) {
4419 case GO_REG_ok:
4420 case GO_REG_near_singular_good:
4421 break;
4422 default:
4423 return 1;
4424 }
4425
4426 *res = linres[0] + (*px) * linres[1];
4427 return 0;
4428 }
4429
4430 static GnmValue *
4431 gnumeric_forecast (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
4432 {
4433 gnm_float x = value_get_as_float (argv[0]);
4434
4435 return float_range_function2d (argv[2], argv[1],
4436 ei,
4437 range_forecast,
4438 COLLECT_IGNORE_BLANKS |
4439 COLLECT_IGNORE_STRINGS |
4440 COLLECT_IGNORE_BOOLS,
4441 GNM_ERROR_VALUE,
4442 &x);
4443 }
4444
4445 /***************************************************************************/
4446
4447 static GnmFuncHelp const help_intercept[] = {
4448 { GNM_FUNC_HELP_NAME, F_("INTERCEPT:the intercept of a linear regression line")},
4449 { GNM_FUNC_HELP_ARG, F_("known_ys:known y-values")},
4450 { GNM_FUNC_HELP_ARG, F_("known_xs:known x-values")},
4451 { GNM_FUNC_HELP_NOTE, F_("If @{known_xs} or @{known_ys} contains no data entries or different "
4452 "number of data entries, this function returns a #N/A error.") },
4453 { GNM_FUNC_HELP_NOTE, F_("If the variance of the @{known_xs} is zero, this function returns "
4454 "#DIV/0 error.")},
4455 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
4456 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5 contain numbers "
4457 "11.4, 17.3, 21.3, 25.9, and 40.1, and the cells B1, B2, ... "
4458 "B5 23.2, 25.8, 29.9, 33.5, and 42.7.") },
4459 { GNM_FUNC_HELP_EXAMPLES, F_("Then INTERCEPT(A1:A5,B1:B5) equals -20.785117212.") },
4460 { GNM_FUNC_HELP_SEEALSO, "FORECAST,TREND"},
4461 { GNM_FUNC_HELP_END }
4462 };
4463
4464 static int
4465 range_intercept (gnm_float const *xs, gnm_float const *ys, int n, gnm_float *res)
4466 {
4467 gnm_float linres[2];
4468 int dim = 1;
4469 GORegressionResult regres;
4470
4471 regres = gnm_linear_regression ((gnm_float **)&xs, dim,
4472 ys, n, 1, linres, NULL);
4473 switch (regres) {
4474 case GO_REG_ok:
4475 case GO_REG_near_singular_good:
4476 break;
4477 default:
4478 return 1;
4479 }
4480
4481 *res = linres[0];
4482 return 0;
4483 }
4484
4485 static GnmValue *
4486 gnumeric_intercept (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
4487 {
4488 return float_range_function2 (argv[1], argv[0],
4489 ei,
4490 range_intercept,
4491 COLLECT_IGNORE_BLANKS |
4492 COLLECT_IGNORE_STRINGS |
4493 COLLECT_IGNORE_BOOLS,
4494 GNM_ERROR_VALUE);
4495 }
4496
4497 /***************************************************************************/
4498
4499 static GnmFuncHelp const help_slope[] = {
4500 { GNM_FUNC_HELP_NAME, F_("SLOPE:the slope of a linear regression line")},
4501 { GNM_FUNC_HELP_ARG, F_("known_ys:known y-values")},
4502 { GNM_FUNC_HELP_ARG, F_("known_xs:known x-values")},
4503 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
4504 { GNM_FUNC_HELP_NOTE, F_("If @{known_xs} or @{known_ys} contains no data entries or different "
4505 "number of data entries, this function returns a #N/A error.") },
4506 { GNM_FUNC_HELP_NOTE, F_("If the variance of the @{known_xs} is zero, this function returns "
4507 "#DIV/0 error.")},
4508 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5 contain numbers "
4509 "11.4, 17.3, 21.3, 25.9, and 40.1, and the cells B1, B2, ... "
4510 "B5 23.2, 25.8, 29.9, 33.5, and 42.7.") },
4511 { GNM_FUNC_HELP_EXAMPLES, F_("Then SLOPE(A1:A5,B1:B5) equals 1.417959936.") },
4512 { GNM_FUNC_HELP_SEEALSO, "STDEV,STDEVPA"},
4513 { GNM_FUNC_HELP_END }
4514 };
4515
4516 static int
4517 range_slope (gnm_float const *xs, gnm_float const *ys, int n, gnm_float *res)
4518 {
4519 gnm_float linres[2];
4520 int dim = 1;
4521 GORegressionResult regres;
4522
4523 regres = gnm_linear_regression ((gnm_float **)&xs, dim,
4524 ys, n, 1, linres, NULL);
4525 switch (regres) {
4526 case GO_REG_ok:
4527 case GO_REG_near_singular_good:
4528 break;
4529 default:
4530 return 1;
4531 }
4532
4533 *res = linres[1];
4534 return 0;
4535 }
4536
4537 static GnmValue *
4538 gnumeric_slope (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
4539 {
4540 return float_range_function2 (argv[1], argv[0],
4541 ei,
4542 range_slope,
4543 COLLECT_IGNORE_BLANKS |
4544 COLLECT_IGNORE_STRINGS |
4545 COLLECT_IGNORE_BOOLS,
4546 GNM_ERROR_VALUE);
4547 }
4548
4549 /***************************************************************************/
4550
4551 static GnmFuncHelp const help_subtotal[] = {
4552 { GNM_FUNC_HELP_NAME, F_("SUBTOTAL:the subtotal of the given list of arguments")},
4553 { GNM_FUNC_HELP_ARG, F_("function_nbr:determines which function to use according to the following table:\n"
4554 "\t1 AVERAGE\n"
4555 "\t2 COUNT\n"
4556 "\t3 COUNTA\n"
4557 "\t4 MAX\n"
4558 "\t5 MIN\n"
4559 "\t6 PRODUCT\n"
4560 "\t7 STDEV\n"
4561 "\t8 STDEVP\n"
4562 "\t9 SUM\n"
4563 "\t10 VAR\n"
4564 "\t11 VARP"
4565 )},
4566 { GNM_FUNC_HELP_ARG, F_("ref1:first value")},
4567 { GNM_FUNC_HELP_ARG, F_("ref2:second value")},
4568 { GNM_FUNC_HELP_EXCEL, F_("This function is Excel compatible.") },
4569 { GNM_FUNC_HELP_EXAMPLES, F_("Let us assume that the cells A1, A2, ..., A5 contain numbers 23, 27, 28, 33, and 39.") },
4570 { GNM_FUNC_HELP_EXAMPLES, F_("Then SUBTOTAL(1,A1:A5) equals 30. "
4571 "SUBTOTAL(6,A1:A5) equals 22378356. "
4572 "SUBTOTAL(7,A1:A5) equals 6.164414003. "
4573 "SUBTOTAL(9,A1:A5) equals 150. "
4574 "SUBTOTAL(11,A1:A5) equals 30.4.") },
4575 { GNM_FUNC_HELP_SEEALSO, "COUNT,SUM"},
4576 { GNM_FUNC_HELP_END }
4577 };
4578
4579 static GnmValue *
4580 gnumeric_subtotal (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
4581 {
4582 GnmExpr const *expr;
4583 GnmValue *val;
4584 int fun_nbr;
4585 float_range_function_t func;
4586 GnmStdError err = GNM_ERROR_DIV0;
4587 CollectFlags flags_errors = 0;
4588 CollectFlags flags_strings = COLLECT_IGNORE_STRINGS;
4589 CollectFlags flags_bools = COLLECT_IGNORE_BOOLS;
4590 CollectFlags flags_other = COLLECT_IGNORE_BLANKS | COLLECT_IGNORE_SUBTOTAL;
4591
4592 if (argc == 0)
4593 return value_new_error_NUM (ei->pos);
4594
4595 expr = argv[0];
4596 if (expr == NULL)
4597 return value_new_error_NUM (ei->pos);
4598
4599 val = gnm_expr_eval (expr, ei->pos, GNM_EXPR_EVAL_SCALAR_NON_EMPTY);
4600 if (VALUE_IS_ERROR (val))
4601 return val;
4602 fun_nbr = value_get_as_int (val);
4603 value_release (val);
4604
4605 /* Skip the first node */
4606 argc--;
4607 argv++;
4608
4609 switch (fun_nbr) {
4610 case 1: func = gnm_range_average; break;
4611 case 2: flags_errors = COLLECT_IGNORE_ERRORS;
4612 func = gnm_range_count; break;
4613 case 3: flags_errors = COLLECT_ZERO_ERRORS;
4614 flags_strings = COLLECT_ZERO_STRINGS;
4615 flags_bools = COLLECT_ZEROONE_BOOLS;
4616 func = gnm_range_count; break;
4617 case 4: err = GNM_ERROR_VALUE;
4618 func = range_max0; break;
4619 case 5: err = GNM_ERROR_VALUE;
4620 func = range_min0; break;
4621 case 6: err = GNM_ERROR_VALUE;
4622 func = gnm_range_product; break;
4623 case 7: func = gnm_range_stddev_est; break;
4624 case 8: func = gnm_range_stddev_pop; break;
4625 case 9: err = GNM_ERROR_VALUE;
4626 func = gnm_range_sum; break;
4627 case 10: func = gnm_range_var_est; break;
4628 case 11: func = gnm_range_var_pop; break;
4629
4630 default:
4631 return value_new_error_NUM (ei->pos);
4632 }
4633
4634 return float_range_function (argc, argv, ei, func,
4635 flags_errors | flags_strings | flags_bools | flags_other,
4636 err);
4637 }
4638
4639 /***************************************************************************/
4640
4641 static GnmFuncHelp const help_cronbach[] = {
4642 { GNM_FUNC_HELP_NAME, F_("CRONBACH:Cronbach's alpha")},
4643 { GNM_FUNC_HELP_ARG, F_("ref1:first data set")},
4644 { GNM_FUNC_HELP_ARG, F_("ref2:second data set")},
4645 { GNM_FUNC_HELP_SEEALSO, "VAR" },
4646 { GNM_FUNC_HELP_END }
4647 };
4648
4649 static void
4650 free_values (GnmValue **values, int top)
4651 {
4652 int i;
4653
4654 for (i = 0; i < top; i++)
4655 if (values [i])
4656 value_release (values [i]);
4657 g_free (values);
4658 }
4659
4660 static GnmValue *
4661 function_marshal_arg (GnmFuncEvalInfo *ei,
4662 GnmExpr const *t,
4663 GnmValue **type_mismatch)
4664 {
4665 GnmValue *v = gnm_expr_eval (t, ei->pos,
4666 GNM_EXPR_EVAL_PERMIT_NON_SCALAR |
4667 GNM_EXPR_EVAL_WANT_REF);
4668
4669 if (!VALUE_IS_ARRAY (v) && !VALUE_IS_CELLRANGE (v))
4670 *type_mismatch = value_new_error_VALUE (ei->pos);
4671 else
4672 *type_mismatch = NULL;
4673
4674 return v;
4675 }
4676
4677 static GnmValue *
4678 gnumeric_cronbach (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
4679 {
4680 int i, j;
4681 GnmValue **values;
4682 gnm_float sum_variance = 0.0;
4683 gnm_float sum_covariance = 0.0;
4684
4685 if (argc < 2)
4686 return value_new_error_VALUE (ei->pos);
4687
4688 for (i = 0; i < argc; i++) {
4689 GnmValue *fl_val =
4690 float_range_function (1, argv + i, ei,
4691 gnm_range_var_pop, 0,
4692 GNM_ERROR_VALUE);
4693 if (!VALUE_IS_NUMBER (fl_val))
4694 return fl_val;
4695 sum_variance += value_get_as_float (fl_val);
4696 value_release (fl_val);
4697 }
4698
4699 values = g_new0 (GnmValue *, argc);
4700
4701 for (i = 0; i < argc; i++) {
4702 GnmValue *type_mismatch;
4703
4704 values[i] = function_marshal_arg (ei, argv[i], &type_mismatch);
4705 if (type_mismatch || values[i] == NULL) {
4706 free_values (values, i + 1);
4707 if (type_mismatch)
4708 return type_mismatch;
4709 else
4710 return value_new_error_VALUE (ei->pos);
4711 }
4712 }
4713
4714 g_return_val_if_fail (i == argc, value_new_error_VALUE (ei->pos));
4715
4716 /* We now have an array of array values and an expression list */
4717 for (i = 0; i < argc; ++i) {
4718 for (j = i + 1; j < argc; ++j) {
4719 GnmValue *fl_val;
4720 fl_val = float_range_function2 (values[i], values[j],
4721 ei,
4722 gnm_range_covar_pop, 0,
4723 GNM_ERROR_VALUE);
4724 if (!VALUE_IS_NUMBER (fl_val)) {
4725 free_values (values, argc);
4726 return fl_val;
4727 }
4728 sum_covariance += value_get_as_float (fl_val);
4729 value_release (fl_val);
4730 }
4731 }
4732
4733 free_values (values, argc);
4734 return value_new_float
4735 (argc * (1 - sum_variance / (sum_variance + 2 * sum_covariance)) / (argc - 1));
4736 }
4737
4738 /***************************************************************************/
4739
4740 static GnmFuncHelp const help_geomdist[] = {
4741 { GNM_FUNC_HELP_NAME, F_("GEOMDIST:probability mass or cumulative distribution function of the geometric distribution")},
4742 { GNM_FUNC_HELP_ARG, F_("k:number of trials")},
4743 { GNM_FUNC_HELP_ARG, F_("p:probability of success in any trial")},
4744 { GNM_FUNC_HELP_ARG, F_("cumulative:whether to evaluate the mass function or the cumulative distribution function")},
4745 { GNM_FUNC_HELP_NOTE, F_("If @{k} < 0 this function returns a #NUM! error.") },
4746 { GNM_FUNC_HELP_NOTE, F_("If @{p} < 0 or @{p} > 1 this function returns a #NUM! error.") },
4747 { GNM_FUNC_HELP_NOTE, F_("If @{cumulative} is neither TRUE nor FALSE this function returns a #VALUE! error.") },
4748 { GNM_FUNC_HELP_EXAMPLES, "=GEOMDIST(2,0.4,TRUE)" },
4749 { GNM_FUNC_HELP_SEEALSO, "RANDGEOM"},
4750 { GNM_FUNC_HELP_END }
4751 };
4752
4753 static GnmValue *
4754 gnumeric_geomdist (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
4755 {
4756 gnm_float k = gnm_fake_floor (value_get_as_float (argv[0]));
4757 gnm_float p = value_get_as_float (argv[1]);
4758 gboolean cum = value_get_as_checked_bool (argv[2]);
4759
4760 if (p < 0 || p > 1 || k < 0)
4761 return value_new_error_NUM (ei->pos);
4762
4763 if (cum)
4764 return value_new_float (pgeom (k, p, TRUE, FALSE));
4765 else
4766 return value_new_float (dgeom (k, p, FALSE));
4767 }
4768
4769 /***************************************************************************/
4770
4771 static GnmFuncHelp const help_logistic[] = {
4772 { GNM_FUNC_HELP_NAME, F_("LOGISTIC:probability density function of the logistic distribution")},
4773 { GNM_FUNC_HELP_ARG, F_("x:number")},
4774 { GNM_FUNC_HELP_ARG, F_("a:scale parameter")},
4775 { GNM_FUNC_HELP_EXAMPLES, "=LOGISTIC(0.4,1)" },
4776 { GNM_FUNC_HELP_SEEALSO, "RANDLOGISTIC"},
4777 { GNM_FUNC_HELP_END }
4778 };
4779
4780 static gnm_float
4781 random_logistic_pdf (gnm_float x, gnm_float a)
4782 {
4783 gnm_float u = gnm_exp (-gnm_abs (x) / a);
4784
4785 return u / (gnm_abs (a) * (1 + u) * (1 + u));
4786 }
4787
4788 static GnmValue *
4789 gnumeric_logistic (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
4790 {
4791 gnm_float x = value_get_as_float (argv[0]);
4792 gnm_float a = value_get_as_float (argv[1]);
4793
4794 if (a <= 0)
4795 return value_new_error_NUM (ei->pos);
4796
4797 return value_new_float (random_logistic_pdf (x, a));
4798 }
4799
4800 /***************************************************************************/
4801
4802 static GnmFuncHelp const help_pareto[] = {
4803 { GNM_FUNC_HELP_NAME, F_("PARETO:probability density function of the Pareto distribution")},
4804 { GNM_FUNC_HELP_ARG, F_("x:number")},
4805 { GNM_FUNC_HELP_ARG, F_("a:exponent")},
4806 { GNM_FUNC_HELP_ARG, F_("b:scale parameter")},
4807 { GNM_FUNC_HELP_EXAMPLES, "=PARETO(0.6,1,2)" },
4808 { GNM_FUNC_HELP_SEEALSO, "RANDPARETO"},
4809 { GNM_FUNC_HELP_END }
4810 };
4811
4812 static gnm_float
4813 random_pareto_pdf (gnm_float x, gnm_float a, gnm_float b)
4814 {
4815 if (x >= b)
4816 return (a / b) / gnm_pow (x / b, a + 1);
4817 else
4818 return 0;
4819 }
4820
4821 static GnmValue *
4822 gnumeric_pareto (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
4823 {
4824 gnm_float x = value_get_as_float (argv[0]);
4825 gnm_float a = value_get_as_float (argv[1]);
4826 gnm_float b = value_get_as_float (argv[2]);
4827
4828 if (a <= 0 || b <= 0)
4829 return value_new_error_NUM (ei->pos);
4830
4831 return value_new_float (random_pareto_pdf (x, a, b));
4832 }
4833
4834 /***************************************************************************/
4835
4836 static GnmFuncHelp const help_rayleigh[] = {
4837 { GNM_FUNC_HELP_NAME, F_("RAYLEIGH:probability density function of the Rayleigh distribution")},
4838 { GNM_FUNC_HELP_ARG, F_("x:number")},
4839 { GNM_FUNC_HELP_ARG, F_("sigma:scale parameter")},
4840 { GNM_FUNC_HELP_EXAMPLES, "=RAYLEIGH(0.4,1)" },
4841 { GNM_FUNC_HELP_SEEALSO, "RANDRAYLEIGH"},
4842 { GNM_FUNC_HELP_END }
4843 };
4844
4845 static GnmValue *
4846 gnumeric_rayleigh (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
4847 {
4848 gnm_float x = value_get_as_float (argv[0]);
4849 gnm_float sigma = value_get_as_float (argv[1]);
4850
4851 return value_new_float (drayleigh (x, sigma, FALSE));
4852 }
4853
4854 /***************************************************************************/
4855
4856 static GnmFuncHelp const help_rayleightail[] = {
4857 { GNM_FUNC_HELP_NAME, F_("RAYLEIGHTAIL:probability density function of the Rayleigh tail distribution")},
4858 { GNM_FUNC_HELP_ARG, F_("x:number")},
4859 { GNM_FUNC_HELP_ARG, F_("a:lower limit")},
4860 { GNM_FUNC_HELP_ARG, F_("sigma:scale parameter")},
4861 { GNM_FUNC_HELP_EXAMPLES, "=RAYLEIGHTAIL(0.6,0.3,1)" },
4862 { GNM_FUNC_HELP_SEEALSO, "RANDRAYLEIGHTAIL"},
4863 { GNM_FUNC_HELP_END }
4864 };
4865
4866 static gnm_float
4867 random_rayleigh_tail_pdf (gnm_float x, gnm_float a, gnm_float sigma)
4868 {
4869 if (x < a)
4870 return 0;
4871 else {
4872 gnm_float u = x / sigma ;
4873 gnm_float v = a / sigma ;
4874
4875 return (u / sigma) * gnm_exp ((v + u) * (v - u) / 2.0) ;
4876 }
4877 }
4878
4879 static GnmValue *
4880 gnumeric_rayleightail (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
4881 {
4882 gnm_float x = value_get_as_float (argv[0]);
4883 gnm_float a = value_get_as_float (argv[1]);
4884 gnm_float sigma = value_get_as_float (argv[2]);
4885
4886 if (sigma <= 0)
4887 return value_new_error_NUM (ei->pos);
4888
4889 return value_new_float (random_rayleigh_tail_pdf (x, a, sigma));
4890 }
4891
4892 /***************************************************************************/
4893
4894 static GnmFuncHelp const help_exppowdist[] = {
4895 { GNM_FUNC_HELP_NAME, F_("EXPPOWDIST:the probability density function of the "
4896 "Exponential Power distribution")},
4897 { GNM_FUNC_HELP_ARG, F_("x:number")},
4898 { GNM_FUNC_HELP_ARG, F_("a:scale parameter")},
4899 { GNM_FUNC_HELP_ARG, F_("b:scale parameter")},
4900 { GNM_FUNC_HELP_DESCRIPTION, F_(
4901 "This distribution has been recommended for lifetime analysis "
4902 "when a U-shaped hazard function is desired. "
4903 "This corresponds to rapid failure once the product starts to "
4904 "wear out after a period of steady or even improving reliability.") },
4905 { GNM_FUNC_HELP_EXAMPLES, "=EXPPOWDIST(0.4,1,2)" },
4906 { GNM_FUNC_HELP_SEEALSO, "RANDEXPPOW"},
4907 { GNM_FUNC_HELP_END }
4908 };
4909 /* Part of help text quoted from the PEXPDF manpage of the DATAPLOT program
4910 * by NIST. */
4911
4912 static GnmValue *
4913 gnumeric_exppowdist (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
4914 {
4915 gnm_float x = value_get_as_float (argv[0]);
4916 gnm_float a = value_get_as_float (argv[1]);
4917 gnm_float b = value_get_as_float (argv[2]);
4918
4919 /* FIXME: Check this condition. */
4920 if (b <= 0)
4921 return value_new_error_NUM (ei->pos);
4922
4923 return value_new_float (random_exppow_pdf (x, a, b));
4924 }
4925
4926 /***************************************************************************/
4927
4928 static GnmFuncHelp const help_laplace[] = {
4929 { GNM_FUNC_HELP_NAME, F_("LAPLACE:probability density function of the Laplace distribution")},
4930 { GNM_FUNC_HELP_ARG, F_("x:number")},
4931 { GNM_FUNC_HELP_ARG, F_("a:mean")},
4932 { GNM_FUNC_HELP_EXAMPLES, "=LAPLACE(0.4,1)" },
4933 { GNM_FUNC_HELP_SEEALSO, "RANDLAPLACE"},
4934 { GNM_FUNC_HELP_END }
4935 };
4936
4937 static GnmValue *
4938 gnumeric_laplace (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
4939 {
4940 gnm_float x = value_get_as_float (argv[0]);
4941 gnm_float a = value_get_as_float (argv[1]);
4942
4943 if (a <= 0)
4944 return value_new_error_NUM (ei->pos);
4945
4946 return value_new_float (random_laplace_pdf (x, a));
4947 }
4948
4949 /***************************************************************************/
4950
4951 static GnmFuncHelp const help_permutationa[] = {
4952 { GNM_FUNC_HELP_NAME, F_("PERMUTATIONA:the number of permutations of @{y} objects chosen from @{x} objects with repetition allowed")},
4953 { GNM_FUNC_HELP_ARG, F_("x:total number of objects")},
4954 { GNM_FUNC_HELP_ARG, F_("y:number of selected objects")},
4955 { GNM_FUNC_HELP_NOTE, F_("If both @{x} and @{y} equal 0, PERMUTATIONA returns 1.") },
4956 { GNM_FUNC_HELP_NOTE, F_("If @{x} < 0 or @{y} < 0, PERMUTATIONA returns #NUM!") },
4957 { GNM_FUNC_HELP_NOTE, F_("If @{x} or @{y} are not integers, they are truncated.") },
4958 { GNM_FUNC_HELP_ODF, F_("This function is OpenFormula compatible.") },
4959 { GNM_FUNC_HELP_EXAMPLES, "=PERMUTATIONA(2,7)" },
4960 { GNM_FUNC_HELP_EXAMPLES, "=PERMUTATIONA(2.3,7.6)" },
4961 { GNM_FUNC_HELP_EXAMPLES, "=PERMUTATIONA(0,0)" },
4962 { GNM_FUNC_HELP_SEEALSO, "POWER"},
4963 { GNM_FUNC_HELP_END}
4964 };
4965
4966 static GnmValue *
4967 gnumeric_permutationa (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
4968 {
4969 gnm_float x = gnm_fake_floor (value_get_as_float (argv[0]));
4970 gnm_float y = gnm_fake_floor (value_get_as_float (argv[1]));
4971
4972 if (x < 0 || y < 0)
4973 return value_new_error_NUM (ei->pos);
4974 else if (y == 0)
4975 return value_new_float (1);
4976 else
4977 return value_new_float (gnm_pow (x, y));
4978 }
4979
4980 /***************************************************************************/
4981
4982 static GnmFuncHelp const help_lkstest[] = {
4983 { GNM_FUNC_HELP_NAME, F_("LKSTEST:Lilliefors (Kolmogorov-Smirnov) Test of Normality") },
4984 { GNM_FUNC_HELP_ARG, F_("x:array of sample values") },
4985 { GNM_FUNC_HELP_DESCRIPTION, F_("This function returns an array with the first row giving the p-value of the Lilliefors (Kolmogorov-Smirnov) Test,"
4986 " the second row the test statistic of the test, and the third the number of observations in the sample.")},
4987 { GNM_FUNC_HELP_NOTE, F_("If there are less than 5 sample values, LKSTEST returns #VALUE!") },
4988 { GNM_FUNC_HELP_SEEALSO, "CHITEST,ADTEST,SFTEST,CVMTEST" },
4989 { GNM_FUNC_HELP_EXTREF, F_("wiki:en:Lilliefors_test") },
4990 { GNM_FUNC_HELP_END }
4991 };
4992
4993 static GnmValue *
4994 gnumeric_lkstest (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
4995 {
4996 gnm_float *xs;
4997 int n;
4998 GnmValue *result = NULL;
4999 gnm_float mu = 0.;
5000 gnm_float sigma = 1.;
5001
5002 xs = collect_floats_value (argv[0], ei->pos,
5003 COLLECT_IGNORE_STRINGS |
5004 COLLECT_IGNORE_BOOLS |
5005 COLLECT_IGNORE_BLANKS |
5006 COLLECT_ORDER_IRRELEVANT,
5007 &n, &result);
5008
5009 if (result)
5010 goto out;
5011
5012 result = value_new_array (1, 3);
5013 value_array_set (result, 0, 2,
5014 value_new_int (n));
5015
5016 if ((n < 5) || gnm_range_average (xs, n, &mu)
5017 || gnm_range_stddev_est (xs, n, &sigma)) {
5018 value_array_set (result, 0, 0,
5019 value_new_error_VALUE (ei->pos));
5020 value_array_set (result, 0, 1,
5021 value_new_error_VALUE (ei->pos));
5022 } else {
5023 int i;
5024 gnm_float dplus, dminus;
5025 gnm_float p, nd;
5026 gnm_float *ys;
5027 gnm_float val;
5028 gnm_float stat;
5029
5030 ys = range_sort (xs, n);
5031
5032 val = pnorm (ys[0], mu, sigma, TRUE, FALSE);
5033 dplus = 1./(gnm_float)n - val;
5034 dminus = val;
5035
5036 for (i = 1; i < n; i++) {
5037 gnm_float one_dplus, one_dminus;
5038 val = pnorm (ys[i], mu, sigma, TRUE, FALSE);
5039 one_dplus = (i + 1)/(gnm_float)n - val;
5040 one_dminus = val - i/(gnm_float)n;
5041
5042 if (one_dplus > dplus)
5043 dplus = one_dplus;
5044 if (one_dminus > dminus)
5045 dminus = one_dminus;
5046 }
5047
5048 stat = ((dplus < dminus) ? dminus : dplus);
5049
5050 value_array_set (result, 0, 1,
5051 value_new_float (stat));
5052
5053 g_free (ys);
5054
5055 if (n > 100) {
5056 stat = stat * gnm_pow (n/100., 0.49);
5057 nd = 100.;
5058 } else
5059 nd = n;
5060
5061 p = gnm_exp (-7.01256 * stat * stat * (nd + 2.78019) + 2.99587 * stat * gnm_sqrt (nd + 2.78019)
5062 - 0.122119 + 0.974598/gnm_sqrt(nd) + 1.67997/nd);
5063
5064 if (p > 0.1) {
5065 stat = (gnm_sqrt (nd) - 0.01 + 0.85/gnm_sqrt (nd)) * stat;
5066 if (stat <= 0.302)
5067 p = 1.;
5068 else if (stat <= 0.5)
5069 p = 2.76773 - 19.828315 * stat
5070 + 80.709644 * stat * stat
5071 - 138.55152 * stat * stat * stat
5072 + 81.218052 * stat * stat * stat * stat;
5073 else if (stat <= 0.9)
5074 p = -4.901232 + 40.662806 * stat
5075 - 97.490286 * stat * stat
5076 + 94.029866 * stat * stat * stat
5077 - 32.355711 * stat * stat * stat * stat;
5078 else if (stat <= 1.31)
5079 p = 6.198765 - 19.558097 * stat
5080 + 23.186922 * stat * stat
5081 - 12.234627 * stat * stat * stat
5082 + 2.423045 * stat * stat * stat * stat;
5083 else
5084 p = 0.;
5085 }
5086
5087 value_array_set (result, 0, 0,
5088 value_new_float (p));
5089 }
5090
5091 out:
5092 g_free (xs);
5093
5094 return result;
5095 }
5096
5097 /***************************************************************************/
5098
5099 static GnmFuncHelp const help_sftest[] = {
5100 { GNM_FUNC_HELP_NAME, F_("SFTEST:Shapiro-Francia Test of Normality") },
5101 { GNM_FUNC_HELP_ARG, F_("x:array of sample values") },
5102 { GNM_FUNC_HELP_DESCRIPTION, F_("This function returns an array with the first row giving the p-value of the Shapiro-Francia Test,"
5103 " the second row the test statistic of the test, and the third the number of observations in the sample.")},
5104 { GNM_FUNC_HELP_NOTE, F_("If there are less than 5 or more than 5000 sample values, SFTEST returns #VALUE!") },
5105 { GNM_FUNC_HELP_SEEALSO, "CHITEST,ADTEST,LKSTEST,CVMTEST" },
5106 { GNM_FUNC_HELP_END }
5107 };
5108
5109 static GnmValue *
5110 gnumeric_sftest (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
5111 {
5112 gnm_float *xs;
5113 int n;
5114 GnmValue *result = NULL;
5115
5116 xs = collect_floats_value (argv[0], ei->pos,
5117 COLLECT_IGNORE_STRINGS |
5118 COLLECT_IGNORE_BOOLS |
5119 COLLECT_IGNORE_BLANKS |
5120 COLLECT_ORDER_IRRELEVANT,
5121 &n, &result);
5122
5123 if (result)
5124 goto out;
5125
5126 result = value_new_array (1, 3);
5127 value_array_set (result, 0, 2,
5128 value_new_int (n));
5129
5130 if ((n < 5) || (n > 5000)) {
5131 value_array_set (result, 0, 0,
5132 value_new_error_VALUE (ei->pos));
5133 value_array_set (result, 0, 1,
5134 value_new_error_VALUE (ei->pos));
5135 } else {
5136 int i;
5137 gnm_float stat_;
5138 gnm_float *ys;
5139 gnm_float *zs;
5140
5141 ys = range_sort (xs, n);
5142 zs = g_new (gnm_float, n);
5143
5144 for (i = 0; i < n; i++)
5145 zs[i] = qnorm ((((gnm_float)(i+1))-3./8.)/(n+0.25), 0., 1., TRUE, FALSE);
5146
5147 if (gnm_range_correl_pop (ys, zs, n, &stat_)) {
5148 value_array_set (result, 0, 0,
5149 value_new_error_VALUE (ei->pos));
5150 value_array_set (result, 0, 1,
5151 value_new_error_VALUE (ei->pos));
5152 } else {
5153 gnm_float p;
5154 gnm_float u, v, mu, sig;
5155
5156 stat_ = stat_ * stat_;
5157
5158 value_array_set (result, 0, 1,
5159 value_new_float (stat_));
5160
5161 u = gnm_log (n);
5162 v = gnm_log (u);
5163 mu = -1.2725 + 1.0521 * (v - u);
5164 sig = 1.0308 - 0.26758 * (v + 2./u);
5165
5166 p = pnorm (gnm_log1p (-stat_), mu, sig, FALSE, FALSE);
5167
5168 value_array_set (result, 0, 0,
5169 value_new_float (p));
5170 }
5171 g_free (ys);
5172 g_free (zs);
5173 }
5174
5175 out:
5176 g_free (xs);
5177
5178 return result;
5179 }
5180
5181 /***************************************************************************/
5182
5183 static GnmFuncHelp const help_cvmtest[] = {
5184 { GNM_FUNC_HELP_NAME, F_("CVMTEST:Cram\xc3\xa9r-von Mises Test of Normality") },
5185 { GNM_FUNC_HELP_ARG, F_("x:array of sample values") },
5186 { GNM_FUNC_HELP_DESCRIPTION, F_("This function returns an array with the first row giving the p-value of the Cram\xc3\xa9r-von Mises Test,"
5187 " the second row the test statistic of the test, and the third the number of observations in the sample.")},
5188 { GNM_FUNC_HELP_NOTE, F_("If there are less than 8 sample values, CVMTEST returns #VALUE!") },
5189 { GNM_FUNC_HELP_SEEALSO, "CHITEST,ADTEST,LKSTEST,SFTEST" },
5190 { GNM_FUNC_HELP_EXTREF, F_("wiki:en:Cramér–von-Mises_criterion") },
5191 { GNM_FUNC_HELP_END }
5192 };
5193
5194 static GnmValue *
5195 gnumeric_cvmtest (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
5196 {
5197 gnm_float *xs;
5198 int n;
5199 GnmValue *result = NULL;
5200 gnm_float mu = 0.;
5201 gnm_float sigma = 1.;
5202
5203 xs = collect_floats_value (argv[0], ei->pos,
5204 COLLECT_IGNORE_STRINGS |
5205 COLLECT_IGNORE_BOOLS |
5206 COLLECT_IGNORE_BLANKS |
5207 COLLECT_ORDER_IRRELEVANT,
5208 &n, &result);
5209
5210 if (result)
5211 goto out;
5212
5213 result = value_new_array (1, 3);
5214 value_array_set (result, 0, 2,
5215 value_new_int (n));
5216
5217 if ((n < 8) || gnm_range_average (xs, n, &mu)
5218 || gnm_range_stddev_est (xs, n, &sigma)) {
5219 value_array_set (result, 0, 0,
5220 value_new_error_VALUE (ei->pos));
5221 value_array_set (result, 0, 1,
5222 value_new_error_VALUE (ei->pos));
5223 } else {
5224 int i;
5225 gnm_float total = 0.;
5226 gnm_float p;
5227 gnm_float *ys;
5228
5229 ys = range_sort (xs, n);
5230
5231 for (i = 0; i < n; i++) {
5232 gnm_float val = pnorm (ys[i], mu, sigma, TRUE, FALSE);
5233 gnm_float delta;
5234 delta = val - (2*i+1)/(2. * n);
5235 total += (delta * delta);
5236 }
5237
5238 total += (1 / (12 * (gnm_float)n));
5239 value_array_set (result, 0, 1,
5240 value_new_float (total));
5241
5242 g_free (ys);
5243
5244 total *= (1 + 0.5 / n);
5245 if (total < 0.0275)
5246 p = 1. - gnm_exp (-13.953 + 775.5 * total - 12542.61 * total * total);
5247 else if (total < 0.051)
5248 p = 1. - gnm_exp (-5.903 + 179.546 * total - 1515.29 * total * total);
5249 else if (total < 0.092)
5250 p = gnm_exp (0.886 - 31.62 * total - 10.897 * total * total);
5251 else if (total < 1.)
5252 p = gnm_exp (1.111 - 34.242 * total + 12.832 * total * total);
5253 else
5254 p = 0.;
5255
5256 value_array_set (result, 0, 0,
5257 value_new_float (p));
5258 }
5259
5260 out:
5261 g_free (xs);
5262
5263 return result;
5264 }
5265
5266 /***************************************************************************/
5267
5268 static GnmFuncHelp const help_adtest[] = {
5269 { GNM_FUNC_HELP_NAME, F_("ADTEST:Anderson-Darling Test of Normality") },
5270 { GNM_FUNC_HELP_ARG, F_("x:array of sample values") },
5271 { GNM_FUNC_HELP_DESCRIPTION, F_("This function returns an array with the first row giving the p-value of the Anderson-Darling Test,"
5272 " the second row the test statistic of the test, and the third the number of observations in the sample.")},
5273 { GNM_FUNC_HELP_NOTE, F_("If there are less than 8 sample values, ADTEST returns #VALUE!") },
5274 { GNM_FUNC_HELP_SEEALSO, "CHITEST,CVMTEST,LKSTEST,SFTEST" },
5275 { GNM_FUNC_HELP_EXTREF, F_("wiki:en:Anderson–Darling_test") },
5276 { GNM_FUNC_HELP_END }
5277 };
5278
5279 static GnmValue *
5280 gnumeric_adtest (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
5281 {
5282 gnm_float *xs;
5283 int n;
5284 GnmValue *result = NULL;
5285 gnm_float statistics = 0.;
5286 gnm_float p = 0.;
5287
5288 xs = collect_floats_value (argv[0], ei->pos,
5289 COLLECT_IGNORE_STRINGS |
5290 COLLECT_IGNORE_BOOLS |
5291 COLLECT_IGNORE_BLANKS |
5292 COLLECT_ORDER_IRRELEVANT,
5293 &n, &result);
5294
5295 if (result)
5296 goto out;
5297
5298 result = value_new_array (1, 3);
5299 value_array_set (result, 0, 2,
5300 value_new_int (n));
5301
5302 if ((n < 8) || gnm_range_adtest (xs, n, &p, &statistics)) {
5303 value_array_set (result, 0, 0,
5304 value_new_error_VALUE (ei->pos));
5305 value_array_set (result, 0, 1,
5306 value_new_error_VALUE (ei->pos));
5307 } else {
5308 value_array_set (result, 0, 0,
5309 value_new_float (p));
5310 value_array_set (result, 0, 1,
5311 value_new_float (statistics));
5312 }
5313
5314 out:
5315 g_free (xs);
5316
5317 return result;
5318 }
5319
5320 /***************************************************************************/
5321
5322 GnmFuncDescriptor const stat_functions[] = {
5323 { "adtest", "A",
5324 help_adtest, gnumeric_adtest, NULL,
5325 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC,
5326 GNM_FUNC_TEST_STATUS_NO_TESTSUITE},
5327 { "sftest", "A",
5328 help_sftest, gnumeric_sftest, NULL,
5329 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC,
5330 GNM_FUNC_TEST_STATUS_NO_TESTSUITE},
5331 { "cvmtest", "A",
5332 help_cvmtest, gnumeric_cvmtest, NULL,
5333 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC,
5334 GNM_FUNC_TEST_STATUS_NO_TESTSUITE},
5335 { "lkstest", "A",
5336 help_lkstest, gnumeric_lkstest, NULL,
5337 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC,
5338 GNM_FUNC_TEST_STATUS_NO_TESTSUITE},
5339 { "avedev", NULL,
5340 help_avedev, NULL, gnumeric_avedev,
5341 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_FIRST,
5342 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5343 { "average", NULL,
5344 help_average, NULL, gnumeric_average,
5345 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_FIRST,
5346 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5347 { "averagea", NULL,
5348 help_averagea, NULL, gnumeric_averagea,
5349 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_FIRST,
5350 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5351
5352 { "bernoulli", "ff", help_bernoulli,
5353 gnumeric_bernoulli, NULL,
5354 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5355
5356 { "betadist", "fff|ff",
5357 help_betadist, gnumeric_betadist, NULL,
5358 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5359 { "beta.dist", "fffb|ff",
5360 help_beta_dist, gnumeric_beta_dist, NULL,
5361 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5362 { "betainv", "fff|ff",
5363 help_betainv, gnumeric_betainv, NULL,
5364 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5365 { "binomdist", "fffb",
5366 help_binomdist, gnumeric_binomdist, NULL,
5367 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5368 { "binom.dist.range", "fff|f",
5369 help_binom_dist_range, gnumeric_binom_dist_range, NULL,
5370 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
5371
5372 { "cauchy", "ffb", help_cauchy,
5373 gnumeric_cauchy, NULL,
5374 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5375
5376 { "chidist", "ff",
5377 help_chidist, gnumeric_chidist, NULL,
5378 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5379 { "chiinv", "ff",
5380 help_chiinv, gnumeric_chiinv, NULL,
5381 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5382 { "chitest", "AA",
5383 help_chitest, gnumeric_chitest, NULL,
5384 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5385 { "confidence", "fff",
5386 help_confidence, gnumeric_confidence, NULL,
5387 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5388 { "confidence.t", "fff",
5389 help_confidence_t, gnumeric_confidence_t, NULL,
5390 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE},
5391 { "correl", "AA",
5392 help_correl, gnumeric_correl, NULL,
5393 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5394 { "count", NULL,
5395 help_count, NULL, gnumeric_count,
5396 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
5397 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5398 { "counta", NULL,
5399 help_counta, NULL, gnumeric_counta,
5400 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
5401 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5402 { "covar", "AA",
5403 help_covar, gnumeric_covar, NULL,
5404 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5405 { "covariance.s", "AA",
5406 help_covariance_s, gnumeric_covariance_s, NULL,
5407 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5408 { "critbinom", "fff",
5409 help_critbinom, gnumeric_critbinom, NULL,
5410 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5411 { "devsq", NULL,
5412 help_devsq, NULL, gnumeric_devsq,
5413 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5414 { "expondist", "ffb",
5415 help_expondist, gnumeric_expondist, NULL,
5416 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5417 { "fdist", "fff",
5418 help_fdist, gnumeric_fdist, NULL,
5419 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5420 { "finv", "fff",
5421 help_finv, gnumeric_finv, NULL,
5422 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5423 { "fisher", "f",
5424 help_fisher, gnumeric_fisher, NULL,
5425 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5426 { "fisherinv", "f",
5427 help_fisherinv, gnumeric_fisherinv, NULL,
5428 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5429 { "forecast", "frr",
5430 help_forecast, gnumeric_forecast, NULL,
5431 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5432 { "frequency", "AA",
5433 help_frequency, gnumeric_frequency, NULL,
5434 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5435 { "ftest", "rr",
5436 help_ftest, gnumeric_ftest, NULL,
5437 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5438 { "gammadist", "fffb",
5439 help_gammadist, gnumeric_gammadist, NULL,
5440 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5441 { "gammainv", "fff",
5442 help_gammainv, gnumeric_gammainv, NULL,
5443 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5444 { "geomean", NULL,
5445 help_geomean, NULL, gnumeric_geomean,
5446 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_FIRST,
5447 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5448 { "growth", "A|AAb",
5449 help_growth, gnumeric_growth, NULL,
5450 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5451 { "harmean", NULL,
5452 help_harmean, NULL, gnumeric_harmean,
5453 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_FIRST,
5454 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5455 { "hypgeomdist", "ffff|b",
5456 help_hypgeomdist, gnumeric_hypgeomdist, NULL,
5457 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5458 { "intercept", "AA",
5459 help_intercept, gnumeric_intercept, NULL,
5460 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5461 { "kurt", NULL,
5462 help_kurt, NULL, gnumeric_kurt,
5463 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5464 { "large", "Af",
5465 help_large, gnumeric_large, NULL,
5466 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_FIRST,
5467 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5468 { "leverage", "A", help_leverage,
5469 gnumeric_leverage, NULL,
5470 GNM_FUNC_RETURNS_NON_SCALAR, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
5471 { "linest", "A|Abb",
5472 help_linest, gnumeric_linest, NULL,
5473 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5474 { "logest", "A|Abb",
5475 help_logest, gnumeric_logest, NULL,
5476 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5477 { "logfit", "rr",
5478 help_logfit, gnumeric_logfit, NULL,
5479 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
5480 { "loginv", "fff",
5481 help_loginv, gnumeric_loginv, NULL,
5482 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5483 { "lognormdist", "fff",
5484 help_lognormdist, gnumeric_lognormdist, NULL,
5485 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5486 { "logreg", "A|Abb",
5487 help_logreg, gnumeric_logreg, NULL,
5488 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
5489 { "max", NULL,
5490 help_max, NULL, gnumeric_max,
5491 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_FIRST,
5492 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5493 { "maxa", NULL,
5494 help_maxa, NULL, gnumeric_maxa,
5495 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_FIRST,
5496 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5497 { "median", NULL,
5498 help_median, NULL, gnumeric_median,
5499 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_FIRST,
5500 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5501 { "min", NULL,
5502 help_min, NULL, gnumeric_min,
5503 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_FIRST,
5504 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5505 { "mina", NULL,
5506 help_mina, NULL, gnumeric_mina,
5507 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_FIRST,
5508 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5509 { "mode", NULL,
5510 help_mode, NULL, gnumeric_mode,
5511 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_FIRST,
5512 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5513 { "mode.mult", NULL,
5514 help_mode_mult, NULL, gnumeric_mode_mult,
5515 GNM_FUNC_SIMPLE,
5516 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE},
5517 { "negbinomdist", "fff",
5518 help_negbinomdist, gnumeric_negbinomdist, NULL,
5519 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5520 { "normdist", "fffb",
5521 help_normdist, gnumeric_normdist, NULL,
5522 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5523 { "snorm.dist.range", "ff",
5524 help_snorm_dist_range, gnumeric_snorm_dist_range, NULL,
5525 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE,
5526 },
5527 { "norminv", "fff",
5528 help_norminv, gnumeric_norminv, NULL,
5529 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5530 { "normsdist", "f",
5531 help_normsdist, gnumeric_normsdist, NULL,
5532 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5533 { "normsinv", "f",
5534 help_normsinv, gnumeric_normsinv, NULL,
5535 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5536 { "owent", "ff",
5537 help_owent, gnumeric_owent, NULL,
5538 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_EXHAUSTIVE },
5539 { "pearson", "AA",
5540 help_pearson, gnumeric_pearson, NULL,
5541 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5542 { "percentile", "Af",
5543 help_percentile, gnumeric_percentile, NULL,
5544 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5545 { "percentile.exc", "Af",
5546 help_percentile_exc, gnumeric_percentile_exc, NULL,
5547 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE},
5548 { "percentrank", "Af|f",
5549 help_percentrank, gnumeric_percentrank, NULL,
5550 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5551 { "percentrank.exc", "Af|f",
5552 help_percentrank_exc, gnumeric_percentrank_exc, NULL,
5553 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE},
5554 { "permut", "ff",
5555 help_permut, gnumeric_permut, NULL,
5556 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
5557 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5558 { "poisson", "ffb",
5559 help_poisson, gnumeric_poisson, NULL,
5560 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5561 { "prob", "AAf|f",
5562 help_prob, gnumeric_prob, NULL,
5563 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5564 { "quartile", "Af",
5565 help_quartile, gnumeric_quartile, NULL,
5566 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5567 { "quartile.exc", "Af",
5568 help_quartile_exc, gnumeric_quartile_exc, NULL,
5569 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE},
5570 { "rank", "fr|b",
5571 help_rank, gnumeric_rank, NULL,
5572 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5573 { "rank.avg", "fr|b",
5574 help_rank_avg, gnumeric_rank_avg, NULL,
5575 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE},
5576 { "slope", "AA",
5577 help_slope, gnumeric_slope, NULL,
5578 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5579 { "small", "Af",
5580 help_small, gnumeric_small, NULL,
5581 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_FIRST,
5582 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5583 { "standardize", "fff",
5584 help_standardize, gnumeric_standardize, NULL,
5585 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5586 { "ssmedian", "A|f",
5587 help_ssmedian, gnumeric_ssmedian, NULL,
5588 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
5589 { "stdev", NULL,
5590 help_stdev, NULL, gnumeric_stdev,
5591 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_FIRST,
5592 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5593 { "stdeva", NULL,
5594 help_stdeva, NULL, gnumeric_stdeva,
5595 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_FIRST,
5596 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5597 { "stdevp", NULL,
5598 help_stdevp, NULL, gnumeric_stdevp,
5599 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_FIRST,
5600 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5601 { "stdevpa", NULL,
5602 help_stdevpa, NULL, gnumeric_stdevpa,
5603 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_FIRST,
5604 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5605 { "steyx", "AA",
5606 help_steyx, gnumeric_steyx, NULL,
5607 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5608 { "rsq", "AA",
5609 help_rsq, gnumeric_rsq, NULL,
5610 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5611 { "skew", NULL,
5612 help_skew, NULL, gnumeric_skew,
5613 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5614 { "tdist", "fff",
5615 help_tdist, gnumeric_tdist, NULL,
5616 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5617 { "tinv", "ff",
5618 help_tinv, gnumeric_tinv, NULL,
5619 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5620 { "trend", "A|AAb",
5621 help_trend, gnumeric_trend, NULL,
5622 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5623 { "trimmean", "rf",
5624 help_trimmean, gnumeric_trimmean, NULL,
5625 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_FIRST,
5626 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5627 { "ttest", "rrff",
5628 help_ttest, gnumeric_ttest, NULL,
5629 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5630 { "var", NULL,
5631 help_var, NULL, gnumeric_var,
5632 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5633 { "vara", NULL,
5634 help_vara, NULL, gnumeric_vara,
5635 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5636 { "varp", NULL,
5637 help_varp, NULL, gnumeric_varp,
5638 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5639 { "varpa", NULL,
5640 help_varpa, NULL, gnumeric_varpa,
5641 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5642 { "weibull", "fffb",
5643 help_weibull, gnumeric_weibull, NULL,
5644 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5645 { "ztest", "Af|f",
5646 help_ztest, gnumeric_ztest, NULL,
5647 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5648
5649 { "exppowdist", "fff", help_exppowdist,
5650 gnumeric_exppowdist, NULL,
5651 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
5652 { "geomdist", "ffb", help_geomdist,
5653 gnumeric_geomdist, NULL,
5654 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
5655 { "kurtp", NULL,
5656 help_kurtp, NULL, gnumeric_kurtp,
5657 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
5658 { "landau", "f", help_landau,
5659 gnumeric_landau, NULL,
5660 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
5661 { "laplace", "ff", help_laplace,
5662 gnumeric_laplace, NULL,
5663 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
5664 { "logistic", "ff", help_logistic,
5665 gnumeric_logistic, NULL,
5666 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
5667 { "pareto", "fff", help_pareto,
5668 gnumeric_pareto, NULL,
5669 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
5670 { "rayleigh", "ff", help_rayleigh,
5671 gnumeric_rayleigh, NULL,
5672 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
5673 { "rayleightail", "fff", help_rayleightail,
5674 gnumeric_rayleightail, NULL,
5675 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
5676 { "skewp", NULL,
5677 help_skewp, NULL, gnumeric_skewp,
5678 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
5679 { "subtotal", NULL,
5680 help_subtotal, NULL, gnumeric_subtotal,
5681 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
5682 { "cronbach", NULL,
5683 help_cronbach, NULL, gnumeric_cronbach,
5684 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
5685 { "permutationa", "ff", help_permutationa,
5686 gnumeric_permutationa, NULL,
5687 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_UNITLESS,
5688 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
5689
5690 {NULL}
5691 };
gnumeric mirror