This function is producing a "0" result. Any ideas out there?
=MEDIAN(IF(AND('Ex.5.2 Complete Data'!$N$2:$N$4500='Ex.5.1 Detailed Summary'!$B$47,'Ex.5.2 Complete Data'!$CS$2:$CS$4500>='Ex.5.1 Detailed Summary'!$F$44,'Ex.5.2 Complete Data'!$CS$2:$CS$4500<='Ex.5.1 Detailed Summary'!$H$44,'Ex.5.2 Complete Data'!$CV$2:$CV$4501<'Ex.5.1 Detailed Summary'!$G$43),'Ex.5.2 Complete Data'!$CT$2:$CT$4501,FALSE))
My concern is, how do I do a MedianIFs function if not like this?
Thanks!
You can't use AND here, try using * to simulate AND like this
=MEDIAN(IF(('Ex.5.2 Complete Data'!$N$2:$N$4500='Ex.5.1 Detailed Summary'!$B$47)*('Ex.5.2 Complete Data'!$CS$2:$CS$4500>='Ex.5.1 Detailed Summary'!$F$44)*('Ex.5.2 Complete Data'!$CS$2:$CS$4500<='Ex.5.1 Detailed Summary'!$H$44)*('Ex.5.2 Complete Data'!$CV$2:$CV$4500<'Ex.5.1 Detailed Summary'!$G$43),'Ex.5.2 Complete Data'!$CT$2:$CT$4500))
....and this is an "array formula" so you need to confirm with CTRL+SHIFT+ENTER. If that's done correctly then curly braces like { and } will appear automatically around the formula
Audere est facere
No quotes and & around the cell references? I'm getting #REF now, which would indicate an issue with my database columns.
No, that syntax only applies to the SUMIF/COUNTIF/AVERAGEIF family of functions.Originally Posted by millonario101
Formula seems OK to me, assuming the referenced worksheets have names as shown
I now get a dialog box asking me to open up the file, which I do, then get the #NUM! error. Should I just give up an calculate it?
Have you used CTRL+SHIFT+ENTER?
If so then #NUM! implies that there are no rows which meet the criteria (or there aren't any numbers in column CT when the criteria is met)
Fixed Column CT - thank you so much - perfect.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks