How do I compare a mean with a constant using EXCEL?
The one-sample t-test is not listed as a procedure in EXCEL but it can be done using a single column of numbers whose mean we wish to compare to a constant, C.
We use five functions which are provided in EXCEL: namely AVERAGE which works out the mean of column of numbers, STDEV which works out their standard deviation, COUNT which determines the number of observations, SQRT which takes the square root and TDIST which evaluates the one or two-sided p-value.
In what follows we assume the sample values are contained in cells A1 to A10.
Specifically to compare sample mean to a constant, C we use
One-sample t = SQRT(COUNT(A1:A10))*((AVERAGE(A1:A10)-C)/ STDEV(A1:A10)) = T0
We can then use the TDIST function to evaluate the p-value for the one-sample t statistic, T0, which will tell us if the sample mean differs from C using
TDIST(ABS(T0),COUNT(A1:A10)-1,2)
where the last argument, taking a value of 1 or 2, specifies a one (or directional where the sign of T0 matters) or two-tailed (or non-directional where the sign of T0 does not matter) test. I the example above we assumed we wanted a two-tailed p-value.
Note that we use the absolute value function, ABS, in the above as TDIST, for some reason, only evaluates test statistics, of form T0, which are positive.