I am having a problem in using INDIRECT in a LOOKUP function in Calc 220.127.116.11 in Linux (Kubuntu 13.10).
In my worksheet, I have column of values (0-255) in column A, in the cell range A3:A258. Column B has a corresponding set of values (which are image grey scale pixel counts in this case).
From this data, I want to do some anaylsis based on maximum and minimum values in column B. However the nature of the data is that can be more than one maximum/minimum in the data in column B so I want to set a range of cells to find local maximum/minimum valies, rather than simply looking at the entire range of cells B3:B258. For example, if I know that there is a local maximum around cell B200, then I want to set the search range to B180:B220 or similar.
Once the local maximum/minimum has been found (that part of the process works just fine), and is placed into cell B268, I want to use that value in my LOOKUP to tell me what the corresponding grey scale value in column A is.
The formula I am trying to use looks like this:
cell B268 holds the local maximum/minimum value,
cell B266 holds the lower bound of the row in the range of cells in column B where the max/min value is,
cell B267 holds the upper bound of the row in the range of cells in column B where the max/min value is
So I want the above function to effectively translate for example into:
The absolute cell references are necessary for column A as there are multiple data columns which will change as I copy the formula across into the data columns but they will all be matched against column A.
I hope that all makes sense.
When I try to use the above formula, I get a #N/A error and don’t know why. What am I doing wrong? Any advice would be helpful.
Thanks in advance and sorry if this question is a bit long.