put the values in a 1st.column; add 2nd.column with "= cell.1stcolumn -valuetogetcloseto " get min of 2nd.column add 3rd.column with "= if cell in 2nd.column = min 2ndcolumn, then write value.cell.2nd.column, else ""
get "count" of 3rd column i'd bet you can do everything in one column though On Fri, Sep 25, 2015 at 12:24 PM, Заболотный Андрей <zappare...@ya.ru> wrote: > Hello! > > I'm trying to solve a what seems a simple problem here, but can't see how. > > I have a formula that computes the value of a resistance. Obviously, it's a > fractional number. > I need to choose the closest value from something like the E24 series of > numbers (https://en.wikipedia.org/wiki/E24_series). > I have put the series on a separate page and... now I'm stuck. > I found the functions MATCH(), and LOOKUP(), but they won't find the closest > value. > Besides, MATCH(x,y,-1) doesn't seem to work altogether, it always returns the > number of elements in the match array. > The closest thing I came with is the following formula: > > match(min((A2:A98-H8)^2);(A2:A98-H8)^2;0) > > (must be entered as array formula to work). > Now I have the row index inside my array, but how to I get the respective > element of the array? > Maybe there's an easier way? > > -- > С уважением, > Андрей > _______________________________________________ > gnumeric-list mailing list > gnumeric-list@gnome.org > https://mail.gnome.org/mailman/listinfo/gnumeric-list _______________________________________________ gnumeric-list mailing list gnumeric-list@gnome.org https://mail.gnome.org/mailman/listinfo/gnumeric-list
