competency test

[sna]

Hi dear
I need your help to calculate competency test scores.
I have sheet score by roles this sheet required manual input.i want to calculate average scores of three column in percentage .for example if D5 in Score by roles is 5 so in sheet score by branch would be 100% it means if it has only one column so result 5*20%.but two columns among three has value say 4,4 so 4+4=8/2 =4*20% =80%.but it contains three columns 4,4,4 so 4+4+4=12,12/3=4*20% =80% etc
I also need to hightlight cells like the header in row 2 to row 5 in sheet score by roles and score by branch
Please find attached


Thanks

[snasui]

Please fill the example results in related sheets and cells and attach file again.

[sna]

Here is a sample of output

[logic]

I guess the formula in 'C7' is:

='Scoring by roles'!D8*0.2

[sna]

yes i know one column but if it has three columns because the competency test takes for BO,SBO,HBO by branch like ASL,AST,BKN etc
I also need conditional formatting
If average score is > 80% (yellow)
If average score is = 80% (Green)
If average score is < 80% (red)

[snasui]

yes i know one column but if it has three columns

What columns and where is the result value of each column in your attached file?

You always need to fill the example value that related with your results in every question for easy to understand.

[sna]

In row 6 in Sheet score by roles and sheet score by branch represent branch name in row 7 of both sheet is the position.
The test need to be done for three position:BO,SBO,HBO so in these columns have to have value but sometimes it has value only two columns or one column but most often three columns must fill with value in order to calculate score of average three columns multiply by 20%.
for example in sheet Score by roles
C8:4 D8:3.7 E8:3.2 in sheet score by branch C7:72.67%
mostly calculate average of every three columns for each branch with three position

Thanks

[snasui]

Try this,

Sheet Scoring by branch

C7

=AVERAGE(OFFSET('Scoring by roles'!$C8,0,3*(COLUMNS($C7:C7)-1),1,3))*0.2

Enter > Copy to right and down

Conditional Formatting

Select C7:B20 > Menu Home > Conditional Formatting > Manage Rules > New rule > Format all cells that contain > On Format only cells with: Select Cell value, Less than, and fill 0.8 respectively > Click Format and select Fill tab > Select Red color > OK > OK

Click New Rule > On Format only cells with: Select Cell value, Equal to, and fill 0.8 respectively > Click Format and select Fill tab > Select Yellow color > OK > OK

Click New Rule > On Format only cells with: Select Cell value, Greater then, and fill 0.8 respectively > Click Format and select Fill tab > Select Green color > OK > OK > OK

[sna]

Thank you so much