A Z-Score is how many standard deviations that task result is from the average. So if you look at the following screenshot of what we’re going to create, you will see it in action:
Have a look at Dean Cameron’s result for Task 1. He received 71 and the task average was 53.9. This gives Dean a deviation from the average of “71 - 53.9” which is 17.1. If we divide this by the Standard Deviation we get “17.1 / 8.8932558717” which gives us our Z-Score of 1.9228. This number may not seem that important but it gives a more accurate idea of where a task result sits in amongst the complete set of results.
So now that we have an idea of what a Z-Score is, how do we implement it in Cell “F8“? We’ve discussed the logic but converting the logic into a correct formula is not always easy. I’ll give you the formula to work out the Z-Score, but you will need to wrap it in condition checking formulae yourself:
(E8 - E$4) / E$3
You can then adapt your formula so that it works for the other tasks in the sheet.
Okay, you’ve seen plenty of big formulae now but it’s time to hit you with a monster:
=IF(COUNTIF(E8:N8,"")>9,"",IF(OR(E$6<>"",G$6<>"",I$6<>"",K$6<>"", M$6<>""),(IF(E$4<>"",((E8/E$5)*E$6),0)+IF(G$4<>"",((G8/G$5)*G$6), 0)+IF(I$4<>"",((I8/I$5)*I$6),0)+IF(K$4<>"",((K8/K$5)*K$6),0)+ IF(M$4<>"",((M8/M$5)*M$6),0))*100/SUM(E$6:N$6),""))
I’m not going to expect you to come up with that formula yourself since this tutorial really isn’t about maths but you should at least know that what it does is give us a raw total using the task results and percentages that have been included. It’s a simple idea in essence but in practice it’s not so easy to execute. I suggest you copy the formula into Q8, autofill it down and we’ll just move on from it :-)
Finishing Columns Q and R
So now we have three things left to do for these two columns to be complete:
- Cell Q4: We’ve done averages a few times now, you should be able to do this.
- Cell Q3: We’ve done SD a few times too so you should be fine with this one.
- Column R: We’ve also done Z-Score now so this too should be possible for you.
This particular lesson involves doing quite a lot, so I suggest having a look at the initial screenshot again and just confirming that everything is done. If you have any questions feel free to ask below…this was a particularly difficult lesson!