How do I come up with a formula to average cells?

[anthonyls1]

[TABLE="width: 605"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]
[/TD]
[TD]Manager[/TD]
[TD]Supervisor[/TD]
[TD]Manager[/TD]
[TD]Supervisor[/TD]
[TD]Manager[/TD]
[TD]Supervisor[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]Daily work production[/TD]
[TD="align: right"]85[/TD]
[TD] [/TD]
[TD="align: right"]80[/TD]
[TD] [/TD]
[TD="align: right"]89[/TD]
[TD] [/TD]
[TD="align: right"]84.67[/TD]
[/TR]
[TR]
[TD]Accuracy of work[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]89[/TD]
[TD] [/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]90[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Above scores is given by managers and supervisors. The daily work production row is scored by the manager only and the supervisor didn't need to make any new entry as he or she felt the scores are correct. I now can make an average of the three scores. The accuracy of work (row) is given by both the manager and supervisor. My question is how do I format the accuracy row to only read the supervisor scores and ignore the manager's score when supervisors make an entry. So for accuracy, the first two are for the same question manager gave a score of 85 and the supervisor gave a score of 90. A supervisor score in this example will override the manager's score. The next set of scores (columns) the manager gave an 89 and the supervisor felt it was correct so no need to make an entry by the supervisor. The last set the manager gave a score of 89 and the supervisor felt a need to correct the score to 90. This is just an example. I have a lot of rows to average out. I wanted to know if there is a formula where I can average the score per row like above but in a case where both managers and supervisors score on the same row, I would like it to only read the supervisor score unless the supervisor felt the score was correct like the second row above where the supervisor didn't score the middle question. So, in this case, the score will consist of a 90, 89 and 90. Is there a formula where if a supervisor scores than the manager's score becomes 0 but if the supervisor doesn't give a score then the manager's score will be included in the average? Thanks everyone for any and all help.

 

[Tetra201]

Maybe

=(SUMIF(C1:G1,"Supervisor",C3:G3)+SUMIFS(B3:F3,B1:F1,"Manager",C3:G3,"",C1:G1,"Supervisor"))/COUNTIF(C1:G1,"Supervisor")

 

[anthonyls1]

Maybe

=(SUMIF(C1:G1,"Supervisor",C3:G3)+SUMIFS(B3:F3,B1:F1,"Manager",C3:G3,"",C1:G1,"Supervisor"))/COUNTIF(C1:G1,"Supervisor")

Thank you Tetra201! This will work!

 

[Tetra201]

Glad to have helped.