Creating a formula to find the most common string given a second variable?

[aussiebob]

I will start this by saying I don't know much about excel, but I do know that formula for this problem is beyond my skill level and beyond what a simple Google search will answer.

The challenge is:
I have 5 areas (Cans/Contingent/Kegs/Maintenance/Bottle) that submit daily codes (20 different code choices) and give each codes a ranking (1-5).

I would like to create a couple of formulas;
1) For each area I need to know which is the most common code used
2) What is the mean rating for that most common code

In the example below I have shown data on the right (Areas/Codes/Ranking) and on the left are the cells I need formula for (?).

This example below shows all the variable but only a fraction of the data - otherwise I could manually workout the answers but since I get so much data and it needs to be reviewed weekly I would prefer a function.

Any help before I pull out what little hair I have left would be greatly appreciated.

Area <tbody> </tbody>	Most common code <tbody> </tbody>	Average rating of most common code <tbody> </tbody>				Area <tbody> </tbody>	Code <tbody> </tbody>	Rating <tbody> </tbody>
Cans <tbody> </tbody>	????	????				Cans <tbody> </tbody>	HMG <tbody> </tbody>	2
Contingent <tbody> </tbody>	????	????				Contingent	HPE	1
Kegs <tbody> </tbody>	????	????				Maintenance	HHE	2
Maintenance <tbody> </tbody>	????	????				Cans <tbody> </tbody>	HCE	1
Bottles <tbody> </tbody>	????	????				Kegs	HOT	2
						Contingent	HFT	3
						Cans <tbody> </tbody>	HLD	4
						Kegs	HSF	3
						Cans <tbody> </tbody>	HMG <tbody> </tbody>	2
						Contingent	HEB	2
						Cans <tbody> </tbody>	HPE	4
						Cans <tbody> </tbody>	HNM	1
						Kegs	HCE	1
						Maintenance	HBM	5
						Cans <tbody> </tbody>	HFT	3
						Bottles	HPE	3
						Kegs	HBN	2
						Cans <tbody> </tbody>	HMG <tbody> </tbody>	4
						Cans <tbody> </tbody>	HBN	5
						Kegs	HCS	5
						Contingent	HEB	2
						Cans <tbody> </tbody>	HCE	3
						Maintenance	HMH	5
						Kegs	HBN	1
						Bottles	HPE	1
						Contingent	HOT	2
						Cans <tbody> </tbody>	HER	3
						Kegs	HMG <tbody> </tbody>	2
						Bottles	HFT	3
						Kegs	HSF	4
						Cans <tbody> </tbody>	HCE	1
						Contingent	HEB	5
						Maintenance	HPE	2
						Kegs	HHE	4
						Cans <tbody> </tbody>	HNM	2
						Bottles	HCE	3
						Contingent	HBM	1
						Maintenance	HLD	5
						Kegs	HBN	2
						Cans <tbody> </tbody>	HMG <tbody> </tbody>	5
						Contingent	HPE	3
						Bottles	HFT	1
						Contingent	HOT	4
						Maintenance	HBN	2
						Cans <tbody> </tbody>	HMH	5
						Kegs	HSF	5
						Bottles	HEB	4
						Cans <tbody> </tbody>	HCE	3
						Maintenance	HNM	2
						Contingent	HPE	4
						Cans <tbody> </tbody>	HMG <tbody> </tbody>	1
						Kegs	HBM	3
						Cans <tbody> </tbody>	HCS	2
						Contingent	HNM	4
						Contingent	HHW	5
						Bottles	HPE	2
						Cans <tbody> </tbody>	HFT	4
						Kegs	HOT	2
						Bottles	HMV	4
						Cans <tbody> </tbody>	HCE	1
						Maintenance	HMV	3
						Contingent	HBM	3
						Kegs	HSF	1
						Cans <tbody> </tbody>	HPE	5
						Contingent	HEB	4
						Contingent	HHW	2
						Kegs	HMG <tbody> </tbody>	5
						Bottles	HCS	1
						Cans <tbody> </tbody>	HLD	3
						Bottles	HMH	2
						Maintenance	HOT	5
						Kegs	HCE	3
						Cans <tbody> </tbody>	HFT	4
						Kegs	HEF	2
						Contingent	HPE	5
						Cans <tbody> </tbody>	HEB	1
						Maintenance	HPT	1
						Cans <tbody> </tbody>	HSF	3
						Contingent	HHE	4
						Cans <tbody> </tbody>	HPE	2
						Bottles	HCE	5
						Contingent	HMG <tbody> </tbody>	4
						Cans <tbody> </tbody>	HPT	3
						Kegs	HFT	5
						Bottles	HMH	2
						Cans <tbody> </tbody>	HLD	1
						Kegs	HCS	4
						Maintenance	HOT	5
						Contingent	HPE	2
						Kegs	HEE	5
						Cans <tbody> </tbody>	HEB	1
						Kegs	HCE	4
						Contingent	HMH	3
						Cans <tbody> </tbody>	HFT	5
						Kegs	HMG <tbody> </tbody>	2
						Cans <tbody> </tbody>	HSF	4
						Maintenance	HCE	3
						Cans <tbody> </tbody>	HPE	1
						Kegs	HMG <tbody> </tbody>	2

<tbody>
</tbody>

Thanks in advance!