Hi,
I am doing the formula below and it seems to be working for 1 condition but not for the other 3. Everything is the same in the formula excel the rows that are changing when dragging the formula. Please help.
=IFERROR(MEDIAN(IF($A$7:$A$85=$A90,IF($B$7:$B$85=$B90,D$7:D$85))),0)
Thanks,
JK
Last edited by JKBR; 12-09-2020 at 07:29 PM.
Welcome to the forum.
There are instructions at the top of the page explaining how to attach your sample workbook.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
What do you mean by not working? Returns an error value (unlikely given IFERROR)? Returns 0 when it shouldn't? Returns the wrong value?
Are you entering this formula as an array formula, holding down [Ctrl] and [Shift] keys before pressing [Enter]?
Thank you for the quick response.
I am doing contorl+Shift+Enter to calculate on row 86 thru 89. It's working on row 86 but not on the other 3 rows. I attached the report to my original post.
Thank you.
Last edited by JKBR; 12-03-2020 at 02:06 AM.
There is nothing on those rows, so nothing to troubleshoot ...
Again, please define "not working".
The formula has been entered as an array formula ONLY on the first row of the table at the bottom. You need to do that before dragging down, however it won't change the results.
Last edited by AliGW; 12-03-2020 at 02:08 AM.
Sorry, I updated the row numbers. I am using the formulas on rows 86 thru 89 and column D thru Y.
Is there a reason why you have ignored the request to define "not working" twice now? Until you answer this, we can't help. Which results do you think are incorrect and why, and what do you think they should be?
The formula has been entered as an array formula ONLY on the first row of the table at the bottom. You need to do that before dragging down, however it won't change the results.
Your row 86 formulas are array formulas, but your formulas in rows 87 to 89 aren't.
Hi,
Even after I change them to array formulas, it returns no values.
I am calculating median by month based on conditions from A86 thru B89. After using the array formulas, I am only see the median being calculated for row 86 and not for the other 3 rows. There should be values in majority of the cells for row 87 thru 89 as there is data to calculate the median.
Dunno, it seems to work for me.
ADDED: note the conditional counts I added below the array formulas. When there are 0s, the corresponding array formulas should return - to indicate no median because no numbers from which to calculate a median.
Last edited by hrlngrv; 12-03-2020 at 02:43 AM. Reason: addendum
Thanks for doing the calc. When I do the same calculation on my computer, I return to same result as before. I can't see what you doing differently in your calculations.
Last edited by JKBR; 12-09-2020 at 07:30 PM.
It looks to me as though MEDIAN IF behaves differently to MEDIAN. Median is happy to ignore blank cells. However, Median-IF converts the blanks to zeros. So.... when you have 5 potential matching values: 0, 0, 0, 1, 3 the median is... zero.
So, add another nested IF to select values >0:
=IFERROR(MEDIAN(IF(($A$5:$A$80=$A86)*($B$5:$B$80=$B86)*(D$5:D$80>0),D$5:D$80,"")),0)
You will probably have to array-enter this formula (Excel 2016).
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
D86=IFERROR(AGGREGATE(16,6,D$5:D$80/($A$5:$A$80=$A86)/($B$5:$B$80=$B86)/(D$5:D$80>0),0.5),0)
copy across and down
That didn't occur to me, Caracalla. Nice one.
JKBR: use Caracalla's solution: no array entry required.
Thanks Glenn
Median with Aggregate Tutorial
https://www.youtube.com/watch?v=zPgu...nel=ExcelIsFun
My fault for using LibreOffice Calc to test this.
Interesting that LibreOffice Calc preserves the blank values in D5:D80 in nested IF calls.
This works because you're using PERCENTILE.INC.
Seems odd to me that for D87,
=AGGREGATE(12,6,D$5:D$80/($A$5:$A$80=$A87)/($B$5:$B$80=$B87)/ISNUMBER(D$5:D$80))
produces a #VALUE! error, but enter
=D$5:D$80/($A$5:$A$80=$A87)/($B$5:$B$80=$B87)/ISNUMBER(D$5:D$80)
in D105:D180, and =AGGREGATE(12,6,D$105:D$180) generates the same result as your formula would. For me, that calls into question the quality of the implementation of AGGREGATE's MEDIAN subfunction, which in turn calls into question the overall quality of the implementation of AGGREGATE. From a different perspective, why bother including a MEDIAN subfunction if it's not robust.
Sorry. I used LibreOffice Calc, and simple array formulas worked in it. I hadn't realized that Calc and Excel differ in this respect.
Thank you all for your help.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks