Forum Discussion
Median and mode with criteria
Hi
Can anyone tell me how to find the median and the mode of values with criteria?
Here is an example of what I mean:
On the left is a list of products, their colors and prices. On the table on the right I want to get the median and mode of the price of the products that are a specific color (in this case blue). But the catch is that I want it to do it automatically, so every time I add a product I dont have to select it myself, but instead the function checks the list of colors and then looks up the prices next to the specific color and returns the media/mode of those prices.
I´ve attached a file if it helps.
Thank you very much!
=AVERAGE(IF(Table1[Color]=[Product color],Table1[Price]))
=MODE(IF(Table1[Color]=[Product color],Table1[Price]))
Is this what you are looking for? Enter the formulas with ctrl+shift+enter if you don't work with Office365 or 2021.
3 Replies
With the permission of those involved, even if Mr. (Quadruple Pawn) Oliver Scheurich suggested solution is most suitable, here is an additional, simplified suggested solution with conditional formatting.
Use conditional formatting to highlight information
Sample file with ready-made formats, only need to enter the color as text.
Thank you for your understanding and time
NikolinoDE
I know I don't know anything (Socrates)
=AVERAGE(IF(Table1[Color]=[Product color],Table1[Price]))
=MODE(IF(Table1[Color]=[Product color],Table1[Price]))
Is this what you are looking for? Enter the formulas with ctrl+shift+enter if you don't work with Office365 or 2021.
- Exactly, thank you!
