1.
Sum
=SUM(A1:B2)
2. Subtraction
= A1-B2
3. Displaying pound sign
Select cells, right click, format cells, currency
4. Monthly balances
Total monthly income - total monthly expenses
For the second month, = (total monthly income - total monthly expenses)+previous monthly balance
5. OK or Overdrawn // Positive or negative income
IF function, formulas, insert functions, IF
Logical test = any value or expression that can be evaluated to TRUE or FALSE
=IF(A1>=0, “OK”, “Overdrawn”)
This means that if cell A1 is larger than 0, it is okay, if it is lower than 0, then it is overdrawn/negative
6. Average
=Average(range)
To find the average of several numbers
7. Locking a cell
=B2*$A$1
Histograms in Excel
Have x and y axis data → drag and select all the data and labels
Insert → Insert statistic chart → histograms
Scatterplots in Excel
Place data in Excel → drag over selected data → insert → scatter
Correlation of scatterplots
=CORREL(range) → to test strength
Time series plot in Excel
Select data values only → insert → line chart → input years by right clicking chart → select data →
on the select data window → edit horizontal (category) axis labels → edit → select x years data
Average = an attempt to calculate a single number that can be used to represent the entire data set
as a typical or central value
- Mean, mode median are ways to calculate averages
Mean = the sum of all values divided by the number of values // computed by summing all the data
values and dividing the sum by the number of items
Mean in Excel =AVERAGE(range)
Median = middle point when the data is sorted in ascending order, if there isn't a middle number,
the mean of the two closest numbers are taken
Median in Excel = MEDIAN(range)
Mode = most frequently occurring value in a data set
Mode in Excel =MODE(range)
● Lookups(VLookups, HLookups, IndexMatch)
● Pivot Tables
● IF Functions (IF, CountIf, SumIf, etc)
● Charts
● Alt Shortcuts
1. Measures of Dispersion
Measures of central tendencies/measure of central location may not be sufficient to fully describe a
data set, the difference is the level of variability around the mean.
If the mean is our measure of central tendency (symmetrical) then use → variance and standard
deviation
If the median is out measure of central tendency (skewed) then use → percentiles, quartiles and
interquartile range
Variance
Population variance in Excel =VAR.P(range)
Sample variance in Excel =VAR.S(range)
Standard Deviation
Problem with variance is that its in the unit squared → which is
a problem if the unit is $ or pounds, therefore, for a better
measure of variability, square root of variance = standard
deviation
- Population standard deviation σ = root of population
variance
- Sample standard deviation s = root of sample variance
Standard deviation in Excel
=STDEV.P(range) for population
=STDEV.S(range) for sample
Percentiles and Quartiles
Percentiles = value that a certain percentage of observations are less than
- The median is 50% → data is split in half
- 25th, 50th and 75th cuts the data into quartiles/quarters
- 3rd quartile - 1st quartile = inter quartile range // Q3 - Q1 = inter-quartile range → finds the
middle 50% of data
Percentiles in Excel
=PERCENTILES.INC(range, p) → if you want 75th percentile, p=0.75
Quartiles in Excel
=QUARTILE.INC(range, q) →if you want quartile 1, q = 1
For the inter-quartile range
=QUARTILE.INC(range, 3) - =QUARTILE.INC(range, 1)
To find the mean, mode, median, standard deviation, variance, range, min, max & sum
In Excel data analysis → descriptive statistics → input and select data → tick labels in first row →
output range click on a blank cell → tick summary statistics → ok
Statistical measure of association / relationship
To find the strength of the relationship, use pearson’s product moment correlation coefficient
Correlation in Excel
=CORREL(range 1, range 2)
A basic-level test , which will include simple data input, basic calculations and
knowing how to create a drop-down list within a worksheet;
An intermediate-level test , which will introduce questions about functions and
specific formatting for a spreadsheet;
An advanced-level test , which requires intricate knowledge of pivot tables,
conditional formatting and charts.
Combinations = finds the number of outcomes if we select r objects from a set N objects
Combinations in Excel =FACT(10)/FACT(3)*FACT(7)) or =COMBIN(10, 3)
Permutations = finds number of outcomes if we select r objects from a set N objects, but the order
of selection is important
Permutations in Excel =PERMUT(8,3)
Calculating probabilities for a normal distribution
Probability for a normal distribution in Excel =NORM.DIST(a, b, c, d)
a= value you wish to calculate
b= mean of the distribution
c= standard deviation of the distribution
d= 1 for cumulative probability, 0 for the height of curve
Calculative percentiles for Normal Distribution
Percentiles for Normal Distribution in Excel
=NORM.INV(a, b, c)
a= cumulative probability for which you require the percentile
b=mean
c=standard deviation
5th percentile = 0.05 for a → IF YOU WANT TOP 5% IT IS =1-NORM.INV(0.05, b, c)
Standardizing Normal Values
If X is a normal random variable → X~N(μ, σ)
Standard normal /Z = normal distribution with mean 0 and standard deviation 1 → Z~N(0, 1)
Transform X to Z scale by
x −μ
Z=
σ
Standardizing in Excel
=STANDARDIZE(x, mean, standard deviation)
To be able to drag down the excel sheet, =STANDARDIZE(x, mean $A$1, standard deviation $B$2)
Point estimate of the population standard deviation in Excel
=STDEV.S(range of numbers)
Xi = total sum of sample
X bar = average of x
2. Sampling Distribution of the sample mean X̅
- The problem: the sample mean x bar may over or under estimate the population mean μ
- Sampling error = X̅ - μ
This test statistic has a t distribution with n-1 degrees of freedom
Where μ0 = borderline value between null and alternative hypotheses (hypothesized mean value)
We use
=T.DIST
Function to find the p-value
Finding the p-value in excel for hypothesis test for a population mean
= T.DIST( t, df, 1 ) if we want left tail
= T.DIST.RT( t, df ) if we want right tail
= T.DIST.2T( t, df ) if we want two tail
Rejection rule: p-value approach
Reject H0 if p-value ≤ α significance level
Finding the p-value in Excel for a test for a proportion
P value in Excel
One-tailed, lower tail: =norm.s.dist
One-tailed, upper tail: =1-norm.s.dist
Two-tailed: =2*(1-norm.s.dist)
