Median and mode with criteria

[UsefulDragon]

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!

[#2 · Accepted solution]

@UsefulDragon 

=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]

@UsefulDragon 

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)

[#4]

Exactly, thank you!