Excel
Formulas Tip Sheet

Note: I use bracket symbols [ ] to
represent the actual cell borders in the following examples.

For individual cells, you *usually
don't have to enter exact
numerical datajust the cell numbers that hold the data.

*Note:
See exceptions below under "Deviations"
where the actual mean value needs to be entered and not just the cell #
where the mean value is located.

Significant figures or places
needed for final values are marked
in green parenthesis after the formula (if I know what they
are). A basic rule is "one more place than you need" for
your actual value according to Dr. M.
[Excel Formulas] & Option
Basics
Coefficient of
Variability:
[=(stdev/mean)*100]
Confidence Interval: [=confidence(0.05,stdev,n)]
(2 places)
Correlation Coefficient:
[=(n*åxy(åx*åy))/sqrt((n*åx^2(åx)^2)*(n*åy^2(åy)^2))]
Count:
[=count(cell:cell)]
Data Analysis Option:
(Note: I use
> to symbolize "go to") >descriptive
statistics>input your cell ranges i.e. (cell:cell)>summarize
statistics (then expand column width so you can read them better) Deviation:
[=(cellmean)] (2
places)

You could
also say "textually" that
Deviation is:

This is the formula to use if
you want to drag the original cell formula down for the whole column.
If you try to use the cell# where the Mean is actually located, you'll get
error messages except for the very first cell with the original
formula.

To drag the whole formula
down so it will work for the other cells, grab the original cell boundary in
the lower right corner (the little box), click mouse, drag down to bottom of
column, release mouse, and you should have the (XMean)
for each X value.

For example
if the Mean=15.6
and your X value=25
(25 is located in cell a:2):

XMean:
[=(a215.6)]
Now click on little square on lower right hand corner and drag down to
last X value, release mouse, and remaining cells will be calculated for (XMean).
If you use the first formula
[=sumsq(cell:cell)] for
whole data column, you only get the right answer for the original cell
with formula. The remaining cells get error messages or wrong
answers. This is confusing, but I don't know how else to explain
it.

You would
use the same methods if you wanted to get the Squared
Deviations: [=(Xmean)^2]
values.
Level
of Confidence: See
"Confidence Interval" above.
Maximum:
[=max(cell:cell)]
Mean:
[=average(cell:cell)]
(1 more place than you need)
Median:
[=median(cell:cell)]
Minimum:
[=min(cell:cell)]
Mode:
[=mode(cell:cell)] Multiply
Symbol: [ * ]
Pearson Correlation:
[=pearson(a2:a38,b2:b38)]
 Array 1=x and
Array 2=y
 My range of sample
cells is x (or
array 1)=a2:a38
and y (or array 2)=b2:b38
Power
Symbol:
use ^ symbol (10 squared in Excel= [10^2]
Range:
[=(highest value celllowest value cell)] Squared
Deviations: [=(Xmean)^2]
Standard Deviation:
[=stdev(cell:cell)]
(2 places)
Standard
Error of Mean: [=sqrt(var/n)]
(2
places)
Sum:
[=sum(cell:cell)]
Sum of Squares:
[=sumsq(cell:cell)]
(3 places)
Sum
Squared: [=sum(cell)^2]
Variance:
[=var(cell:cell)]
Z Maximum:
[=(max cell#mean)/SD]
Z Minimum:
[=(min cell#mean)/SD]
RonJones.Org
 Back to Top  Back to CSUN
610  Site Map
Ron
Jones/www.ronjones.org (11301)
