Okay, all that data at the top of the sheet needs completing so let’s attack it piece by piece:
So what is Std Dev you may be asking, it simply stands for Standard Deviation and if the whole concept of Standard Deviation (or SD) is new to you then without getting into the complicated maths, it basically gives you the “deviation” to the average that is considered “standard” for the range of results in that average. In order to work it out for a range you simply use the STDEVP function, i.e:
This number is important when determining Z-Scores which we will be addressing later. For now though, see if you can get this formula to be executed on the condition there is at least two task results to work with and that there is a maximum score included.
Task Average as a Percentage
So how do you work out the average result for a task? You divide the raw average by the maximum. Simple:
So what’s different with that result to the one from our screenshot at the top? Well for starters, ours is rounded and it is a percentage. This problem can be solved in two ways, either:
- Format the cell: This ensures the data can be used for other things later
- Alter the formula: This can make the data look correct, but not be useable
Both solutions are fine in this case since the data in this row is only going to be informative to the user and not actually be used for other formulae…technically though, the first solution is advisable since it leaves the data usable should we change our mind. Since we haven’t encountered formatting yet feel free to leave this decision until later but do fix the formula to make sure it only gets executed under the right circumstances.
Okay, let’s move onto z-scores…