[Solved] INDEX MATCH Puzzle

[crusader]

i have been "playing" with an INDEX MATCH combo formula for quite a few days now (I am not new to the INDEX MATCH combo).

I am requesting help in understanding wthy the formula in G5 returns an error message. The only difference between the formulas in G3 and G5 is the second parameter in the MATCH formula. Ideally, I would like to have the second MATCH parameter extend to E53; however, I get an error when I enter a parameter that extends beyond E8. Strangely, I have been able to increase the range in the second MATCH parameter successfully when I have more entries in column C - but that should not be the case!

I have made sure the range in column E is sorted in decending order and have set the third MATCH parameter appropriately to -1.

What am I missing?

Note: the attachment is a LO product on Mint; given my increasingly serious allergy to Windows, I have not tested this puzzling formula on Redmond's money minting machine.

[Charlie Young]

I haven't fully comprehended the issues yet, but the immediate problem seems to be that E8 is text and the E3:E7 are numbers. The #N/A goes away if i change G5 to

Code: Select all

=INDEX(E3:E53;MATCH(B2+1;E3:E7;-1))-B2

[RusselB]

It appears that what you are "missing" is actually a problem due to your usage of "" in the formulas used in column E when the corresponding row in column C matches ""
The easiest way to resolve this is to change the "" (that goes into E) to 0. If you don't want the 0's to display, then you can change the Zero Value display option.
In Open Office Calc this is located via Tools -> Options -> Open Office Calc -> View
I suspect it'll be similar in your version of LO

[Charlie Young]

There is another problem, probably introduced inadvertently while preparing the spreadsheet for posting. The conditional formatting in column E asks if the cell value is equal to $#REF!.$C$3, which I guess would be caused by deleting a sheet.

[crusader]

Thank you Charlie and RusselB!

the immediate problem seems to be that E8 is text and the E3:E7 are numbers. The #N/A goes away if i change G5 to

Code: Select all

=INDEX(E3:E53;MATCH(B2+1;E3:E7;-1))-B2

True: the problem begins after E8. The number and text issue is based on the "" in the column E formulas (as pointed out by RusselB).

There is another problem, probably introduced inadvertently while preparing the spreadsheet for posting. The conditional formatting in column E asks if the cell value is equal to $#REF!.$C$3, which I guess would be caused by deleting a sheet.

I did remove material not directly relevant to the issue at hand; however, i am curious where did you "see" $#REF!.$C$3. I don't see it anywhere.

It appears that what you are "missing" is actually a problem due to your usage of "" in the formulas used in column E when the corresponding row in column C matches ""

Bingo! Clearly, the formula does not like "". On the other hand, I don't like zeros in this particular instance, but want to retain the broader option of viewing zeros. Turning off that option is an option - but it is generalized to Calc and not specific to this particular spreadsheet (that I am aware of).

Before I sound too picky, I will mark this thread as solved (largely because the issue has been resolved) - although I am not thrilled about the prospect of seeing non-relevant zeros in this case, nor do I want to kill all zeros everywhere else.

Hallelujah!

[acknak]

... I don't like zeros in this particular instance, but want to retain the broader option of viewing zeros. Turning off that option is an option - but it is generalized to Calc and not specific to this particular spreadsheet (that I am aware of). ...

Agreed. When I need to avoid displaying zeroes for certain cells, I use a custom number format that shows "" when the result is zero.

Even this should be considered carefully, as displaying something completely different from what's in the cell almost always causes confusion at some point.

[crusader]

When I need to avoid displaying zeroes for certain cells, I use a custom number format that shows "" when the result is zero.
Even this should be considered carefully, as displaying something completely different from what's in the cell almost always causes confusion at some point.

Brilliant idea, Acknak! And the caution is worth its weight in gold: adopting this approach has to be well thought out.

[Charlie Young]

There is another problem, probably introduced inadvertently while preparing the spreadsheet for posting. The conditional formatting in column E asks if the cell value is equal to $#REF!.$C$3, which I guess would be caused by deleting a sheet.

I did remove material not directly relevant to the issue at hand; however, i am curious where did you "see" $#REF!.$C$3. I don't see it anywhere.

I see it if I select any cell in E3:E53 of Puzzle1 and do Format > Conditional Formatting. I first noticed it when I entered an explicit 0 in Cell E8 and found the 0 to have an odd color. I figured out finally that this was caused by the conditional formatting. Also the applied Style Name is "Untitled1".

[crusader]

I see it if I select any cell in E3:E53 of Puzzle1 and do Format > Conditional Formatting. I first noticed it when I entered an explicit 0 in Cell E8 and found the 0 to have an odd color. I figured out finally that this was caused by the conditional formatting. Also the applied Style Name is "Untitled1".

Thank you for the insight, Charlie. Never thought of cracking this nut from that angle.

[Lupp]

As far as I can see the cause for the NA() result was not yet clearly named.
@crusader told us

I have made sure the range in column E is sorted in decending order and have set the third MATCH parameter appropriately to -1.

But this is only true for the numbers. Comparing any text, even the empty string, with any number Calc will judge the text GT (greater than) the number. Thus the third parameter of MATCH() ist telling a lie in this case. This will, however only cause the observed error if during the generation of the series of bisections, MATCH will apply to an ordered range, one of the cells containing text gets actually compared with a number. Errors during calculation of formatting conditions (CF) will never afflict the result on the level of the evaluation of the cell's formula.