analysis methods excel by k01nsY8

VIEWS: 9 PAGES: 20

									                                                      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