Max no. of times a text repeated

[nsd]

Hi,
I have a report that categorises the regions into different catergories for.
e.g.

Region Population Category Education Category Job Category
1 A B
A
2 B C
C
3 A A
A
4 B C
B
5 C B
A

I'm looking for a formula that will check the categories i.e. A, B or C and
give a result in the form of max appeared i.e. in case of Region 1, A appears
twice and in case of Region 4, B appears twice. Or else if all categories are
equal then the result populate the category in Population criteria i.e. in
Region 5 all A, B & C categories appears hence it gives me the result as C
that appears under Population criteria.

Please help and advise.
thanks, nsd

 

[Bernard Liengme]

It is not very cleaver but seems to work:
=IF(COUNTIF(B2:E2,"A")>1,"A",IF(COUNTIF(B2:E2,"B")>1,"B",IF(COUNTIF(B1:E1,"C")>1,"C",B2)))
best wishes

 

[nsd]

Thanks a ton 'Sir' Bernard, it really worked.

I would also request if you can explain this formula to me.
Thanx,
nsd

 

[Bernard Liengme]

Suppose we had: =IF(COUNTIF(B2:E2,"A")>1,"A","The answers is not A")
COUNTIF(B2:E2,"A") counts how may As
If the answers greater than 1 (2 or 3) then A's are in the majority as you
have only three categories
So we return A when this is true and "The answers is not A" otherwise

IF(COUNTIF(B2:E2,"A")>1,"A",IF(COUNTIF(B2:E2,"B")>1,"B","The answer is not A
or B" ))
Now we look to see if A is there more than once, and if not then we see if
B is there more than once.

IF(COUNTIF(B2:E2,"A")>1,"A",IF(COUNTIF(B2:E2,"B")>1,"B",IF(COUNTIF(B1:E1,"C")>1,"C",B2)))
Finally we look for A, then B, then C and if it is none of these is the
majority then we return what ever is in column B

best wishes