Evening all,
I've created a MEDIAN IF array formula, but when I try to write the non-array equivalent I'm struggling.(I'm assuming it's an AGGREGATE formula?)
The array formula calculates the median by rate type for all cells that have been populated (they may be blank). The formula needs to extend over an expanded range to accommodate any additional rates added.
Attached is a sample data set along with an array solution.
Thanks in advance,
Snook
how about using FILTER() function..
in H2 and drag down:
Formula:
Please Login or Register to view this content.
<----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.
Hi dosydos,
Unfortunately the FILTER function isn't an option for me as I'm using Excel 2019 (despite the Microsoft site claiming that it's in Excel 2019). I only appear to have FILTERXML. According to my account info I'm on Microsoft Office Standard 2019.
Snook
The most logical attempt I can come up with is:
=AGGREGATE(12,6,IF(($B$2:$B$50=G2)*($C$2:$C$50<>0),$C$2:$C$50,1/0))
This returns £63.50 (I have no idea how?
Any thoughts where I'm going wrong?
The median component of AGGREGATE is seriously weird. I have never got it to behave itself! Here's a workaround.
Named Range (data):
=IFERROR(Sheet1!$C$2:$C$35/((Sheet1!$C$2:$C$35<>"")*(Sheet1!$B$2:$B$35=Sheet1!$G2)),"")
and
=MEDIAN(Data)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Well... on the plus side, your effort did at least return a number. My attempts have ALWAYS given an error.
TS... when I try your AGGREGATE.... I get the #VALUE error.
THIS works (no named range):
=AGGREGATE(16,6,$C$2:$C$35/(($B$2:$B$35=G2)*($C$2:$C$35<>"")),0.5)
The aggregate function only works with an array when the 1st argument is 14 or greater, which is why this wont work =AGGREGATE(12,6,IF(($B$2:$B$50=G2)*($C$2:$C$50<>0),$C$2:$C$50,1/0))
Glenn with a late bid for 'most mind-blowing solution of the year'!I have no idea how you pulled that out the onion bag?!?!
@Fluff13 - Thanks for clarifying, that makes sense. I've read the Microsoft AGGREGATE page and it insinuates that only certain functions accept arrays but it is hardly very prescriptive (or helpful). It would probably make sense for the tool tip to change based on the function number you input. Functions 1-13 still refer to it as an array.
Thanks all, that was mashing my head.
Snook
There >>is<< a little disagreement about it, but most folk agree that the 50th percentile is IDENTICAL to the median.
I had simply forgotten about AGGREGATE's function 16. It happens all-too-often these days.
I'm certainly not going to argue!
For info - Either percentile or quartile functions (16-19) can be used to calculate the median. You'd just change the [k] argument to 2 if you were using quartile.
Last edited by The_Snook; 12-03-2022 at 01:33 PM.
Glad to help & thanks for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks