Formatting Column Color And Averages Based On Drop-Down Variables

So I'm a teacher and Im trying to put together a grading structure and I'd like to share it with my colleagues if I can make it user-friendly.

Please see the image to assist with the word salad I'm about to serve.

The way I've set it up is such that each assignment could be assessed for up to three outcomes. The 'Algebra Quiz' for example is used to assess both division and algebra. There is a drop down menu in each of the three columns under each assignment. Each drop down has each of the 13 colored curriculum areas to the right.

Now, I am new to using formulas, so I am unsure how feasible these ideas are, but I would really like to do two things that I can't figure out. I've tried searching online, but it's not working so far.

Okay, the first thing I'd like to do is make it so that the column under the selected outcome from the drop down matches the color of the outcome on the right. For example, the 'DIVIS' color is orange. If a 'DIVIS' is selected from the drop down, can the column beneath change to orange as I have shown? But is it possible to set it up so that any column can be set to match the color of whichever of the 13 outcomes are chosen? It would mean a lot of conditional formulas per cell, so I am not sure if that works. Each column would have to respond only to the drop down above it.

Second, and this one seems like witchcraft, honestly, I'd like to create a running average on the right 'total' section. I have used the regular average formula to do this with columns that hold static information, but these columns could hold one of 13 different outcome types. Basically, is it possible to average, say, the 'ratio' outcome for Billy even though that value could pop up in some or all assignments, in any of the three columns within said assignments?

I would truly appreciate any help I can get with one or both of these struggles. This would save my coworkers and I some time and hassle. Thanks in advance to anyone willing to take on this challenge.

https://ibb.co/HDTDvg4X