Forum Discussion

mlang760's avatar
mlang760
Copper Contributor
Aug 08, 2023

I'm having trouble creating a MEDIAN(IF(AND function in excel.

I'm trying to create a function that returns a median of an array when two criterion are met. The function works if I do a MEDIAN(IF( statement with one criterion. However, as soon as I change it to ...
excel
Formulas and Functions
mlang760's avatar
mlang760
Copper Contributor
Aug 08, 2023
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.
mtarler's avatar
mtarler
Silver Contributor
Aug 08, 2023

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,"<>"))

 

 

Resources