Max value

I’ve attached a spreadsheet that I received help on earlier today. I’d like to add a few more components, and I’m once again stumped/not even sure if what I want to do is possible. The document tracks task status with an overall team status. Following are the new components I’d like to introduce, but have no idea where to start:

• For each team status, if there isn’t a max value, currently I get an #N/A error. Is it possible to automatically return a value of red for this scenario?

• The team status formula currently excludes the “a” value. Is there a way to reflect an “a” for team status only once all tasks are marked with an “a”?

• Overall Status – how can I calculate an overall status based on each of the team status? Same rules as the individual teams status . . .