Mode type function for text array

Scott26

New Member
Joined
Mar 21, 2012
Messages
21
Hi. I know there are many many similar threads to this, but I can't get any of them to work for me. I don't know a lot about VBA which is a shame. I'm trying to get to grips with it, because I'm certain it'll make my life easier. In the meantime, maybe one (or more) of you guys can.

I basically want a formula that replicates the mode function for text values.

I have a spreadsheet with lists of who has scored in football matches for a number of different teams in a league, in each week. So for week 1, A3 to A22 are the team names. And the player names are in B3 to E22 (but could potentially stretch across to say J22 if there were a lot of goals).

Each week has the same format. Team names in the A column, Scorer names in the B through J columns. Week 2 is rows 26 to 45. Week 3 is 49 to 68. And so forth through to week 38, rows 854 to 873.

What I want is a formula for another sheet, which says that Team A's top scorer is Player A, and the second top scorer is Player B. And Team B's top scorer is Player C and the second top scorer is Player D. So Team A's top scorer can be read on the 'overview sheet' using the information from the 'scorers sheet'.

I'm using MS Excel 2007, on Windows 7.

Any useful thoughts or solutions would be much appreciated, thanks.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
The problem i'm having is that

=INDIRECT("R"&SUBSTITUTE(MIN(IF(COUNTIF(Goals!B3:J22,Goals!B3:J22)=MAX(COUNTIF(Goals!B3:J22,Goals!B3:J22)),ROW(Goals!B3:B22)+COLUMN(Goals!B3:J3)/10)),".","C"),FALSE)

doesn't work, I think because I have blank fields. And having non-blank fields obviously returns "" as the most frequent scorer. And this only returns the top scorer of the week, regardless of team. I want the top scorer of the team, over the whole season.
 
Upvote 0
Or I can do a similar thing with

=INDIRECT("R"&SUBSTITUTE(MIN(IF(COUNTIF(Goals!$B3:$J3,Goals!$B3:$J3)=MAX(COUNTIF(Goals!$B3:$J3,Goals!$B3:$J3)),ROW(Goals!$B3)+COLUMN(Goals!$B3:$J3)/10)),".","C"),FALSE)

to find the top scorer for each team for that week. But I can't extrapolate from this to find the top scorer for the team for the season.
 
Upvote 0

Forum statistics

Threads
1,223,054
Messages
6,169,834
Members
452,284
Latest member
TKM623

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top