GNOME Bugzilla – Bug 571744
function small has changed behaviour between 1.6.3 and 1.8.3
Last modified: 2009-02-17 00:47:24 UTC
Please describe the problem: In gnumeric 1.6.3 you could evaluate =small(A1, B2, C3, 2) and this would give you the second smallest value of the listed cells. This doesn't seem to work in gnumeric 1.8.3, though =small(A1:A3, 2) does work. However, I have many old spreadsheets using the old behaviour on non-contiguous cells, so this is somewhat inconvenient. Note that the documentation describes the first behaviour, but gives an example of the second... Yours Joseph Steps to reproduce: put values in cells A1, B2, C3 and try =small(A1, B2, C3, 2) Actual results: cell evaluates to #N/A Expected results: obtain the second smallest value of the listed cells Does this happen every time? yes Other information: I also filed this as a debian bug: http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=515161
This is correct and, in turns out, deliberate, see. http://bugzilla.gnome.org/show_bug.cgi?id=343176#c4 The work-around is to use a range. If your original data was not in a range as-is, create a new range that simply copies the data. The documentation appears to be fixed in 1.9.x: static GnmFuncHelp const help_small[] = { { GNM_FUNC_HELP_NAME, F_("SMALL: @{k}-th smallest value in a data set.") }, { GNM_FUNC_HELP_ARG, F_("data:data set")}, { GNM_FUNC_HELP_ARG, F_("k:which value to find")}, { GNM_FUNC_HELP_NOTE, F_("If data set is empty this function returns a # NUM! error.") }, { GNM_FUNC_HELP_NOTE, F_("If @{k} <= 0 or @{k} is greater than the numbe r of data items given " "this function returns a #NUM! error.")}, { GNM_FUNC_HELP_DESCRIPTION, F_("This function is Excel compatible.") }, { 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.") }, { GNM_FUNC_HELP_EXAMPLES, F_("Then SMALL(A1:A5,2) equals 17.3." "SMALL(A1:A5,4) equals 25.9.") }, { GNM_FUNC_HELP_SEEALSO, "PERCENTILE,PERCENTRANK,QUARTILE,LARGE"}, { GNM_FUNC_HELP_END } }; Closing as NOTABUG because the change was deliberate and necessary to fix implicit iteration.