This user's experience is a perfect example of why trusting a spreadsheet's output without understanding its logic can lead to genuine confusion. The problem here is not a broken formula or a faulty data set, it is a fundamental misunderstanding of what a Z-score represents and how Excel's functions are designed to work. The user expected the average of their Z-scores to be zero and the standard deviation to be one. Instead, they got an average of negative four and a standard deviation of two. That is not a glitch; that is a signal that the values used in the STANDARDIZE function did not match the actual mean and standard deviation of the data.
Let's break down what happened. The user calculated Z-scores by manually entering the mean and standard deviation into the formula. If those numbers came from a different dataset, or if they were computed incorrectly from the original data, every Z-score will be shifted and scaled incorrectly. A Z-score is simply a measure of how many standard deviations a value is from the mean. If the mean you plug in is off by even a small amount, the entire column of Z-scores will be systematically wrong. The fact that the user's average Z-score is negative four suggests that the mean they used was higher than the actual mean of their data, so every score was pushed downward. Similarly, a standard deviation of two instead of one means the scaling factor was off.
The practical lesson here is straightforward: never hardcode summary statistics into a formula that should be dynamic. Instead of typing a number for mean or standard deviation, reference the cells where those values are calculated using AVERAGE and STDEV.P or STDEV.S. This ensures that when your data changes, or when you copy the formula down, the Z-scores remain accurate. For a dataset of 170 points, it is also worth checking for outliers or data entry errors, especially since the user mentioned null cells. Removing nulls is correct, but if the mean and standard deviation were calculated before the nulls were removed, the numbers would still be inconsistent.
What this user really needs is not a better formula, but a clearer workflow. Calculate the mean and standard deviation of the cleaned data in separate cells. Then use those cell references in STANDARDIZE. After that, run AVERAGE and STDEV.P on the Z-score column. If the results are still not zero and one, the original data likely contains an error or the nulls were not fully excluded. This is not a failure of Excel or of the user's ability, it is a reminder that spreadsheets are only as reliable as the assumptions we build into them. For anyone teaching themselves statistics with a screen reader, the path forward is to verify each intermediate step rather than trusting the final output at face value.