# Formula to pull city that shows the most times, including a number..

1. ## Formula to pull city that shows the most times, including a number..

Good afternoon,

Is there a formula to count the city that shows the most in the below 6 cells?
I was going through countifs and everything, but it seems I will need something more complicated...

I'm looking for the next cell to basically result in "Sacramento" here since they show 4 out of 6 times.
Sacramento 1
Sacramento 0
Sacramento 1.6
Washington 0.6
Washington 1.6
Sacramento 5.7

Also, would it be possible to factor in the average and result in Sacramento 2.075 ?

Thanks for the help!!

2. ## Re: Formula to pull city that shows the most times, including a number..

Maybe try a Pivot Table.

3. ## Re: Formula to pull city that shows the most times, including a number..

A pivot table is almost certainly the best solution but if you wanted to do it with formula I think it could be done.

Assuming that the city (column A) and the number (column B) are in different columns, you could find the most common city then use averageif to get the average for that city. This will probably break if there is more than one city which occurs the same number of times, regardless

=INDEX(A1:A7,MODE(MATCH(A1:A7,A1:A7,0)))

This will find the most commonly occurring item in the range A1:A7

Then just use =AVERAGEIF(A1:A7,A8,B1:B7) to average the corresponding values

But pivot table is still the best way I think.

There are currently 1 users browsing this thread. (0 members and 1 guests)