Hi,
I need help with a formula. I have a big table where i need to calculate the median with multiple conditions. Please see attached file.
Hi,
I need help with a formula. I have a big table where i need to calculate the median with multiple conditions. Please see attached file.
hi there. you need to change the cells in H5 and I5 to show just the Type like in Column C. just "A" and "B". then perhaps this array formula in cell H6:
=MEDIAN(IF(Tabell1[Fruit]=$G6,IF(Tabell1[Type]=H$5,Tabell1[Value])))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
otherwise:
=MEDIAN(IF(Tabell1[Fruit]=$G6,IF(Tabell1[Type]=SUBSTITUTE(H$5,"Type ",""),Tabell1[Value])))
do also consider to input the desired results so that we don't have to second-guess if what we are doing is correct or not. you may look at my signature to upload an eg that is easier to understand.
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Hello Masun,
You can use Averageifs function.
Formula:Please Login or Register to view this content.
Please refer attached excel for more clsrification.
Regards,
Thangavel D
Appreciate the help? CLICK *
you can do that with PowerQuery add-in for Ex2010
see attached file
@sandy:
i tried using the PowerQuery too. and i got the same answers as yours and they don't seem right. Orange for Type A if you filter the Table is:
13
34
14
22
in ascending order would be:
13
14
22
34
Median would be the average of 14 and 22; 18. the answer we get from Power Query is 14. not sure how it derived it. but it seems like for banan type B, and Apple Type B, Power Query took the 2nd number if it has 4 numbers in total instead of averaging it.
ps: googled but forgot to read it before i posted. seems like a bug
https://www.myonlinetraininghub.com/...ng-power-query
Because PQ Median treat blank values as 0 (not null)
see attached file and try with/without zero
because OP didn't show what he want to achieve I set simplest way
Edit: I can calculate median for each product separately then join it to one table with null instead of 0
Last edited by sandy666; 11-17-2017 at 05:41 AM.
Change Cell H5 to "A" and try this formula
Formula:Please Login or Register to view this content.
Should be ok now
PQ/PPT
Last edited by sandy666; 11-17-2017 at 03:19 PM. Reason: little update
Hi!
Thanks for all the help!
I was wondering how i construct the formula if i need the median for both apple and orange for example. I have tried to put in the AND-function but have not succeeded. I have attached a file with the desired output. Hope you can help me!
Try This formula in H25 and copy to H25:M27
Formula:Please Login or Register to view this content.
See the file
i suggest you don't label the cell like you did in G15:G17. it is possible but difficult for the formula to pick it up. for what you did in G20:G22, add in the 2nd fruit in H20:H22
Orange
Apple
Banana
Ensure I19:J19 has the Type too; A. B.
then in I20:
=MEDIAN(IF((Tabell1[Fruit]=$G20)+(Tabell1[Fruit]=$H20),IF(Tabell1[Type]=I$19,Tabell1[Value])))
ps: it's also good to try the solutions or acknowledge the efforts of other members who chipped in. i think Jose's AGGREGATE formula might work faster. but i hardly give AGGREGATE solutions as they are only available from Excel 2010 onwards. it will fail if you send it to someone else with lower version.
sandy's solution should be interesting too. tedious at the start but easier to maintain later. calculates faster. again, only available from Excel 2010 onwards
Hi,
Thank you very much! All of you! It is truly amazing with all the competence and willingness to help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks