Re: Choosing the closest value from database

[Marc Dunord <marcdunord gmail com>]

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, Заболотный Андрей <zapparello 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