Complicated Array Computations

[Ackbach]

I have some hard-coded values in the B column: $B$2:$B$86. Next to it, I have some computed values in the range $C$2:$C$86. What I want to do is this: for each computed value in the C column, I want to find the element in the B column that is closest to it, and return that. I want something like this (which doesn’t work, but communicates the idea) in the D column: =LOOKUP(MIN(ABS($B$2:$B$86-C2)),ABS($B$2:$B$86-C2),$B$2:$B$86).

The problem is that the ABS function does not appear to take arrays (or a range of values) for arguments. For that matter, I’m not sure LibreOffice is able to subtract a scalar from a vector (here, I just mean that I want to subtract the vector consisting of all entries equal to the value in C2, and of the same dimensions as the B column). I’ve fiddled around with curly braces and array functions, but was not able to get it to work.

Any ideas?

[bencomp]

Could you make the main question more specific to the problem you’re having with the ABS function? “Complicated Array Computations” doesn’t say much about the actual problem. Thanks!

[oweng]

In addition to what @bencomp and @mariosv have mentioned, you are going to have to define what “closest to” means. Matching on this type of basis, requires something like comparisons of the results from the MATCH() function from the search key, and the search key +/- a provided percentage (error margin). The provided Calc functions have no concept of nearest. This has to be defined on a case basis.

[mariosv]

Can you explain a bit with a few data values, and with the expected results?

[bencomp]

Could you convert this “answer” into a comment?

[mariosv]

Hi @bencomp, it’s fine see more people involved, but I don’t think we need censors here.

[bencomp]

Hi @mariosv, I don’t want to censor. I just think what you posted does not really answer the question. And AskLibO says: “If you wanted to comment on the question or answer, just use the commenting tool.” If you disagree, could you add an answer to How can I best suggest that a given answer should be a comment? ? Thanks!

[mariosv]

Hi @bencom, maybe it was better as comment, but it was my choice. As I know there is not way to change an answer in comment, and sorry but I don’t care. On other hand, do you really think in to lose a second of your time in it?.
Please @bencom IMHO the main is get right answers for the questions, and don’t see Ask as kind a place to take in too much care what can be as much little formalities. There is a lot of useful things to do in the project. http://www.libreoffice.org/get-involved/

[erAck]

If you want functions that take a scalar argument to operate on arrays you need to enter the entire formula expression as array formula, i.e. use Ctrl+Shift+Enter instead of just Enter.