In Column 3, beginning at Line 18, calculate the number of expected data values
ID: 3353291 • Letter: I
Question
In Column 3, beginning at Line 18, calculate the number of expected data values that should be less than the value of x on the corresponding line of Column 1. (For example, since there are 110 points, you know that all 110 points must have a value that is less than infinity, and 55 points should have values that are less than xmean). The task reduces to simply multiplying the value in Column 2 by 110.
Next, find the values of x that would give roughly 10 data values in each bin. For example, start with x1 = 59.5 so that the bin from -infinity to x1 will have roughly 10 values. Then take x2 = 61 because 20.765 - 10.27 is roughly 10. Put the appropriate x values in Column 5 (starting with Row 1) and put the expected number of points in Column 6. You will have 5 values of x in Column 5. At the end of the N values in Column 6, add an extra value that represents the number of data points that are expected to be larger than the last x value.
x F(x) (NORMDIST()) N < x x N 55.0 0.004230108 55.5 0.006436976 56.0 0.009607194 56.5 0.014065548 57.0 0.020203627 57.5 0.028476627 58.0 0.039392692 58.5 0.053493431 59.0 0.071325025 59.5 0.093400555 60.0 0.12015554 60.5 0.151900217 61.0 0.188773326 61.5 0.230702976 62.0 0.277380238 62.5 0.328250287 63.0 0.382524258 63.5 0.439212591 64.0 0.497177938 64.5 0.555203031 65.0 0.612066836 65.5 0.666621093 66.0 0.717859312 66.5 0.764971368 67.0 0.807378876 67.5 0.844749133 68.0 0.87698817 68.5 0.904215853 69.0 0.926727741 69.5 0.944949312 70.0 0.959388151 70.5 0.970589012 71.0 0.979095359 71.5 0.985419586 72.0 0.990022621 72.5 0.993302469 73.0 0.995590362 73.5 0.997152755 74.0 0.998197276 74.5 0.9988809 75.0 0.999318915
Explanation / Answer
Please find snapshot of answer spreadsheet with formulae given in top header row.