| G6 | =SUM(B6:F6) |
| H6 | =IF(G6>=F$2,B$2,0) |
| I6 | =B$1*G6+H6 |
| J6 | =RANK(I6,I$6:I$13,0) |
| B15 | =AVERAGE(B6:B13) |
| B16 | =MAX(B6:B13) |
| B17 | =MIN(B6:B13) |
| D19 | =COUNTIF(H6:H13,B$2) |
| G3 | =D3*D$1+E3*E$1+F3*F$1 |
| H3 | =IF(G3>=K$3,L$3,IF(G3>=K$4,L$4,L$5)) |
| I3 | =RANK(G3,G$3:G$8,0) |
| M3 | =COUNTIF(H$3:H$8,L3) |
| M7 | =SUM(M3:M6) |
| N3 | =M3/M$7 |
| D10 | =AVERAGE(D3:D8) |
| D11 | =MAX(D3:D8) |
| D12 | =MIN(D3:D8) |
| B2 | =UPPER(A2) |
| C2 | =LOWER(A2) |
| D2 | =SUBSTITUTE(C2," ","_") |
| E2 | =CONCATENATE(D2,"@shcc.edu.hk") |
| C9 | =TODAY() |
| C11 | =C9+1000 |
| C12 | =C9-C10 |
| B2 | =A2^2+2*A2-1 |
| D1 | =RANDBETWEEN(0,9) |
| D2 | =VLOOKUP(D1,A1:B10,2,FALSE) |