Average of cells in a range
5 2 4 6 8
Average of individual cells
4 2 4 6
Average of cells contained in two different ranges
3.5 1 2 3 4 5
Average of cells in a range including blank (ignored) cells
4 0 4 8
Average using values instead of cells
5
6
Use of BINOM.DIST and BINOM.INV for example with 5 trials, each with a 50% chance of success
Trials 5
Probability 0.5
Binomial Binomial
Distribution Distribution
Criterion Successful Probability Probability
Value Trials TRUE FALSE
0.01 0 0.031 0.031
0.1 1 0.188 0.156
0.25 2 0.500 0.313
0.49 2 0.500 0.313
0.51 3 0.813 0.313
0.7 3 0.813 0.313
0.9 4 0.969 0.156
0.98 5 1.000 0.031
ance of success
Examples of the correlation coefficient of two sets of values (two arrays)
1 1 2 3 4 2
-1 1 2 3 4 4
0 1 2 3 4 2
0.6742 1 2 3 4 1
3 4 5
3 2 1
4 1 3
2 1 3
Count of the number of cells less than zero and number of cells equal to 5
-1 2 5 -4 10 5 3 -3
Less than 0 using "<0" 3
Equal to 5 using 5 2
Examples of population covariance and sample covariance between two sets of values (two arrays)
Covariance.P Ave X Ave Y Calculated Covariance.S Ave X
4.800 3.2 6 4.800 6.000 3.2
-4.800 3.2 6 -4.800 -6.000 3.2
6.040 3.2 5.8 6.040 7.550 3.2
es (two arrays)
Ave Y Calculated X Array Y Array
6 6.000 1 2 3 4 6 2
6 -6.000 1 2 3 4 6 10
5.8 7.550 1 2 3 4 6 1
4 6 8 10
8 6 4 2
3 6 8 11
Example using Goal Seek to determine IRR.
Discount
Year Return Factor
0 (420) 1.0000
1 115 0.9091
2 115 0.8264
3 115 0.7513
4 115 0.6830
Discount Rate 10.00%
Net Present Value (55.47)
1. On the Data tab/menu, select the "What-If Analysis" button.
2. Select Goal Seek.
3. In the "Set cell" box, enter the reference for the cell that contains the formula.
(In this case, the formula cell is E14)
4. In the "To value" box, enter the result you want for the formula selected in the "Se
(In this case, enter 0 since IRR is the discount rate that results in NPV of 0)
5. In the "By changing cell" section, select the reference for the cell that contains the
(In this case, enter E12)
6. Click "OK".
(The result should be 3.74%.)
that contains the formula.
he formula selected in the "Set cell" box.
t results in NPV of 0)
e for the cell that contains the value that you want to change.
Multiplication tables of two arrays, one vertical and one horizontal
Must hit CTRL/SHIFT/ENTER when entering the MMULT function
1 2 3 1 1 2
2 4 6 2
3 6 9 3
5 4 3 2 1 1
10 8 6 4 2 2
15 12 9 6 3 3
20 16 12 8 4 4
25 20 15 10 5 5
Multiplication table of two matrixes (two ranges)
7 10 1 2 1 2
15 22 3 4 3 4
Calculated
7 10
15 22
3
5 4 3 2 1
Examples of the normal distribution and inverse normal distribution functions
Standard
1 FALSE x Mean Deviation
NORM.DIST 0.500 0.080 10 10 5
0.841 0.048 15 10 5
0.977 0.011 20 10 5
0.691 0.352 0.5 0 1
NORM.INV 10.0 0.5 10 5
15.0 0.84 10 5
19.8 0.975 10 5
1.0 0.84 0 1
NORM.S.DIST 0.691 0.352 0.5
NORM.S.INV 1.0 0.84
Note
Value is equal to the mean
Value is equal to the mean plus one standard deviation
Value is equal to the mean plus two standard deviations
Example where the Mean is 0 and Standard Deviation is 1
Inverse producing value equal to the mean
Inverse producing value approximately equal to the mean plus one standard deviation
Inverse producing value approximately equal to the mean plus two standard deviations
Example where the Mean is 0 and Standard Deviation is 1
Matches the example above where the Mean is 0 and Standard Deviation is 1
Matches the example above where the Mean is 0 and Standard Deviation is 1
50th percentile of cells in a range
1 3 5 7 9 2 4 6
0.5 5
95th percentile of cells in a range with interpolation
1 3 5 7 9 2 4 6
0.95 8.60
50th percentile of cells in a range with three rows and five columns
1 3 5 7 9
2 4 6 13 15
11 12 14 8 10
0.5 8
8
8
Examples of finding the rank of a value in a range
In a range of 1 to 9, the value of 6 is ranked 6th in ascending order and ranked 4th in descending order
1 3 5 7 9 2 4
Ascending Descending
6 6 4
In a range below, the value of 1.5 is ranked 3rd in ascending order and ranked 7th in descending order
0.5 1.5 2.5 3.5 4.5 1 2
Ascending Descending
1.5 3 7
In a range below with three rows and five columns, the value of 10 is ranked 10th in ascending order and
1 3 5 7 9
2 4 6 13 15
11 12 14 8 10
Ascending Descending
10 10 6
ked 4th in descending order
6 8
ked 7th in descending order
4 6
ed 10th in ascending order and ranked 6th in descending order
Examples of finding the x smallest value in a range
In a range below, the 6th smallest value is 5.75
1 3 5 7 9 2 4
6 5.75
In a range below, the 3rd smallest value is 1.5
0.5 1.5 2.5 3.5 4.5 1 2
3 1.5
Using "1" in the range below, the smallest value is 0.5
0.5 1.5 2.5 3.5 4.5 1 2
1 0.5
5.75 8
4 6
4 6
Examples of the standard deviation functions
Population
STDEV.P
Standard Deviation of cells in a range 2.236
Standard Deviation of individual cells 1.633
Standard Deviation of cells contained in two different ranges 1.708
Standard Deviation of cells in a range including blank (ignored) cells 1.633
Standard Deviation using values instead of cells 2.236
Sample
Average Calculated STDEV.S Average Calculated
5 2.236 2.582 5 2.582 2 4 6
4 1.633 2.000 4 2.000 2 4 6
3.5 1.708 1.871 3.5 1.871 1 2 3
2.000 2 4
2.582
8
4 5 6
6
Sum of cells in a range
20 2 4 6 8
Sum of individual cells
12 2 4 6
Sum of cells contained in two different ranges
21 1 2 3 4 5
Sum of cells in a range including blank (ignored) cells
12 0 4 8
Sum using values instead of cells
20
6
Examples of the SUMPRODUCT and TRANSPOSE functions
Sumproduct with vertical arrays
Sumproduct with horizontal arrays
Sumproduct with mixed arrays (using TRANSPOSE)
Must hit CTRL/SHIFT/ENTER when entering the TRANSPOSE function
TRANSPOSE without Sumproduct
Must highlight full result range and hit CTRL/SHIFT/ENTER
27 2 5
3 3
4 2
27 2 3 4 5 3 2
27 2 5 3 2
3
4
2 2 3 4
3
4
Vertical lookup examples using an exact match (logical value = FALSE)
3 35 2 40
4 38
22 42 3 35
22 42
8 33
Vertical lookup examples using an approximate (next lowest) match (logical value = TRUE)
1.7 40 1 20 20 40
2 20 18 38
3.3 35 3 20 15 35
4 20 22 42
5 18 15 33
