gravanoc
Active Member
- Joined
- Oct 20, 2015
- Messages
- 348
- Office Version
- 365
- Platform
- Windows
- Mobile
I'm attempting to use a formula to determine which region has the most occurrences of a specific value. There are 9 regions, and if that specific value occurs in the same row as a region then that is counted. I'm using the sum of two COUNTIFS because there are two tables being looked at, then using RANK.EQ to return the region rankings. This is all wrapped in an IF, which looks to match the rankings with the number 1, then returning that #1 region.
The first problem is that I need some way of reducing the resulting array to the #1 value. For example, using MIN on an array of numbers would reduce it to just 1 number. The final result might include 8 blanks along with the answer. The other issue is if there is a tie, I would like to use some method to break it. I was thinking RANDARRAY might work, but I'm not sure.
Formula:
The first problem is that I need some way of reducing the resulting array to the #1 value. For example, using MIN on an array of numbers would reduce it to just 1 number. The final result might include 8 blanks along with the answer. The other issue is if there is a tie, I would like to use some method to break it. I was thinking RANDARRAY might work, but I'm not sure.
Formula:
Excel Formula:
=IF(RANK.EQ((COUNTIFS(Tags2021[Division (selected)],$Y$4:$Y$12,Tags2021[Channel Name],'Alpha Indivdual'!T4)+COUNTIFS(Tags2022[Division (selected)],$Y$4:$Y$12,Tags2022[Channel Name],'Alpha Indivdual'!$T$4)),$Z$4:$AA$12)=1,$Y$4:$Y$12,"")
