Fluxuating Weighted Average

I’m attempting to create an excel spreadsheet to calculate a weighted average based off of 5 different categories. This chart will act as a rating system for individuals based on a 1 to 5 scale. The part that I’m having trouble getting past is how to have Excel change the weights is someone has no result for one of the categories. I would prefer that the weigh for the category missing category be pro-rated and added to the weight of the categories that are available. Doing this with a if doesn’t seem efficient. Does anyone have any suggestions?

do you have an example of what you have / want?