Median and Mode with Criteria
Hi
I am working out some averages (Mean, Mode, Median) for a large data set.
For mean I have used AVERAGEIFS as I have some criteria I want to check against.
Now I want to do the same for Mode and Median but I don't see the *IFS* functionality in Excel. Is there a way round this could I create some sort of compound formula something like (mode(IFS(A:A,"x",B:B,"y")).
thanks in advance
Robin
You might want to investigate the AGGREGATE function: https://support.office.microsoft.com...ain11.chm60533
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.
Try
=MODE(IF($A:$A=x,$B:$B))
or
=MODE(IF(($A:$A=x)*($B:$B=y),$C:$C)
BUT limit the range rather than whole columns.
Enter with Ctrl+Shift+Enter
Mean (Average) Function: One of the most used statistical functions in Excel is Average. Simply use the Average function and select the range which needs to be averaged.Formula:
Please Login or Register to view this content.
Median : Median is a function which is used to find the middle number in a given range of numbers.
Formula:Please Login or Register to view this content.
Mode : Mode helps you to find out the value that occurs most number of times. When you are working on a large amount of data, this function can be a lot of help. To find the most occurring value in Excel, use the MODE function and select the range you want to find the mode.
Formula:Please Login or Register to view this content.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
@AVK: read what the OP requires ...
.... as I have some criteria I want to check against......
Now I want to do the same for Mode and Median but I don't see the *IFS* functionality in Excel. Is there a way round this could I create some sort of compound formula something like (mode(IFS(A:A,"x",B:B,"y")).
Thanks everyone.
John's method is working like a charm
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks