Forum Discussion
I'm having trouble creating a MEDIAN(IF(AND function in excel.
FYI the reasoning to use (expression)*(expression) instead of AND(expression, expression) is because the AND will be applied to the entire combined range of ALL values while the multiplication will be applied through the index array.
- That was it! The format was set to text instead of numbers. Thank you for the explanation as well. Are you able to explain the difference between an array and a range? It would be nice to know for some potential issues I may run into in the future.
technically a Range is specifically a reference to a group of cells on a worksheet. I believe a Range could always be used as an input for a function that takes a range or an array. An array may not have a direct link to specific cells on a worksheet and hence cannot be used in a function that requires a range input. For example COUNTIF requires a RANGE and not an ARRAY. Here is an example where I take a RANGE and do some operations to it to make it an ARRAY and how it throws an error while its 'sister' function COUNTA works fine
NOTE: excel does try to be 'smart' about it and 'find' the range when it can. For example my 1st attempt at converting RANGE to ARRAY was using TAKE but excel out smarted me and this version using COUNTIF still worked:
=LET(a,A:A,b,TAKE(a,40),COUNTIF(b,"<>"))