Discrepancy between function Argument and cell display

[vinh nhut nguyen]

When I type =IF(OR(K24 = H16:H22),"","<-- MUST BE A, B, C, D, E ,F or G") in cell L24 of Music Notation tab, the function Arguments window return correct formula result but the cell gives #VALUE.

Calculation step evaluates H16:H22 as an #VALUE.

OR function should be allowed to check a cell value equal to values of a table?

[#2]

with "K24 = H16:H22" are you trying to see if H16:H22 contains K24 ?

If yes, then use match function instead.

Regards,

DILIPandey

[#3 · Accepted solution]

Yes, that could be

=IF(ISERROR(MATCH(K24,H16:H22,0)),
   "<-- MUST BE A, B, C, D, E ,F or G",
   "")

or

=IF(
   (K24>=H16)*(K24<=H22),
   "",
   "<-- MUST BE A, B, C, D, E ,F or G")

[#4]

Thank you very much.

Your proposed solutions works very well. I understand well the first solution but cannot understand how your second solution works. Please let me know where I can read to understand the term (K24>=H16)*(K24<=H22).

[#5]

Okay, the logic is

1) In cells from H16 to H22 we have the letters in alphabetical order

2) we'd like to check if the letter in K24 is one of above

3) if so the letter in K24 shall be more or equal than one in H16 and less or equal than one in H22. Other words, both (K24>=H16) and (K24<=H22) are to be TRUE. Otherwise the letter is out of our range.

4) In Excel logical TRUE is equivalent of numeric 1; and logical FALSE is 0

5) Thus if both of our conditions are TRUE (aka 1), their multiplication gives also 1. If at least one of them is FALSE result will be also FALSE, or zero (e.g. 1*0=0)

6) Thus (K24>=H16)*(K24<=H22) returns 1 if our letter is within the range and 0 if not

7) Above 1 or 0 could be interpreted as logical TRUE or FALSE

Couple of more notes

a) Stay on the cell with your formula, in Ribbon at Formulas tab press Evaluate Formula and after that click Evaluate in opened window till calculation of formula is not finished. You'll see how the formula works step by step

b) if you'd like to convert logical value into numeric one literally use double dash before it. For example, if =(K24>=H16) returns TRUE when =--(K24>=H16) returns 1

[#6]

Wow. This is very an interesting logic. So True is 1 and FALSE is 0 then we can multiply or add logical results together. You are very smart.

Thank you so much.

[#7]

You are welcome. That's more or less standard pattern

[#8]

@vinh nhut nguyen,

I have actually the same problem but with the MATCH function. The function argument window shows the correct result (2), but in the cell I get #VALUE...

[#9]

@Boudewijn_Knooren 

You need to array-enter the formula:  press ctrl+shift+Enter instead of just Enter.

The Function Evaluator (and Evaluate Formula) always behave as if the formula is array-entered.  Consequently, they might work when a normally-entered does not.

In the future, please post a new inquiry instead of piggybacking an old posting, especially one that resolved 5 years ago.