Excel 2007 : Mode formula

[jkee]

Am using Excel to count ballots for a car show. I have a series of numbers in each of several categories.

Mode tells me which car got the most votes for first place in each category.

Once I find the number that occurs most often using mode formula, is there a formula to find the number that occurs second most often. Only thing I can do right now is delete the number that occurs most often from the series until I see the mode change in each category. Only then can I identify the one that occurs second most often to award a second place trophy.

Current mode formula also does not let me know when there is a tie. It goes automatically to the first "mode" in the series.

There's got to be a simpler way.
Thanks

[Paul]

Hi JKee, welcome to the forum.

Rather than using MODE to find the most repeated value, perhaps consider using COUNTIF to determine the count of each numeric response. Then use the LARGE function to return the first, second, third, etc. largest values. If you need to break ties you could also take a look at this: http://www.contextures.com/excel-functions-rank.html (specifically the Breaking Ties section).

[daddylonglegs]

You could show values in descending order of appearances like this, assuming the data is in A2:A100

Put this formula in C2

=MODE(A2:A100)

Now in C3 use this formula

=MODE(IF(COUNTIF(C$2:C2,A$2:A$100)=0,A$2:A$100))

that's an array formula that needs to be entered with CTRL+SHIFT+ENTER

copy down as far as required

You can use a COUNTIF in the adjacent column to see the number of occurrences of each