search function () original pose problems for many users of Excel. Some had trouble understanding it was in the rightmost column, or the lowest line of the matrix that we took the result. Others complained that - with its algorithm - we had to find the right result (vertical array) or bottom (horizontal array): they wanted to find a result to the left or above ...
So a dozen years later, Microsoft added a second syntax for this function: = search (value, vector research; result vector) .
In the example below, compared to that presented four days ago, we switched the blocks A2: A10 and B2: B10, with rates before the weights.
The formula in cell B13 shows the direct application of the new syntax to the two vectors of the vertical array. The new algorithm is as follows: we identify in the search vector position of the last value less than or equal to the value sought (see route in blue), then there is the position of this value, then the vector result, we identify (See red line) the value of having this position.
Nothing prevents then, if one has the spirit a bit twisted - would it be me? - To as B13, which we conduct research in the vertical vector B3: B10 and find the result in the horizontal vector A17: H17!
This works perfectly well, but it's not really recommended for readability of your models:)
0 comments:
Post a Comment