I need an excel expert

I am trying to build an automated case mix projection worksheet. I am an MDS Coordinator in the state of Ohio and case mix is basically what gets us reimbursed in a nursing home. In case mix we have scores called "RUGS" and they are 3 digit letters such as RMB, RVA, HD1, and many more. Each RUG score has a corresponding weight. Example RMB = 3.2667 I have a resident roster and on this roster I want to be able to select a RUG score from a drop down menu and the corresponding weight (which is aka CMI) should auto-populate in the next column. I need a roster of 88 residents. Next, it should add all of the weights together and that total number is divided by total of residents and that gives you your overall case mix index. Now here is the tricky part, I need another over all score that will auto-calculate the total weights together MINUS residents and their weights with RUG scores of PA1 and PA2 as those RUG scores do not count. I have made a mark-up of what I am looking for that I feel will explain it much better. Of course I want the sheet to look pretty professional and well designed.