Forum Discussion
Discrepancy between function Argument and cell display
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")
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).
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
- 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.You are welcome. That's more or less standard pattern