# analysis methods excel by k01nsY8

VIEWS: 9 PAGES: 20

• pg 1
```									                                                      Created by Robert L. Andrews
Ways of performing analyses in Excel
Computations performed by Mathematical Formulas - Formula bar - Updated Automatically
Excel Functions - Function Button or Insert Menu - Updated Automatically
Pivot Table - Data Menu - Updated by refreshing
Subtotals - Data Menu - Updated Automatically
Data Analysis - Tools Menu - Fixed output with no update
Goal Seek - Tools Menu - Dependent cell value is formula or function based
Solver - Tools Menu - Dependent cell value is formula or function based
ert L. Andrews

ed Automatically
Analyzing data for a single variable
Created by Robert L. Andrews
Qualitative measurement - labels that categorize according to some quality
The measurements may be recorded as numbers or as text.
Statistics: Frequency, proportion, percent

Quantitative measurement - numerical values that measure some quantity
The measurements must be recorded as numbers.
Statistics:
Frequency distributions
Measures of the middle:
average(mean), weighted average
median
mode
Measures of the variation:
range = Max - Min
average deviation or mean absolute deviation
variance and its square root the standard deviation
Analyzing relationships between two variables
Created by Robert L. Andrews
2 Qualitative measurements
Cross-tabulation or cross-classification table
Tables with Frequency, proportion, percent
Obtained from a list by a PivotTable

1 Qualitative measurement and 1 Quantitative measurement
Table of Quantitative Statistics by Qualitative measurement:
Frequency distributions
Measures of the middle: average(mean), median, mode
Measures of the variation: range, average deviation, variance and its square root the standard deviation

2 Quantitative measurements
Statistics:
Correlation
Regression analysis
ert L. Andrews

e standard deviation
List of Excel functions most used for analysis            Created   by Robert L. Andrews
ABS(number)
AND(logical1,logical2, ...)
AVEDEV(number1,number2, ...)
AVERAGE(number1,number2, ...)
CHIDIST(x,degrees_freedom)
CHIINV(probability,degrees_freedom)
CHITEST(actual_range,expected_range)
CORREL(array1,array2)
COUNT(value1,value2, ...)
COUNTA(value1,value2, ...)
COUNTBLANK(range)
COUNTIF(range,criteria)
DAY(serial_number)
DEVSQ(number1,number2,...)
EFFECT(nominal_rate,npery)
EXP(number)
FDIST(x,degrees_freedom1,degrees_freedom2)
FINV(probability,degrees_freedom1,degrees_freedom2)
FIXED(number,decimals,no_commas)
FORECAST(x,known_y's,known_x's)
FREQUENCY(data_array,bins_array)
FTEST(array1,array2)
FV(rate,nper,pmt,pv,type)
GROWTH(known_y's,known_x's,new_x's,const)
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
HOUR(serial_number)
IF(logical_test,value_if_true,value_if_false)
INT(number)
INTERCEPT(known_y's,known_x's)
LARGE(array,k)
LN(number)
LOG(number,base)
LOG10(number)
MAX(number1,number2,...)
MEDIAN(number1,number2, ...)
MIN(number1,number2, ...)
MINUTE(serial_number)
MODE(number1,number2, ...) - Not recommended
MONTH(serial_number)
NORMDIST(x,mean,standard_dev,cumulative)
NORMINV(probability,mean,standard_dev)
NOW( )
NPER(rate, pmt, pv, fv, type)
OR(logical1,logical2,...)
PEARSON(array1,array2)
PERCENTILE(array,k)
PMT(rate,nper,pv,fv,type)
PV(rate,nper,pmt,fv,type)
QUARTILE(array,quart)
RANDBETWEEN(bottom,top)
RATE(nper,pmt,pv,fv,type,guess)
ROUND(number,num_digits)
ROUNDDOWN(number,num_digits)
ROUNDUP(number,num_digits)
RSQ(known_y's,known_x's)
SECOND(serial_number)
SLOPE(known_y's,known_x's)
SMALL(array,k)
SQRT(number)
STDEV(number1,number2,...)
STDEVP(number1,number2,...)
STEYX(known_y's,known_x's)
SUM(number1,number2, ...)
SUMIF(range,criteria,sum_range)
SUMPRODUCT(array1,array2,array3, ...)
TDIST(x,degrees_freedom,tails)
TINV(probability,degrees_freedom)
TODAY( )
TRANSPOSE(array)
TRIMMEAN(array,percent)
TRUNC(number,num_digits)
TTEST(array1,array2,tails,type)
VAR(number1,number2,...)
VARP(number1,number2,...)
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
YEAR(serial_number)
ert L. Andrews
Finance functions                             Created by Robert L. Andrews
EFFECT(nominal_rate,npery) this is known as APY or Annual Percentage Yield
Also
npery = number of compounding periods in a year
nominal_rate = Annual rate (This rate is divided by npery to get RATE per compounding period.)

FV(rate,nper,pmt,pv,type)
NPER(rate, pmt, pv, fv, type)
PMT(rate,nper,pv,fv,type)
PV(rate,nper,pmt,fv,type)
RATE(nper,pmt,pv,fv,type,guess)
FV = Future Value
NPER = Number of Compounding Periods
PMT = Payment Amount
PV = Present Value
RATE = Rate per compounding period = nominal_rate / npery
TYPE = 0 for payments made at the end of the month
TYPE = 1 for payments at the beginning of the month
pounding period.)
Created by Robert L. Andrews
Measures of relative standing                     Totals
FREQUENCY(data_array,bins_array)                  COUNT(value1,value2, ...)
LARGE(array,k)                                    SUM(number1,number2, ...)
MAX(number1,number2,...)                          SUMPRODUCT(array1,array2,array3, ...)
MIN(number1,number2, ...)                         DEVSQ(number1,number2,...)
PERCENTILE(array,k)
QUARTILE(array,quart)                             Measures of variation
SMALL(array,k)                                    AVEDEV(number1,number2, ...)
STDEV(number1,number2,...)
Location of Middle                                VAR(number1,number2,...)
AVERAGE(number1,number2, ...)                     STDEVP(number1,number2,...)
MEDIAN(number1,number2, ...)                      VARP(number1,number2,...)
MODE(number1,number2, ...) - Not recommended      range = MAX - MIN
TRIMMEAN(array,percent)
weighted_average = SUMPRODUCT(observations,corresponding_weights) / SUM(weights)
d by Robert L. Andrews
Relationships between 2 quantitative variables                   Created by Robert L. Andrews
CORREL(array1,array2)
FORECAST(x,known_y's,known_x's)                  Testing of hypothesized null
INTERCEPT(known_y's,known_x's)                   CHITEST(actual_range,expected_range)
PEARSON(array1,array2)                           FTEST(array1,array2)
RSQ(known_y's,known_x's)                         TTEST(array1,array2,tails,type)
SLOPE(known_y's,known_x's)                           type = 1 for paired t-test
STEYX(known_y's,known_x's)                           type = 2 for pooled t-test
type = 3 for unequal variance t-test
Probability distribution values
CHIDIST(x,degrees_freedom)
CHIINV(probability,degrees_freedom)
FDIST(x,degrees_freedom1,degrees_freedom2)
FINV(probability,degrees_freedom1,degrees_freedom2)
NORMDIST(x,mean,standard_dev,cumulative)
NORMINV(probability,mean,standard_dev)
TDIST(x,degrees_freedom,tails)
TINV(probability,degrees_freedom)
ert L. Andrews
Functions on a number                   Count functions                     Created by Robert L. And
ABS(number)                             COUNT(value1,value2, ...)
EXP(number)                             COUNTA(value1,value2, ...)
FIXED(number,decimals,no_commas)        COUNTBLANK(range)
INT(number) - rounds up to integer      COUNTIF(range,criteria)
LN(number)
LOG(number,base)                        Time & Date functions
LOG10(number)                           NOW( )   DAY(serial_number) MONTH(serial_number)
ROUND(number,num_digits)                TODAY( ) YEAR(serial_number) HOUR(serial_number)
ROUNDDOWN(number,num_digits)            SECOND(serial_number)        MINUTE(serial_number)
ROUNDUP(number,num_digits)
SQRT(number)                            Lookup functions
TRUNC(number,num_digits)                HLOOKUP(lookup_value,table_array,row_index_num,range_look
VLOOKUP(lookup_value,table_array,col_index_num,range_looku
Array functions
FREQUENCY(data_array,bins_array)        Random numbers
TRANSPOSE(array)                        RAND( )    RANDBETWEEN(bottom,top)

Combination functions                   Logical functions
COUNTIF(range,criteria)                 AND(logical1,logical2, ...)
SUMIF(range,criteria,sum_range)         OR(logical1,logical2,...)
SUMPRODUCT(array1,array2,array3, ...)   IF(logical_test,value_if_true,value_if_false)
Created by Robert L. Andrews

MONTH(serial_number)
HOUR(serial_number)
MINUTE(serial_number)

array,row_index_num,range_lookup)
array,col_index_num,range_lookup)
Determining Frequencies or Counts                 Created by Robert L. Andrews

Qualitative measurement
Functions
COUNT(value1,value2, ...)
COUNTA(value1,value2, ...)
COUNTBLANK(range)
COUNTIF(range,criteria)

Pivot Table

Subtotal (count)

Quantitative measurement
Functions
FREQUENCY(data_array,bins_array)

Need to define the bins to get the desired categories for the
frequency table for either the frequency function or Histogram.
Data Analysis
Histogram
Calculating Statistics for a single variable
Quantitative measurements                    Created by Robert L. Andrews
Pivot Table            Count     Average
Min       Max
Sum
Subtotal                StdDev     StdDevp
Var       Varp
Functions
AVERAGE(number1,number2, ...)
MEDIAN(number1,number2, ...)
MODE(number1,number2, ...) - Not recommended
TRIMMEAN(array,percent)
QUARTILE(array,quart)
range = MAX - MIN
AVEDEV(number1,number2, ...)
STDEV(number1,number2,...)
VAR(number1,number2,...)
STDEVP(number1,number2,...)
VARP(number1,number2,...)

Data Analysis
Descriptive Statistics -
Check Summary statistics box
Calculating Statistics for two variables         Created by Robert L. Andrews
1 Qualitative measurement and 1 Quantitative measurement

Subtotal

(For the procedures below arrange the data in columns with one column for each qualitative category)
Functions (Use 1 variable statistics on data in the columns)

Data Analysis
Descriptive Statistics - Check Summary statistics box
F-test Two Sample for Variances
t-test Paired Two Sample for Means
t-test Two-Sample Assuming Equal Variances
t-test Two-Sample Assuming Unequal Variances
z-test Two Sample for Means
ANOVA: Single Factor

Correlation
Calculating Statistics for two variables                        Created by Robert L. Andrews

2 Quantitative measurements
Functions
CORREL(array1,array2)
FORECAST(x,known_y's,known_x's)
INTERCEPT(known_y's,known_x's)
PEARSON(array1,array2)
RSQ(known_y's,known_x's)
SLOPE(known_y's,known_x's)
STEYX(known_y's,known_x's)

Data Analysis
Correlation
Regression

Chart Wizard
Add Trendline to XY Scatter with Options
Right Mouse Click on data point in the Scatter diagram

```
To top