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 data--just the cell numbers that hold the data.  

  • For example, Excel formula for mean: [=average(first cell#:last cell#)]

• *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)

• 95% Level of Confidence is P=.05 (also stated as: 95% Confidence Interval) 

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:  [=(cell-mean)]  (2 places)

• You could also say "textually" that Deviation is:  

  • [=(cell# of X minus actual mean value)]

• 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 (X-Mean) for each X value.

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

  • X-Mean:  [=(a2-15.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 (X-Mean).  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: [=(X-mean)^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 cell-lowest value cell)]

Squared Deviations:  [=(X-mean)^2]

• See "Deviations" above for more specifics on how to drag the original formula down so you don't have to type in each cell entry one at a time.

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)

• Remember, you first need the deviation for each value.  Deviation=[(X-Mean)].  See "Deviations" and notes above for more information.  

Sum Squared:  [=sum(cell)^2]

Variance:  [=var(cell:cell)]

Z Maximum:  [=(max cell#-mean)/SD]

• You need to plug in the actual cell number that contains the maximum range value and standard deviation.

Z Minimum:  [=(min cell#-mean)/SD]

• You need to plug in the actual cell number that contains the minimum range value and standard deviation.

 RonJones.Org | Back to Top | Back to CSUN 610 | Site Map

Ron Jones/www.ronjones.org (11-3-01)

                      Get Fit.  Be Strong.
                                 Corporate Wellness · Consulting · Health Promotion