Mar 25 2017 07:34 AM
Mar 25 2017 07:34 AM
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?
Mar 25 2017 10:37 AM
with "K24 = H16:H22" are you trying to see if H16:H22 contains K24 ?
If yes, then use match function instead.
Mar 25 2017 11:26 AMSolution
Yes, that could be
=IF(ISERROR(MATCH(K24,H16:H22,0)), "<-- MUST BE A, B, C, D, E ,F or G", "")
=IF( (K24>=H16)*(K24<=H22), "", "<-- MUST BE A, B, C, D, E ,F or G")
Mar 25 2017 02:02 PM
Mar 25 2017 02:44 PM
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
Mar 25 2017 03:42 PM
Aug 31 2022 01:53 PM
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...
Aug 31 2022 04:55 PM
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.