Home » Blogs » dave s's blog

Getting MODE if certain criteria is met

Submitted by dave s on 9 November, 2015 - 16:39

In my data I have a list of sales (amounts) by cities. How can I get the mode for the sales only where the city = chicago? I have sorted the list by city and written the mode formula based on my sort to get only Chicago sales (rows 10:20). This works as long as the list stays sorted because the mode range is specific to to the sort results.

Thanks

»

Solution

Submitted by Max F. on 9 November, 2015 - 19:15.

The easiest way I can think to do this is to add a few columns/fields like this:

You want to add a column that pulls out your city's sales. And add a field wehre you can indicate which city you would like to look at, and a field to show the Mode of that city.

For the Column City's Sales. I used this and coppiedit down: =IF(A2=$E$2,B2,"")

For the Mode I used: =MODE.MULT(C:C)

(Keep in mind if you do not have multiple occurances of any number, then you will get #N/A as your result)

As far as I know there isn't a formula that will allow you to gather the mode with conditions, so you have to do a little manipulation on your own.

I hope this helps!

 

-Max

»

Thanks

Submitted by dave s on 9 November, 2015 - 20:39.

Max, thanks. I will give this a try.

»