Excel Master Series Blog

Use the Excel t Test To Find Out What the Best Days To Sell Are

2010-08-22T11:33:00.000-07:00

The t Test in Excel

Can Determine What Your

Best Sales Days Are

It's always great to know what day of the week you can expect to get peak sales. The t test in Excel can provide that information for you. It's quite a simple test to run, as you will see. This blog article will walk you step-by-step through a t Test in Excel. The t Test compares two groups of samples and determines whether the mean of one sample is different than the other. Most types of t Tests require that each sample group have the same number of samples and also have the same variance. This Excel t Test has neither of those requirements.

Here is the scenario we are going to test: Suppose that you have been monitoring your daily sales for about a year. Your two best sales during the week are normally Monday and Wednesday. You would like to know which of those two days really does produce the best sales. You've tracked Monday sales for the last 40 weeks and Wednesday sales for the last 42 weeks. Mean sales for Wednesday is a bit higher than mean sales for Monday, but you would like to know with 95% certainty whether the difference in means is not just by chance and that Wednesday really is a better day for selling.

You can run your data through an Excel t Test and know within a minute whether Wednesday really is the best sales day. Excel has several built-in t Tests. The specific Excel t Test we will use is called the "t Test: Two Sample Assuming Unequal Variances." This t Test allows for two samples that have unequal sizes and variances. The only requirement is that both samples are Normally distributed. This will be discussed shortly. In Excel 2003, this test can be accessed through this menu path: Tools / Data Analysis / t Test: Two Sample Assuming Unequal Variances. Before we perform this t Test, we need to have a discussion of what the t Test is.

t Test - General Description

This test will tell you whether the difference between the before and after numbers is genuine or whether this difference could merely have been the result of chance. Overall a t-test compares two means and determines within a specified degree of certainty whether the two means really are different, or whether the difference might have occurred by chance.

t Test for Two Samples Having
Unequal Sizes and Variances

The t Test that can be applied to two samples with unequal sizes and unequal variances determines whether the means of both samples are the same. In other words, this test evaluates within a specified degree of certainty whether the measured difference between the meaqns is real or could have occurred merely by chance.

Before we start discussing this specific test in detail, The t-test needs to be generally explained. The basic question to be answered is:

The t Test - What Is It?

The t test is a statistics test generally used to test whether means of populations are different. In the t test, a t value is calculated based upon the difference in the means and variances of the two populations. The greater the t value, the more certain it is that the means are different.

The t value can be generally described as follows:

t value = (Difference between the group means) / (Variability of the groups)

There are many variations of the t test. Each has its own specific formula for calculating a t value for the sampled data sets. All of the t value formulas can be described by the above formula.

The Higher the t Value - The More Likely the Groups Are Different

The higher the t value is, the more likely it is that the two means are different. If the two groups being compared have a high degree of variance (t value has a high denominator), it is much harder to tell them apart. On the other hand, if the two groups being compared have a low degree of variance (the t value has a low denominator), it is much easier to tell the two groups apart.

The Lower the Combined Variance, the Higher the t Value

The illustrations below should clarify how the degree of variance in the two groups determines how easy or difficult it is to state that the means of the two groups are really different. The t test quantifies this relationship and provides a way to determine whether the measured difference between two means can be considered real or not based upon the amount of variance in both groups. Here are illustrations that should clarify this relationship.

We can see that pair of data sets on the right are much easier to differentiate because they have much less overlap than the pair of data sets on the right. The overlap represents the overall variability between the two data sets in each pair. The higher the total variablility within the pair of data sets, the higher will be the denominator in the t value formula. The higher the denominator, the lower the t value for the pair of data sets. The lower the t value, the less likely it is that the two data sets are separate data sets with different means.

T-Test for Two Samples Having Unequal Sizes and Variances

The t Test for comparing two samples with unequal sizes and variance is a variation of the t Test called Welch's t Test. It is not the classic Student's t Test, which does not allow for samples having unqual variances.

We are going to use this t test to determine within 95% certainty whether the means sales from Wednesdays is different than the mean sales from Monday. We have measured from the last 42 Wednesdays and the last 40 Mondays and we will apply this Excel t test to determine whether the measured difference between the means is real or not.

A Little Bit More About This t Test

The t Test in general is a special case of one-way (sometimes called “single factor”) ANOVA. This paired two-sample student’s t test is applied when there is a natural pairing of samples. It is most often used to determine whether “before” and “after” means of a sample of the same objects have changed during an experiment. One really great thing about this t test is that the paired two-sample t test does not require that the variances of both populations to be the same.

Here is the formula to calculate the t value for a two-sample t test of unequal variances if you are testing to determine whether there difference between the two samples:

t value = [ X1 - X2 ] / [ SQRT( (s1^2 / n1) + (s2^2/n2) ) ]

Degree of Freedom = df =

[ [ (s1^2 / n1) + (s2^2 / n2) ]^2 ] / [ ( [ (s1^2 / n1)^2 ] / [n1 -1 ] ) + ( [ (s2^2 / n2)^2 ] / [n2 -1 ] ) ]

X1 and X2 are the sample means. s1 and s2 are the sample standard deviations, and n1 and n2 are sample sizes.

You can see that this follows the general formula for calculating the t value in a t test, which is:

t value = (Difference between the group means) / (Variability of the groups)

The t value is a specific point on the x-axis in the t distribution (student’s t distribution). If this t value falls outside the region of required certainty, it can be stated that the two means are probably different. If this t value falls within the region of required certainty, it cannot be stated that the two means are probably different.

The required region of certainty depends upon the degree of certainty required in the test. If 95% certainty is required, then the required region of certainty consists of 95% of the area under the student’s t distribution. The outer 5% is the region of uncertainty. This is also referred to as α (alpha) or the degree of significance. If the t value is large enough to be located all the way out on the x-axis in the 5% region of uncertainty, it can be stated within 95% certainty that the two means are different.

A t test can be a one-tailed test or a two-tailed test. A one-tailed test determines whether the means are different in one specific direction. For example, a one-tailed test could be used to determine only if the mean of the “after” measurements is greater than the mean of the “before” measurements. A two-tailed test determines whether the two means are merely different.

Two-Tailed t Test Is More Stringent

The two-tailed test is more stringent because the area in the outer tails outside of the region of required degree of certainty is split into two tails. For example, if the required degree of certainty is 95% on a two-tailed test, the calculated t value must be all the way out in the outer 2.5% of either tail for the t test to conclude within 95% certainty that the means are different.

One-Tailed t Test Is Less Stringent

A one-tailed test is less stringent. If the required degree of certainty is 95% on a one-tailed test, the calculated t value only has to be within the outer 5% of whatever tail is being tested to be able to state the two means are probably different.

Doing The Two-Sample t Test for Unequal Variances in Excel

We are testing to determine whether there really is a difference between mean sales on Monday and mean sales on Wednesday.

The data need to be arranged in Excel as follows:

Click on Image To See Enlarged View

The t Test we are about to use allows for different sample sizes and different variances, but that standard requirement for all t Tests is that both samples being compared are Normally distributed. There are a number of different ways of doing this. For brevity, we are going to do it the simplest possible way. We will make an Excel histogram of each sample's data and simple eyeball the shape of the histogram. If the shape of each histogram resembles the Normal curve, we will go with it. There are a number of better ways of checking for Normalty and here is a link to an article in this blog which describes how to do a simple but more accurate Excel Normality test called the Normal Probability Plot.

The Excel histogram is a simple thing to construct. If you haven't ever done one, here is a link to an article in this blog which shows how to create a histogram in Excel from sample data.
Completed histograms for each of the two samples are as follows:

Both histograms appear to be Normally distributed so we can use t Test to compare the two samples. If either sample is not Normally distributed, the t test cannot be used because the output is likely to be totally incorrect. If either sample is not Normally distributed, we must use a nonparametric test such as the Mann-Whitney U Test to compare the samples. Here is a link to an article in this blog which shows exactly how to do the Mann-Whiteney U Test and several other nonparametric tests in Excel.

Before we run the t Test, we would like to take a look at a description of of each sample. In Exzcel 2003, this can be quickly done by the following tool: Tools / Data Analysis / Descriptive Statistics. The Descriptive Statistics for each sample are as follows:

Now, access this Excel t Test as follows (this is Excel 2003):

Tools / Data Analysis / t-Test: Two Sample Assuming Unequal Variances

This following dialogue box will appear:

Click on Image To See Enlarged View

Input the data as followings:

Variable 1 Range: Select everything that is highlighted yellow, including the label “Monday Sales.”

Variable 2 Range: Select everything that is highlighted tan, including the label “Wednesday Sales.”

Hypothesized Mean Difference: 0

Labels: Check the box because you included the labels for Variables 1 and 2.

Alpha: This depends on your desired degree of certainty. 0.05, if you desired 95% certainty. 0.20 if you desire 80% certainty.

Output Range: Select the cell that you want the upper left corner of the output to appear in.

Hit “OK” to run the analysis and the following Excel output appears:

Click on Image To See Enlarged View

This output can be interpreted as follows:

The t value is -6.088.

α = 0.05 = 1 - Required Degree of Certainty = 1 - 95%

p Value (1-Tailed) = 1.88E-08

p Value (2-Tailed) = 3.77E-08

One-tailed Test

This t value has a greater absolute value (6.088) than the critical t value for a one-tailed test (1.664). We can therefore state with 95% certainty that there really is a difference between Wednesday sales and Monday sales.

The above conclusion can also be reached because the p Value for the one-tailed test (highlighted in light red on the Excel output) is 1.88E-08. This is much less than alpha (0.05). The p Value being less than alpha is an equivalent result to the t value being greater than the t critical value.

Two-Tailed Test

The same result is arrived at for the two-tailed test. The two-tailed test is more stringent because the alpha region of uncertainty (5% of the area under the student’s t distribution curve) is now divided between both outer tails. The t value needs to be larger for the two-tailed test to wind up in the outer 2.5% area of either outer tail.

In this case, the t value was large enough to be positioned in the outer 2.5% of either outer tail. The absolute value of t value (6.088) is much larger than the critical t value for the two-tailed test (1.990). This indicates that it cannot be stated with 95% certainty that there has been a change in the mean from before to after.

The p value calculated for the two-tailed test (3.77E-08) is much smaller than alpha (0.05). This is an equivalent result to the above.

Hand Calculation of the t Value and p Value

Let’s calculate the t value and p values for the one and two-tailed tests by hand to make sure that Excel has done a correct job. The t value is stated as the t statistic.

Here is the original test data Excel Descriptive Statistics:

Click on Images To See Enlarged View

Here is the hand calculation of the t value and p values for the one and two-tailed tests for this Two-Sample t Test Assuming Unequal Variance. The hand calculations below of the t Value and p Values agree with the Excel outputs. There are very slight differences due to rounding differences:

t value = [ X1 - X2 ] / [ SQRT( (s1^2 / n1) + (s2^2/n2) ) ]

Degree of Freedom = df =

[ [ (s1^2 / n1) + (s2^2 / n2) ]^2 ] / [ ( [ (s1^2 / n1)^2 ] / [n1 -1 ] ) + ( [ (s2^2 / n2)^2 ] / [n2 -1 ] ) ]

The Degrees of Freedom calucation must be rounded to the nearest whole number, which in this case is 80.

X1 and X2 are the sample means. s1 and s2 are the sample standard deviations, and n1 and n2 are sample sizes.

p Value = TDIST ( T Statistic, df, Number of Tails )

Here are the actual calculations done by hand in Excel:

Click on Image To See Enlarged View

The Two Sample t Test Assuming Unequal Variances.is a very simple test to run in Excel and can be applied to nearly any aspect of your marketing program to see if one group of samples is different from another group of samples. One note: both sample groups must be continuous and measured using the using the same scale.

Here are other articles in this blog that might help your understanding of t Testa and equivalent nonparametric tests to be used when samples are not Normally distributed:

Nonparametric Tests - How To Do the 4 Most Popular in Excel

A Quick, Easy Normality Test For Excel

Statistical Mistakes You Don't Want To Make

How To Do ALL Hypothesis Tests in Only 4 Steps

The t Tests - How and When Should the Marketer Use Them In Excel

If you would like to create a link to this blog article, here is the link to copy for your convenience:

How To Use the Two-Sample t Test Assuming Unequal Variances To Determine When Your Best Sales Days Are

Please post any comments you have on this article. Your opinion is highly valued!

If You Like This, Then Share It...

Nonparametric Tests - Completed Examples in Excel

2010-08-09T13:37:00.000-07:00

Nonparametric Tests

Done in Excel

Nonparametric tests are incredibly useful statistical procedures, and yet not many marketers use them. I like them because they are easy shortcuts for widely-used statistical tests such as the t-test and ANOVA and they remove any worries about requirements of normal distribution of underlying variables.

Maybe the reason that nonparametric tests are not as popular as they should be is that not a whole lot of people know how to do them. They are rarely taught in statistics courses and it is commonly believed that you need high-level, expensive, complicated software such as SPSS to run these tests.

This article will show how easy it is to do the four most popular nonparametric tests in Excel. These tests are:

The Sign Test
The Mann-Whitney U Test
The Kruskal-Wallis H Test
The Spearman Correlation Coefficient Test

In future blog articles, I will also show how to perform some of the other popular nonparametric tests such as:

- The Wilcoxon Rank Sum Test
- The Wilcoxon Signed-Rank Test
- The Paired-Sample Sign Test

All nonparametric tests can easily be performed in Excel from start to finish. One of the great thing about knowing how to do these procedures in Excel is that the need to look data up on statistics tables in completely eliminated. That is, in fact, one of the main reasons that I learned to do statsitics in Excel. I used to HATE having to look data in statisics charts in thick statistics textbooks. Since mastering statistics in Excel, I've sold all of my statistics textbooks on eBay.

Below are completed examples of the first four nonparametric tests done in Excel:

The Sign Test

1) Used to determine whether a population median is equal to, less than, or greater than a specific number

2) Used to determine whether there is a real difference between pairs of data, such as before and after measurements

Procedures

Small samples - less than 25 samples

- less than 25 samples for case 1) above

- less than 25 pairs of data for case 2) above

In the case of testing whether there is a difference between before and after measurements, subtract the before measurement from the after measurement (Case 2 above).

In the case of testing to determine whether a population median is equal to, less than, or greater to a specific number, subtract the sample from the fixed number (Case 1 above).

Excel Exercise: Determine whether the Before and After Data is Different:

Here is the original Before and After Data:

1) Take the difference between each Before and After data point.

2) Record whether that difference is positive or negative.

3) Record the total number of positive and negative results.

Click On Image To See An Enlarged View

X = Lesser of (+) or (-) Numbers = 1

N = Sum of (+) and (-) Numbers = 3 + 5 = 9

p Value = -2*BINOMDIST(X, N, 0.5, 1) = 0.0390625

The p value is the total area under 2 tails in a 2-tailed test

Rule: The Result is Significant if p Value < α

If 95% certainty is required, then α = 0.05

In This Case, the Result Is Significant and We Can State That There Is a Difference Between the Before and After Numbers.

Large samples - 25 or more samples

Plug the X and N into the Following Formula

Z Score = [ (X + 0.5 ) – (N – 2) ] / [ SQRT(N) / 2 ]

Then Compare this Z Score to Z Critical for the Given α

For a one-tailed test, Z Critical = NORMSINV(α)

For a two-tailed test, Z Critical = NORMSINV(α/2)

Rule: The Result if Significant If Z Score > Z Critical

For Example:

X = 3

N = 9

Z Score = [ (X + 0.5 ) – (N – 2) ] / [ SQRT(N) / 2 ] = -2.33

If We Require a 95% Degree of Certainty, Then α = 0.05

For a one-tailed test, Z Critical = NORMSINV(α) = -1.64

For a two-tailed test, Z Critical = NORMSINV(α/2) = -1.96

In this case, we can state with at least 95% certainty that the result is significant and that there is a difference between Before and After data.

Mann-Whitney U Test

- Is used to decide if there is a difference between two groups or, equivalently, whether both groups come from the same population.

- Is a good substitute for the t test (Student’s t test)

Procedures

Determine if these two groups come from different populations

Combine all samples into 1 group

Create a mechanism to Keep Track of Each Sample's Group. In this case, each sample from the 1st group was labeled with an "A" and each sample from the 2nd group was labeled with a "B."

Rank All Samples.

Put Samples Back In Original Groups

Count the Rankings For Each Group

N1 = Count of Samples in 1st Group = 7

N2 = Count of Samples in 2nd Group = 7

Calculate the U Statistic by the following formula:

U = N1*N2 + [ ( N1 * (N1 + 1) ) / 2 ] - R1 = 28

Calculate the Mean, µU , and Standard Deviation, σU = N1 * N2 / 2 = 24.5

σU = SQRT [ ( N1 * N2 * ( N1 + N2 + 1) ) / 12 ] = 7.826

Calculate the Z Score With the Following Formula:

Z Score = ( U - µU ) / σU = 0.447

Then Compare this Z Score to Z Critical for the Given α

For a one-tailed test, Z Critical = NORMSINV(α)

For a two-tailed test, Z Critical = NORMSINV(α/2)

If We Require a 95% Degree of Certainty, Then α = 0.05

For a one-tailed test, Z Critical = NORMSINV(α) = - 1.645

For a two-tailed test, Z Critical = NORMSINV(α/2) = - 1.960

Rule: The Result if Significant If Z Score > Z Critical

In This Case, Z Score < Z Critical

So The Result Is Not Statistically Significant and We Cannot State Within 95% Certainty for Either a One-Tailed Test Or a Two-Tailed Test That There is a Difference Between The Two Groups.

Kruskal-Wallis H Test

- Is used to decide if there is a difference between more than two groups or, equivalently, whether both groups come from the same population.

- Is a good substitute for Single-Factor (One-Way) ANOVA

Procedures

Determine if these three groups come from different populations

Combine all samples into 1 group.

Create a mechanism to keep track of each sample's group. In this case, each sample in the 1st group was labeled "A," each sample in the 2nd group was labeled "B," and each sample in teh 3rd group was labeled "C."

Rank All Samples

Put samples back in original groups.

N1 = Count of Samples in 1st Group = 7

N2 = Count of Samples in 2nd Group = 7

N3 = Count of Samples in 1st Group = 7

n = Total Number of Samples = 21

k = Number of Groups = 3

df = Degrees of Freedom = k - 1 = 2

Calculate the H Statistic by the following formula:
H Statistic =

[ 12 / { n*(n + 1) } ] * [ ( R1^^2)/N1 + (R2^^2)/N2 + … + (RN^^2)/NN ] – 3*(n + 1)

H Statistic = -33.818

Test Statistic H is very nearly distributed as the Chi-Square Distribution With k - 1 Degrees of Freedom as long as the number of samples in each group Is at least 5.

Therefore the Critical Value Can Be Found By The Following Excel Formula:

H Critical = CHIINV( α, df )

If we wish to be at least 95% of whether or not the result is statistically significant, then α = 0.05

H Critical = CHIINV( α, df ) = 5.991

Rule: The Result if Significant If H Statistic > H Critical

In This Case, H Statistic > H Critical

So The Result Is Statistically Significant and We Can State Within 95% That There is Difference Between The Groups, Or, Equivalently, They Come From Different Populations.

The Spearman Rank Correlation Coeffcient Test

- Used to determine if the strength and directon of a relationship
between 2 sets of numbers.

Procedures

Determine if there is a correlation between these two sets of numbers:

Rank the data.

Calculate the difference between Ranks 1 & 2, and then square that difference.

Σ (d^^2) = 18

n = Number of Samples = 7

Spearman Rank Correlation Coefficient, rs, =

rs = 1 – [ 6*Σ(d^^2) ] / [n*(n^^2 – 1)] = 0.6786

Rule: rs is statistically significant if the following p Value is less than α:

p Value = FDIST [ ( df * rs^^2) / ( 1 - rs^^2), 1, df ]

df = degrees of freedom = n - 2 = 5

p Value = 0.001635228

If We Require a 95% Degree of Certainty, Then α = 0.05

In This Case, We Can Be At Least 95% Certain That There Is a Relationship Between The Variables

Here Is a Quick Way To Assign Ranking To Samples In the Following Data Set

Assign the Count to Each Sample

Sort Both Columns By the Original Data Set and Assign a Numerical Rank.

Sort All 3 Columns By the Middle Column

Replace Middle Column With Left Column

Here are other articles in this blog that might help your understanding of nonparametric tests:

When Should the Marketer Use Nonparametric Tests

Nonparametric Tests - How To Do the 4 Most Important Ones in Excel

A Quick, Easy Normality Test For Excel

If you would like to create a link to this blog article, here is the link to copy for your convenience:

Nonparametric Tests - Completed Examples in Excel

Please post any comments you have on this article. Your opinion is highly valued!

If You Like This, Then Share It...

Nonparametric Tests - How To Do The 4 Most Important Ones in Excel

2010-08-05T15:30:00.000-07:00

Nonparametric Tests

The Basics in Excel

Often the marketer will need to use a nonparametric test in place of more well-known statistical procedures because of doubts about the distribution of variables being tested. Nonparametric tests are not taught in most statistics classes. Information about how to do them is not as readily available as is, for example, how to perform a t test or ANOVA.
This article will provide instructions on how to use Excel to perform the 5 most widely-used nonparametric tests. These include:

- The Sign Test
- The Mann-Whitney U Test
- The Kruskal-Wallis H Test
- The Spearman Correlation Coefficient Test

The Sign Test

The Sign Test is used to test a hypothesis about a population median. It is sometimes used in place of a one-sample t test when the assumption of normality in the underlying population cannot be verified or it known that the data is highly skewed. The one-sample t test is used to verify as assumption the mean of a population. If the one-sample t test cannot be used to test an assumption about a population mean, then the Sign Test can safely be substituted to perform a similar test about the population median.

For example, a marketing manager might use the Sign Test to determine within 95% certainty whether the median daily sales for one product on the web site exceeds $800.

The Sign Test is also used to test paired data, such as before and after data, to determine if there is a difference. In this case, the Sign Test can be safely substituted for the paired-sample t test if there are doubts about the underlying distributions of the variables. The Sign Test is used to test differences in the median of before and after measurements while the paired-sample t test is used to test differences in the mean in the before and after measurements. The Null Hypothesis in this use of the Sign Test is that there is no difference between median before and after measurements.

The marketing manager can safely use the Sign Test in nearly any instance that the paired-sample t test would have been used if the variable were normally distributed. Examples of this would be testing whether a new advertising campaign raised sales across sales territories or whether a new training program increased daily sales.

How The Sign Test Works

Evaluating an Assumption About a Population Median

The Sign Test is used here to evaluate whether a population median is equal to, greater than, or less than a specific number. The Sign Test takes the difference between sample and the number being evaluated and then records whether that difference is positive or negative. The total number of positive and negative signs is added up from all samples.

The net positive or net negative result is then checked for statistical significance. If the result is found to be statistically significant, the Null Hypothesis, which states that there is no difference the measured median and the assumed median, is rejected. It can then be stated within the required degree of certainty that the actual population median is different than the assumed median.

Evaluating Whether There Is a Difference Between the Median Before and After Measurements in Paired Data Samples

The Sign Test takes the difference between each paired sample of before and after measurements and then records whether that difference is positive or negative. The total number of positive and negative signs is added up from all samples. This will produce eaither a net positive or net negative result.

The net positive or net negative result is then checked for statistical significance. If the result is found to be statistically significant, the Null Hypothesis which states that there is no difference between median before and after measurements is rejected. It can then be stated within the required degree of certainty that there is a difference in median before or after measurements.

After obtaining the net positive or negative results, we need to evaluate that result for statistical significance. The method used to check for statistical significance depends upon the sample size. Small samples (less than or equal to 25) will compare their binomial distribution p Value with α to determine whether the result is statistically significant. Large samples will have a Z Score calculated and then compared with the Z Critical Value for the required degree of certainty. These methods are done in Excel as follows:

Small Samples (sample size of 25 or less)

We will be solving this by using the binomial distribution. We will determine if our result is statistically significant by comparing the p value calculated from the sample with α (the degree of significance, which equals 1 minus the degree of certainty we require). If we require a 95% degree of certainty, then α = 0.05. If the p value calculated from the sample is less than 0.05, we can state within 95% certainty that the result is statistically significant. This means that we can reject the Null Hypothesis which states that there is no difference in median values.

We will be using the binomial distribution to calculate the p value. Assign X to the smaller number of net (+) or (–) signs. Assign N to the sum of the number of + signs and the number of – signs. For example, if there are 3 (+) signs and 9 (–) signs, then X = 3 and N = 12.

Use the following Excel formula to calculate the p Value calculated in Excel from the sample as follows:

p Value = 2*BINOMDIST(X, N, 0.5, 1)

The p Value equals the area outside the test statistical in both tails in a two-tailed test. If the p Value is less than α, we can state within the required degree of certainty that the result we obtained from the samples is statistically significant. In other words, we can reject the Null Hypothesis and state within the required degree of certainty that there is a difference in the medians.

Large Samples (More than 25)

We will be calculating a Z score from the sample. Depending on whether the calculated Z score is greater than the critical Z value and whether this is a one or two-tailed test, we can determine if we have a statistically significant (probably correct) result.

N = the sum of the number of both (+) and (-) signs

X = If the alternative hypothesis states that the actual median is larger than a specific number, X = the larger number of (+) or (–) signs. If the alternative hypothesis states that the median is smaller than a certain number, X = the smaller number of (+) or (–) signs.

The Z Score = [ (X - 0.5 ) – (N/2) ] / [ SQRT(N) / 2 ]

Compare this Z Score with Z Critical calculated in Excel as follows:

For a one-tailed test, Z Critical = NORMSINV(α)

For a two-tailed test, Z Critical = NORMSINV(α/2)

A one-tailed tests determines whether there is a difference in medians in one specific direction. A two-tailed test determine whether there is any difference between medians in any direction. The two-tailed test is more stringent than a one-tailed test.

If the Z Score is greater than Z Critical, we can state within the required degree of certainty that the result we obtained during the sampling was statistically significant.

The next blog article will provide an actual example of the Sign Test being performed in Excel. Here is a link to that article:

Nonparametric tests - Completed Examples in Excel

Mann-Whitney U Test

The Mann-Whitney U Test is used to decide if there is a difference between two samples, or, equivalently, whether both samples come from the same population. The Mann-Whitney U Test is a good substitute for the t test (Student’s t test) when there are doubts regarding the normality of the population distributions from which the samples were drawn.

The Mann-Whitney U Test is used to compare performances of two groups from which regular samples can be drawn. A typical example would be to compare two sales territories by recording daily sales for one month from each territory. Comparing the performance of any two groups of objects from which regular samples can be drawn is a good candidate for a Mann-Whitney U Test.

How The Mann-Whitney U Test works:

In the number of samples in each group is at least 15, we can calculate a Z Score that will indicate whether the samples are different. Ulitmately what we are trying to determine is whether the samples are different, or do they come from the same population.

All samples from both groups are combined into one group and then ranked from smallest to largest. Each ranked sample is assigned the number of its ranking. The smallest sample receives the smallest rank of 1. If two or mode samples are equal, each of these samples is assigned the means of the ranks that would otherwise be assigned.

The following figures are then calculated from the ranked samples:

N1 = number of samples in the 1st group

N2 = number of samples in the 2nd group

R1 = the sum of the ranking of all samples from the 1st group

R2 = The sum of the rankings of all samples from the 2nd group

From these figures, the U statistic is calculated:

U = N1*N2 + [ {N1*(N1 + 1)}/2] - R1

The sampling distribution of the U statistic is symmetrical and has a mean and standard deviation as follows:

meanU = µU = (N1*N2)/2

Standard DeviationU = σU = SQRT ( [N1*N2*(N1 + N2+ 1)] / 12 )

If N1 and N2 are at least equal in value to 15, then the distribution of the U statistic in nearly normal. In this case, the Z Score of this U statistic can be calculated as follows:

Z ScoreU = (U – µU) / σU

This Z Score is compared with the Critical Z Value for the required level of certainty and whether a one or two-tailed test is being performed.

A one-tailed test is used to determine whether there is a difference in just one direction. A two-tailed test determines whether there is any difference in either direction. A two-tailed test is more stringent than a one-tailed test. The Critical Z Values are calculated in Excel as follows:

α = Level of Significance = 1 – Level of Certainty

For example, if you require a 95% level of certainty, then α = 0.05

For a Two-Tailed Test

Z Criticalα = NORMSINV( 1 – α/2 )

For a One-Tailed Test

Z Criticalα = NORMSINV( 1 – α )

The Result is Statistically Significant If:

If Z ScoreU > Critical Zα , The Null Hypothesis can be rejected and we can state within a the required level of certainty that there is a difference between the two groups.

The next blog article will show an example of this test worked entirely through in Excel.

Here is a link to that article:

Nonparametric tests - Completed Examples in Excel

Kruskal-Wallis H Test

The Kruskal-Wallis H Test is a nonparametric test can be used to test whether there is a difference between two or more two groups or, equivalently, whether all groups comes from the same population. The Mann-Whitney U Test is a subset of the Kruskal-Wallis H Test.

The Kruskal-Wallis H Test is a nonparametric test that can substituted for a Single Factor (One Way) ANOVA test when there is doubt about the normality of variables.

The marketing manager would use this test for the same reasons as a single-factor ANOVA test: to determine whether multiple variations of a single factor affected a group differently. For example, this test could be used to determine three different training programs produced different results on similar groups of salespeople.

How the Kruskal-Wallis H Test Works

We will calculate a test statistic from the sample data called H statistic. This H statistic will be compared with a critical value that will be calculated from the Chi-Square distribution. The H statistic is very nearly distributed as the Chi-Square distribution with k -1 degrees of freedom as long as each group has at least 5 samples. k = number of groups being tested.

If the H statistic is greater than the critical value, we can reject the Null Hypothesis and state with the required degree of certainty that the groups are different.

All samples from all groups are combined into one group and then ranked from smallest to largest. Each ranked sample is assigned the number of its ranking. The smallest sample receives the smallest rank of 1. If two or mode samples are equal, each of these samples is assigned the means of the ranks that would otherwise be assigned.

The following figures are then calculated from the ranked samples:

N1 = number of samples in the 1st group

N2 = number of samples in the 2nd group
..
NN = number of samples in the Nth group

R1 = the sum of the ranking of all samples from the 1st group

R2 = the sum of the rankings of all samples from the 2nd group
.
.
RN = the sum of rankings of all samples from the Nth group

N = the total number of samples in all groups combined

k = number of groups

From these figures, the H statistic is calculated:

H Statistic = [ 12 / { N*(N + 1) } ] * [ R1^^2/N1 + R2^^2/N2 + … + N^^2/NN ] – 3*(N + 1)

( ^^ denotes - to the square of)

The Critical H Value is calculated with the following Excel formula:

Critical H Value = CHIINV( α, df )

df = degrees of freedom = k – 1

α = Degree of significance = 1 - Degree of certainty
For example, if 95% certainty is required, then σ = 0.05)

Once again, if the H statistic is greater than the critical value, we can reject the Null Hypothesis and state with the required degree of certainty that the groups are different.

The next blog article will run through a complete example in Excel and show just how it is done. Here is a link to that article:

Nonparametric tests - Completed Examples in Excel

Spearman Rank Correlation Coefficient Test

The Spearman rank correlation coefficient, rs, is similar to the well-known Pearson correlation coefficient in that it measures the direction and strength of a relationship between variables. The Spearman rank correlation coefficient test is a nonparametric test and does not require that variables be normally distributed like the Pearson correlation coefficient does.

The Spearman rank correlation coefficient test would be applied for the same reasons that the normally used Pearson correlation would be. The correlation coefficients in both cases indicate whether there is a relationship between two variables. Although a high correlation does now equal causality, a high correlation does indicate that there could be a strong link that should be investigated. Both types of correlation coefficients have perfect correlations at the values of +1 and -1.

A marketing manager might use correlation analysis to find out if there is a relationship between variables such as:

1) The amount purchased and time spent with a salesperson

2) The amount purchased and the number of visits to a web site

3) The amount purchase and the customer’s service rating

4) Weekly product demand and price

5) AdWords ad position and CPC

6) AdWords ad position and number of clicks

Amy time that you want to know whether a relationship exists between two variables, you can safely use the Spearman Correlation Coefficient Test and not have to worry about the underlying distributions of the variables.

The Spearman Rank Correlation Coefficient Is Calculated As Follows:

1) Rank each variable within its own set of variable

2) Take the difference between the ranks of the variables in each pair of variable. This equals d.

3) Square d. Calculate Σd2.

4) n equals the number of variable pairs.

Here is an example of how the data would be recorded. In this example, we are trying to determine if there is a relationship between the price negotiated with the customer and the amount of time that the customer spent on the web site.

Sale                     Time
Price    Rank1    Spent on Site    Rank2    diff    d^^2

$26       6             27                       6            0        0

$19       2             18                       1            1        1

$25       5              21                       2           3        9

$16       1              21.5                    3           -2       4

$24.5     4             23                       4            0       0

$24        3             24                       5           -2       4

$28        7             29                       7            7        0

Σd^^2 = 18

Calculate the Spearman Rank correlation coefficient, rs, as follows:

rs = 1 – [ 6*Σ(d^^2 ) ] / [ n*( n^^2 – 1 ) ]

You now need to test whether or not this correlation coefficient is statistically significant. Statistical significance means that you can reject the Null Hypothesis and state within the required degree of certainty (1 – α) that a correlation between the variables probably exists.

rs is statistically significant if the following p Value calculated in Excel is less than α:

p Value = FDIST [ ( df * rs^^2 ) / ( 1 - rs^^2 ), 1, df ]

df = degrees of freedom = n - 2

n = number of sample pairs

α = (alpha) Degree of Significance = 1 – Degree of Certainty.
For example, if you require 95% certainty, then α = 0.05 (1 – 95% = 0.05)

The next blog article will run through a complete example of this nonparametric test in Excel and show exactly how it is done. Here is a link to that article:

Nonparametric tests - Completed Examples in Excel

Here are other articles in this blog that might help your understanding of nonparametric tests:

When Should the Marketer Use Nonparametric Tests

Nonparametric Tests - Completed Examples In Excel

A Quick, Easy Normality Test For Excel

If you would like to create a link to this blog article, here is the link to copy for your convenience:

Nonparametric Tests - How To the Four Most Popular In Excel

Please post any comments you have on this article. Your opinion is highly valued!

If You Like This, Then Share It...

Nonparametric Tests - When Should The Marketer Use Them

2010-08-05T13:50:00.000-07:00

Nonparametric Tests

When To Use Them in Marketing

Statistical procedures are either parametric or nonparametric. Parametric statistical tests require assumptions about the population from which the samples are drawn. For example, many tests such as the t Test, Chi-Square tests, z Tests, and F tests, and many types of hypothesis tests require the underlying population to be normally distributed. Some tests require equal variances of both populations.

Sometimes these assumptions cannot be always be assumed. Examples of this would be if the population is highly skewed or if the underlying distribution or variances were entirely unknown.

Nonparametric tests have no assumptions regarding distribution of underlying populations or variance. Most of this are very easy to perform but they are not usually as precise as parametric tests and the Null Hypothesis usually requires more evidence to be rejected in a nonparametric test.

When To Use Nonparametric Tests

Nonparametric tests are often used as shortcut replacements for more complicated parametric tests. You can quite often get a quick answer that requires little calculation by running a nonparametric test.

Nonparametric tests are often used when the data is ranked but cannot be quantified. For example, how would you quantify consumer rankings such as very satisfied, moderately satisfied, just satisfied, less than satisfied, dissatisfied?

Nonparametric tests can be applied when there are a lot of outliers that might skew the results. Nonparametric tests often evaluate medians rather than means and therefore if the data have one or two outliers, the outcome of the analysis is not affected.

They come in especially handy when dealing with non-numeric data, such as having customers rank products or attributes according to preference.

Over the next several blogs, I will provide specific instructions and show examples of how to perform the most important nonparametric tests in Excel.

These nonparametric tests will include:

- The Sign Test

- The Wilcoxon Signed Rank Test

- The Mann-Whitney U Test

- The Kruskal-Wallis H Test

- The Spearman Correlation Coefficient Test

So stay tuned.

The next blog article will run through a complete example of this nonparametric test in Excel and show exactly how it is done. Here is a link to that article:

Nonparametric tests - Completed Examples in Excel

Here are other articles in this blog that might help your understanding of nonparametric tests:

Nonparametric Tests - How To Do the 4 Most Popular in Excel

A Quick, Easy Normality Test For Excel

If you would like to create a link to this blog article, here is the link to copy for your convenience:

Nonparametric Tests - When Should the Marketer Use Them?

Please post any comments you have on this article. Your opinion is highly valued!

If You Like This, Then Share It...

t Tests - How To Use the t Test in Excel To Find Out If Your New Marketing Worked

2010-08-03T13:31:00.000-07:00

The t Test

Simplified and Done in Excel

If you had recently launched a new marketing campaign, you would want to know as soon as possible whether the campaign was working. If you are able to take a large sample of before and after measurements (for example, in all of the sales territories), Excel has the perfect tool for you  a data analysis tool called the two-sample paired t-test for means. It is very simple to use and the output is straight-forward and easy to interpret.

t Test - General Description

Two-Sample, Paired t Test

The two-sample paired t-test for means evaluates whether the average difference between the before and after measurements is greater than zero or not. In other words, this test evaluates within a specified degree of certainty whether the average measured difference between before and after is real or could have occurred merely by chance.

Before we start discussing this specific test in detail, The t-test needs to be generally explained. The basic question to be answered is:

The t Test - What Is It?

The Higher the t Value - The More Likely the Groups Are Different

The Lower the Combined Variance, the Higher the t Value

T-Test Paired Two Sample for Means

A paired t test or paired difference t test is use to determine whether the average of the "before" and "after" measurements taken of a single set of objects is the same. The Null Hypothesis being tested states that there is no difference between the average "before" and "after" measurements. Specifically, the Null Hypothesis states that the mean of all "after" measurements minus the mean of all "before" measurements taken of the same objects equals 0.

We are going to use the paired t test to determine within 95% certainty whether the average sales from a group of sales territories increased after a new marketing program was implemented. We will simply measure the before and after sales from each territory and apply this t test using Excel to get our result.

A Little Bit More About This t Test

The t Test in general is a special case of one-way (sometimes called “single factor”) ANOVA. This paired two-sample student’s t test is applied when there is a natural pairing of samples. It is most often used to determine whether “before” and “after” means of a sample of the same objects have changed during an experiment. One really great thing about this t test is that the paired two-sample t test does not require that the variances of both populations to be the same.

To sum up the paired two-sample student’s t test, a single t value is calculated from data from both samples. Here is the formula to calculate the t value for a paired two-sample student’s t test if you are testing to determine whether the difference between two means is greater than zero:

t value = Average Difference Between Each Pair /
[ Stan. Dev. Of Average Differences / SQRT(n) ]

You can see that this follows the general formula for calculating the t value in a t test, which is:

t value = (Difference between the group means) / (Variability of the groups)

The t value is a specific point on the x-axis in the t distribution (student’s t distribution). If this t value falls outside the region of required certainty, it can be stated that the two means are probably different. If this t value falls within the region of required certainty, it cannot be stated that the two means are probably different.

The required region of certainty depends upon the degree of certainty required in the test. If 95% certainty is required, then the required region of certainty consists of 95% of the area under the student’s t distribution. The outer 5% is the region of uncertainty. This is also referred to as α (alpha) or the degree of significance. If the t value is large enough to be located all the way out on the x-axis in the 5% region of uncertainty, it can be stated within 95% certainty that the two means are different.

A t test can be a one-tailed test or a two-tailed test. A one-tailed test determines whether the means are different in one specific direction. For example, a one-tailed test could be used to determine only if the mean of the “after” measurements is greater than the mean of the “before” measurements. A two-tailed test determines whether the two means are merely different.

Two-Tailed t Test Is More Stringent

One-Tailed t Test Is Less Stringent

Doing The Paired Two-Sample t Test in Excel

We are testing to determine whether a new marketing campaign has increased sales in a group of six sales territories. In this case the sample size (n) equals 6. For this type of t test, the degrees of freedom = n – 1 = 5.

The data need to be arranged in Excel as follows:

Click on Image To See Enlarged View

Now, access this Excel t Test as follows (this is Excel 2003):

Tools / Data Analysis / t-Test: Paired Two Sample for Means

This following dialogue box will appear:

Click on Image To See Enlarged View

Input the data as followings:

Variable 1 Range: Select everything that is highlighted light blue, including the label “Sales After New Ads.” If you are trying to determine whether the “after” measurements have gone up, the “after” data is Input Variable 1. If you are trying to determine whether the “after” measurements have gone down, the “after” data is Input Variable 2.

Variable 2 Range: Select everything that is highlighted in yellow, including the label “Sales Before New Ads.”

Hypothesized Mean Difference: 0

Labels: Check the box because you included the labels for Variables 1 and 2.

Alpha: This depends on your desired degree of certainty. 0.05, if you desired 95% certainty. 0.20 if you desire 80% certainty.

Output Range: Select the cell that you want the upper left corner of the output to appear in.

Hit “OK” to run the analysis and the following Excel output appears:

Click on Image To See Enlarged View

This output can be interpreted as follows:

The t value is 2.511.

One-tailed Test

This t value is greater than the critical t value for a one-tailed test (2.015). We can therefore state with 95% certainty that the mean sales has increased as a result of the new marketing campaign.

The above conclusion can also be reached because the p Value for the one-tailed test (highlighted in light blue on the Excel output) is 0.027. This is less than alpha (0.05). The p Value being less than alpha is an equivalent result to the t value being greater than the t critical value.

Two-Tailed Test

A different result is arrived at for the two-tailed test. The two-tailed test is more stringent because the alpha region of uncertainty (5% of the area under the student’s t distribution curve) is now divided between both outer tails. The t value needs to be larger for the two-tailed test to wind up in the outer 2.5% area of either outer tail.

In this case, the t value was not large enough to be positioned in the outer 2.5% of either outer tail. The t value (2.511) is smaller than the critical t value for the two-tailed test (2.571). This indicates that it cannot be stated with 95% certainty that there has been a change in the mean from before to after.

The p value calculated for the two-tailed test (0.054) is larger than alpha (0.05). This is an equivalent result to the above.

Hand Calculation of the t Value and p Value

Click on Image To See Enlarged View

Here is the hand calculation of the t value and p values for the one and two-tailed tests for this Paired Two-Sample t Test. The hand calculation agrees with the Excel outputs. There are very slight differences due to rounding differences:

Click on Image To See Enlarged View

The Paired Two-Sample t Test is a very simple test to run and can be applied to nearly any aspect of your marketing program to see if a single change affected a large number of elements whose before and after measurements can be taken. One note: the before and after measurements must be continuous and using the same scale.

Here are other articles in this blog that might help your understanding of nonparametric tests:

Nonparametric Tests - How To Do the 4 Most Popular in Excel

A Quick, Easy Normality Test For Excel

Statistical Mistakes You Don't Want To Make

How To Do ALL Hypothesis Tests in Only 4 Steps

If you would like to create a link to this blog article, here is the link to copy for your convenience:

The t Tests - How and When Should the Marketer Use Them In Excel

Please post any comments you have on this article. Your opinion is highly valued!

If You Like This, Then Share It...

A Quick Normality Test Easily Done In Excel

2010-08-01T17:11:00.000-07:00

The Normality Test

Simple and Done in Excel

The normality test is used to determine whether a data set resembles the normal distribution. If the data set can be modeled by the normal distribution, then statistical tests involving the normal distribution and t distribution such as Z test, t tests, F tests, and Chi-Square tests can performed on the data set.

There are a number of well-known normality tests such as Kolmogorov Smirnov Test, Shapiro Wilk Test, and the Anderson Darling Test. In this article we will describe two normality tests that can be performed with Excel, but are much simpler than the above tests.

The Normality Test

The Most Basics Ones

The Histogram - The Simplest Normality Test

Probably the easiest normality test is to plot the data in an Excel histogram and then compare the histogram to a normal curve. This method works much better with larger data sets.

The Normal Probability Plot -
A Simple, Quick Normality Test for Excel

Another normality test that is very easy to implement in Excel is called the Normal Probability Plot. The data set is ranked in order and then plotted on a graph. Each point in the data set represents a y value of a plotted point. The x values of the points are Normal Order Statistic Medians. The closer than the graph is to a straight line, the more closely the data set resembles the normal distribution. Correlation analysis can also be performed the data set (called the Order Responses) and the Normal Order Statistic Medians. The closer the correlation coefficient is to 1, the more the data set resembles the normal distribution.

An Example

An example is the best way to illustrate the Normal Probability Plot. Evaluate the following data set of 6 points for normality:

{66, 76, 17, 23, 44, 41}

The rank of each data point is:

5, 6, 1, 2, 4, 3

The data in ranked order is:

{17, 23, 41, 44, 66, 76}

Now we have to calculate the Normal Order Statistic Medians. We know that we have 6 points so n = 6. The Normal Order Statistic Medians are given by the following formula:

N(i) = G(U(i))

U(i) are the Uniform Order Statistic Medians defined by this formula:

m(i) = 1 - m(n) for i = 1

m(i) = (i - 0.3175)/(n + 0.365) for i = 2, 3, ..., n-1

m(i) = 0.5(1/n) for i = n

G is called the Percent Point of the Normal Distribution. It is the inverse of the cumulative distribution function. In Excel, it would be the NORMSINV(x) function. It tells you the probability the x has a value of m(i) or less. Variable x is normally distributed on a standard normal curve (µ = 0 and σ = 1).

Given the above information, here is how the Normal Order Statistic Medians are calculated:

n = 6

Now calculate U(i) – the Uniform Order Statistic Medians.

U(i) are the Uniform Order Statistic Medians defined by this formula:

m(i) = 1 - m(n) for i = 1

m(i) = (i - 0.3175)/(n + 0.365) for i = 2, 3, ..., n-1

m(i) = 0.5(1/n) for i = n

i = 1 -->
m(1) = 1 – m(n) = 1 – m(6) = 1 – 0.8909 = 0.1091

i = 2 -->
m(2) = (i - 0.3175)/(n + 0.365) = (2 – 0.3175) / (6 + 0.365) = 0.2643

i = 3 -->
m(3) = (i - 0.3175)/(n + 0.365) = (3 – 0.3175) / (6 + 0.365) = 0.4214

i = 4 -->
m(4) = (i - 0.3175)/(n + 0.365) = (4 – 0.3175) / (6 + 0.365) = 0.5786

i = 5 -->
m(5) = (i - 0.3175)/(n + 0.365) = (5 – 0.3175) / (6 + 0.365) = 0.7357

i = 6 -->
m(6) = m(i) = 0.5(1/n) for i = n = m(i) = 0.5(1/6) = 0.8909

So,

U(1) = 0.1091
U(2) = 0.2643
U(3) = 0.4214
U(4) = 0.5786
U(5) = 0.7357
U(6) = 0.8909

The Normal Order Statistic Medians are given by the following formula:N(i) = G(U(i)) --> G(U(i)) is the inverse of the cumulative distribution function. It tells the x value that corresponds to the probability U(i) that a random sample taken from a standardized normally distributed population will have a value of x or less.

This is found in Excel by the following formula:

N(i) = G(U(i)) = NORMSINV(U(i))

So, the Normal Order Statistic Medians are given by:G(U(i)) = NORMSINV(U(i))

N(1) = NORMSINV(U(1)) = NORMSINV(0.1091) = -1.23
N(2) = NORMSINV(U(2)) = NORMSINV(0.2643) = - 0.63
N(3) = NORMSINV(U(3)) = NORMSINV(0.4214) = - 0.20
N(4) = NORMSINV(U(4)) = NORMSINV(0.5786) = 0.20
N(5) = NORMSINV(U(5)) = NORMSINV(0.7357) = 0.63
N(6) = NORMSINV(U(6)) = NORMSINV(0.8908) = 1.23

The above are the X values of the data points whose Y values are the ranked point in the data set. The ranked data set is:

{17, 23, 41, 44 66, 76}

So, the following points can be plotted:

(-1.23, 17) (-0.63, 23) (-0.20, 41) (0.20, 44) (0.63, 66) (1.23, 76)

The final graph will resemble a chart such as this:

The closer that the plotted resembles a straight line, the closer the data set resembles the normal distribution. You can also run correlation analysis between the data set of Ordered Responses and the Normal Order Statistic Medians. The closer the correlation coefficient is to 1, the more closely the data set resembles the normal distribution.

There are other well-known Normality tests such as the Kolmogorov-Smirnov Goodness-of-Fit Test, the Anderson-Darling Goodness-of-Fit Test, The Shapiro-Wilk Test, and the Chi-Square Goodness-of-Fit Test. I will very shortly publish an article or two in this blog which will detail how to do these tests in Excel.

If you are going to perform any statistical analysis that uses the normal distribution or t distribution such as Z test, t tests, F tests, and chi-square tests, you should first test your data set for normality. The Normal Probability Plot described in this article is probably the easiest and quickest way to do it in Excel.

Here are links to a few more articles in this blog which might help you to understand the normailty test better:

How To Create an Interactive Graph of the Normal Curve in Excel

Statistical Mistakes You Don't Want To Make

Nonparametric Tests - When Should the Marketer Use Them?

Nonparametric Tests - Completed Examples in Excel

If you would like to create a link to this post, here is a quick link that you can copy:
A Quick Normailty Test That Can Be Easily Performed in Excel

Statistical Mistakes You Don't Want To Make

2010-07-30T12:53:00.000-07:00

Common Statistical Errors

1) Assuming that correlation equals causation

– This is, of course, not true. However, if you find a correlation, you should look hard for links between the two. The correlation may be pure chance, but then again, it may not be. A correlation is a reason to look for underlying causes behind the behavior. Correlation is often a symptom of a larger issue.

2) Not graphing and eyeballing the data prior to performing regression analysis

– Always graph the data before you do regression analysis. You’ll know immediately whether you’re dealing with linear regression, non-linear regression, or completely unrelated data that can’t be regressed.

3) Not doing correlation analysis on all variables prior to performing regression

– You’ll save yourself a lot of time if you can remove any input variables that have a low correlation with the dependent (output – Y) variable or that have a high correlation with another input variable (this error is called multicollinearity). In the 2nd case, you would want to remove the input variable from the highly correlated pair of input variables that has the lowest correlation with the output variable.

4) Adding a large number of new input variables into a regression analysis all at once

– You always want to add new input variables one at a time and run a separate regression each time a new input variable is added. The changes you observe to the output of the regression will tell you whether the new input variable adds to the predictive power of the regression equation. Adjusted r squared only increases when a new variable adds greater predictive power to the regression equation.

5) Applying input variables to a regression equation that are outside of the value of the original input variables that were used to create the regression equation

– Here is an example to illustrate why this might produce totally invalid results. Suppose that you created a regression equation that predicted a child’s weight based upon the child’s age, and then you provided an adult age as an input. This regression equation would predict a completely incorrect weight for the adult, because adult data was not used to construct the original regression equation.

6) Not examining the residuals in regression

– you should always at least eyeball the residuals. If the residuals show a pattern, your regression equation is not explaining all of the behavior of the data.

7) Only evaluating r square in a regression equation

– In the output of regression performed in Excel, there are actually four very important components of the output that should be looked at. There is an article in this blog that covers this topic in a lot more detail than could be done in this bullet point.

8) Not drawing a representative sample from a population

– This is usually solved by taking a larger sample and using a random sampling technique such as nth-ing (sampling every nth object in the population).

9) Drawing a conclusion without applying the proper statistical analysis

- This occurs quite often when people simply eyeball the results instead of performing a hypothesis test to determine if the observed change has at least an 80% chance (or whatever level of certainty you desire) of not being pure chance.

10) Drawing a conclusion before a statistically significant result has been reached

– This is often caused by choosing a statistical test requiring a lot of samples but depending on a low sample rate. A common occurrence of this would be performing multivariate testing on a web site that does not have sufficient traffic. Such a test is likely to be concluded prematurely. A better solution might be to perform a number of successive A/B split-tests in place of multivariate analysis. You get a lot more testing done a lot faster, and correctly.

11) Analyzing non-normal data with the normal distribution

– Data should always be eyeballed and analyzed for normality before using the normal distribution. If the data is not normally distributed, you must use data fitting techniques to determine which statistical distribution most closely fits the data.

12) Not removing outliers prior to statistical analysis

– A couple of outliers can skew results badly. Once again, eyeball the data and determine what belongs and what doesn’t.

13) Not controlling or taking into account other variables besides the one(s) being testing when using the t test, ANOVA, or hypothesis tests.

Other variables that not part of the test need to be held as constant as possible during the above tests or your answer might be invalid without you knowing.

14) Using the wrong t test

– The t-test to be applied depends upon factors such as whether or samples have the same size and variance. It is important to pick the right t-test before starting.

15) Attempting to apply the wrong type of hypothesis test

– There are 4 ways that the data must be classified before the correct hypothesis test can be selected. Another article in this blog discusses this. Also, Chapters 8 and 9 of the Excel Statistical Master provide clear, detailed instructions on how to analyze your data prior to hypothesis test selection. You probably wouldn’t get far into a hypothesis test if you have incorrectly classified the data and selected the wrong hypothesis test.

16) Not using Excel

– This point may sound a little self-serving, but knowing how to do this stuff in Excel is a real time-saver, particularly if you are in marketing, and especially if you’re an Internet marketer. You’ll never need to pick up another thick confusing statistics text book or figure how to work those confusing statistics tables ever again. I’ve actually thrown out all of my statistics text books (well, not quite, I sold them on eBay).

17) Always requiring 95% certainty

– This could really slow you down. For example, if I’m A/B split-testing keywords or ads in an AdWords campaign, I will typically pick a winner when my split-tester tells me that it is 80% sure that one result is better than the other. Achieving 95% certainty would often take too long.

18) Thinking it is impossible to get a statistically significant sample if your target market is large

– The sample size you need from a large population is probably quite a bit smaller than you think. Nationwide surveys are normally within a percentage point or two from real answer after only several thousand interviews have been conducted. That of course depends hugely on obtaining a representative sample to interview.

19) Not taking steps to ensure that your sample is normally distributed when analyzing with the normal distribution

– One way to ensure that you have a normally distributed sample for analysis is to take a number of large samples (each sample consists of at least 30 objects) and then tke the mean from each sample as one sample point. You will then have one final, working sample that consists of the means of all of your previous samples. A statistical theory called the Central Limit Theory states that the means of a group of large samples (each sample consists of at least 30 objects) will be normally distributed, no matter how the underlying population is distributed. You can then perform statistical analysis on that final sample using the normal distribution.

20) Using covariance analysis instead of correlation analysis

– The output of covariance analysis is dependent upon the scale used to measure the data. Different scales of measurement can produce completely different results on the same data if covariance analysis is used. Correlation analysis is completely independent of the scale used to measure the data. Different scales of measurement will produce the same results on a data set using correlation analysis, unlike covariance analysis.

21) Using a one-tailed test instead of a two-tailed test when accuracy is needed

– If accuracy it what you are seeking, it might be better to use the two-tailed when performing, for example, a hypothesis test. The two-tailed test is more stringent than the one-tailed test because the outer regions (I call them the regions of uncertainty) are half the size in a two-tailed test than in a one-tailed test. The two-tailed test tells you merely that the means are different. The one-tailed test tells you that the means are different in one specific direction.

22) Not using nonparametric tests when analyzing small samples of unknown distribution

– The t Distribution should only be used in small sample analysis if the population from which the samples were drawn was normally distributed. Nonparametric tests are valid when the population distribution is not known, or is known not to be normally distributed. Using the t distribution in either of these cases for small sample analysis is invalid. I will write a couple of articles in this blog in the future detailing how and when to perform a couple of commonly-used nonparametric tests with Excel.

Here are other articles in this blog that might help your understanding of nonparametric tests:

Nonparametric Tests - How To Do the 4 Most Popular in Excel

A Quick, Easy Normality Test For Excel

The t Test - When and How To Do It In Excel

Nonparametric Tests- When Should the Marketer Use Them?

If you would like to create a link to this blog article, here is the link to copy for your convenience:

Statistical Mistakes You Don't Want To Make

Please post any comments you have on this article. Your opinion is highly valued!

How To Solve ALL Hypothesis Tests in Only 4 Steps

2010-07-28T13:07:00.000-07:00

Every Hypothesis Test

Done in Excel in 4 Steps

As an Internet marketing manager I use hypothesis testing all the time. There are quite a few great marketing uses of the hypothesis test with Excel that I will explain in detail in future articles of this blog. If you would like to see one very useful application of the hypothesis test in an article in this blog, check out this blog article on how to construct a split-tester in Excel that is better than the Google Website Optimizer. The basic test of this split-tester (and the Google Website Optimizer) is a hypothesis test.

Hypothesis Test Determines if Something Changed

In a nutshell, a hypothesis test is used to determine if something really has changed. For example, maybe you changed your Intenet marketing program slightly and you want to determine within 95% certainty whether the sales results that you've noticed are caused by your changes or are they just the result of random chance. The hypothesis test is the perfect tool to quickly answer that question. I will go so far as to say that the hypothesis test is my favorite Internet marketing statistical tool.

Hypothesis Test - Solved With 4-Step Framework

Right now I would like to present a 4-step framework that can be used to solve ALL hypothesis tests. To my knowledge, I have not seen this framework presented anywhere else, but it definitely works for every type of hypothesis test.

Hypothesis Test Must 1st Be Classified

Before you can begin the 4-step procedure, you must classify the hypothesis test you are about to perform. There are 4 separate categories in which the hypothesis test must be classified before applying the 4-step method. Each classification must be solved a slightly different way while applying the 4-step method. You therefore must determine upfront the type of hypothesis test so you will know exactly how to apply the 4-step method. The 4 categories of hypothesis tests are as follows:

Problem Classification:

Select the proper choice of each of the four ways that a Hypothesis problem is classified as follows:

1) Mean Testing vs. Proportion Testing

• Proportion test samples have only two possible outcomes.
• Mean test samples have multiple possible outcomes.

2) One-Tailed vs. Two-Tailed Testing

    • Two-tailed tests determine whether two means are merely different.
    • One-tailed tests determine whether one mean is different in one
         direction.

3) One-Sample vs. Two Sample Testing

    • One sample is taken if original or "Before" comparison data is
           available.
    • Two samples are taken if no comparison data is available.

4) Unpaired Data Testing vs. Paired Data Testing

    • Paired data testing can be performed if "Before" and "After" data
         are collected from the same objects. Mean testing can be
         performed on paired data - Proportion testing cannot.
    • Unpaired data testing is performed on data collected in groups.

Here below is a more detailed explanation of the above classifications:

1) Mean testing vs. Proportion testing -

This is the most important distinction that must be made. Mean testing and proportion are both solved using the same 4-step method but use different formulas.

Mean testing – Hypothesis tests of mean use samples that can taken a range of values. For example, you are testing to determine if sales have gone up over the course of a month. The sampled daily sales can have a wide range of values.

Proportion testing – Hypothesis test of proportion use samples that can have only 2 values. For example, you are testing to determine whether new keywords in a Google AdWords ad group have increased conversion rate. You are sampling whether or not a click converted. Your sample has only 2 possible values. The click either converted or it didn’t.

2) One-tailed vs. Two-tailed testing

– This depends upon whether you are using the hypothesis test to determine whether the mean or proportion of one sampled group is merely different that the mean or proportion of another sampled group, or whether it is specifically different in one direction – whether it is larger or smaller.

One-tailed test – You are testing to determine if the mean or proportion of one sampled group is different in one specific direction than the mean or proportion of the other sampled group.

Two-tailed test – You only want to know if the mean or proportion of one group is different than that of the other group, but aren’t testing for direction.

3) One-sample vs. two-sample testing

– Whether you need to take one sample or two samples depends on whether you need have original or “before” sample data available. Two-sample testing is performed if no “before” data is available, or if no data is available on either side.

4) Unpaired data testing vs. paired data testing

– Most hypothesis tests use unpaired data. Whether data is paired or unpaired depends on whether both samples were collected from the same objects or not.

Paired data testing – An example of this would be “before” and “after” testing of the same object. For example, you are measuring whether sales really went up. Paired data testing can only be performed for a hypothesis test of mean, not proportion.

Unpaired data samples – Groups of unpaired data testing are treated independently of each other.

The 4-Step Method To Solve ALL Hypothesis Tests

After having classified the hypothesis test according to the 4 categories, you are now ready to perform the 4-step method. In summary, the steps are as follows:

1) Create the Null and Alternate Hypotheses

2) Map the Normal Curve
- Showing the Distribution of the Variable Used by the Null Hypothesis.

3) Map the Region of Certainty

– The Area Under the Normal Curve That Corresponds With the Degree of Certainty You Require For Your Hypothesis Test.

4) Perform Either the Critical Value Test or the P Value Test

– to Determine Whether To Reject or Fail To Reject the Null Hypothesis

Without going into too much detail, we will take a brief look at solving a hypothesis test using the 4-step method.

Problem - One-Tailed, One-Sample, Unpaired Hypothesis Test of Mean

Testing whether a delivery time has gotten worse

Problem: A furniture company states that its average delivery time is 15 days with a (population) standard deviation of 4 days. A random sample of 50 deliveries showed an average delivery time of 17 days. Determine within 98% certainty (0.02 significance level) whether delivery time has increased.

SOLUTION:

We know that this is a test of mean and not proportion because each individual sample taken can have a wide range of values: Any delivery time sample measurement from 12 to 18 days is probably reasonable.

We know that this is a one-tailed test because we are trying to determine if the "After Data" mean delivery time is larger than the "Before Data" mean delivery time, not whether the mean delivery times are merely different.

We know that only one sample needs to be taken because the population data being tested is already available.

This is unpaired data because groups are sampled independently. Below is the Before and After sample data:

Click On Image To See Enlarged View

Step 1 - Create the Null and Alternate Hypotheses

The Null Hypothesis normally states that both means are the same.

If the "Before Data" population mean, µ, equals the "After Data" sample mean, xavg, then

xavg = µ = 15

The Null Hypothesis states that both means are the same, which is equivalent to:

The Null Hypothesis, which states that xavg is the same as µ (which is 15), is as follows:

Null Hypothesis, H0 ---->; xavg = 15

*****************************************************************************

The Alternate Hypothesis states that the After Data mean is larger, which is equivalent to:

The Alternate Hypothesis, which states that xavg is larger than µ (which is 15), is as follows:

Alternate Hypothesis, H1 ---->; xavg > 15

Step 2 - Map the Normal Curve

We now create a Normal curve showing a distribution of the same variable that is used by the Null Hypothesis, which is xavg.

The mean of this Normal curve will occur at the same value of the distributed variable as stated in the Null Hypothesis.

Since the Null Hypothesis states that xavg = 15, the Normal curve will map the distribution of the variable xavg with a mean of xavg = 15.

This Normal curve will have a standard error that is calculated as the standard error of a sample taken from a population is normally calculated, as follows:

Sample Standard Error = sxavg = σ / SQRT(n) = 4 / SQRT(50) = 0.566

Here is the Normal Curve mapped with the mean of xavg = 15

and Sample Standard Error = 0.566

Click On Image To See Enlarged View

Step 3 - Map the Region of Certainty

The problem requires a 98% Level of Certainty so the Region of Certainty will contain 98% of the area under the Normal curve.

We know that this problem uses a one-tailed test with the Region of Uncertainty entirely contained in the outer right tail. The Region of Uncertainty contains 2% of the total area under the Normal curve. The entire 98% Region of Certainty lies to the left of the 2% Region of Uncertainty, which is entirely contained in the outer right tail.

*****************************************************************************
We need to find out how far the boundary of the Region of Certainty is from the Normal curve mean. Calculating the number of standard errors from the Normal curve mean to the outer boundary of the Region of Certainty in the right tail for a one-tailed test is done in Excel as follows:

z 98%,1-tailed = NORMSINV(1 - α) = NORMSINV(0.98) = 2.05

Excel Note - NORMSINV(x) = The number of standard errors from the Normal curve mean to a point right of the Normal curve mean at which x percent of the area under the Normal curve will be to the left of that point.

Additional note - For a one-tailed test, NORMSINV(x) can be used to calculate the number of standard errors from the Normal curve mean to the boundary of the Region of Certainty whether it is in the left or the right tail.

The Region of Certainty extends to the right of the Normal curve mean of xavg = 15 by 2.05 standard errors.

One standard error = sxavg = 0.566, so:

2.05 standard errors = (2.05) * (0.566) = 1.16

The outer boundary of the Region of Certainty has the value = µ + z 98%,1-tailed * sxavg

which equals 15 + (2.05) * (0.566) = 15 + 1.16 = 16.16

The point, 16.16, is 2.05 standard errors from the Normal curve mean of xavg = 15

This point, 16.16, is the right boundary of the 98% Region of Certainty on the Normal curve.

Here is the mapping of the Region of Certainty:

Click On Image To See Enlarged View

Step 4 - Perform Critical Value and p-Value Tests

a) Critical Value Test

The Critical Value Test is the final test to determine whether to reject or not reject the Null Hypothesis. The p Value Test, described later, is an equivalent alternative to the Critical Value Test.

The Critical Value test tells whether the value of the actual variable, xavg, falls inside or outside of the Critical Value, which is the boundary between the Region of Certainty and the Region of Uncertainty.

If the actual value of the distributed variable, xavg, falls within the Region of Certainty, the Null Hypothesis is not rejected.

If the actual value of the distributed variable, xavg, falls outside of the Region of Certainty and, therefore, into the Region of Uncertainty, the Null Hypothesis is rejected and the Alternate Hypothesis is accepted.

In this case, the actual value of the variable, xavg = 17

The actual value of the variable xavg = 17 and is therefore to the right of (outside of) the outer right Critical Value (16.16), which is the boundary between the Regions of Certainty and Uncertainty in the right tail.

The actual value of the variable xavg is outside the Region of Certainty and therefore outside the Critical Value.

We therefore reject the Null Hypothesis and accept the Alternate Hypothesis which states that delivery time has increased, with a maximum possible error of 2%. This is shown in the following Excel graph:

Click On Image To See Enlarged View

b) p Value Test
The p Value Test is an equivalent alternative to the Critical Value Test and also tells whether to reject or not reject the Null Hypothesis.

The p Value equals the percentage of area under the Normal curve that is in the tail outside of the actual value of the variable xavg.

For a one-tailed test, if the p Value is larger than α, the Null Hypothesis is not rejected.

For a two-tailed test, if the p Value is larger than α/2, the Null Hypothesis is not rejected.

For a one-tailed test, the Region of Uncertainty is contained entirely in one tail. Therefore the curve area contained by the Region of Uncertainty in that tail equals α.

For a two-tailed test, the Region of Uncertainty is split between both tails. Therefore the curve area contained by the Region of Uncertainty in that tail equals α/2.

The p Value for the actual value of the distributed variable, which in this case is greater than the mean (falls to the right of the mean in the right tail), calculated in Excel is:

p Valuexavg = 1 - NORMSDIST( [ xavg - µ ] / sxavg )

Excel note - NORMSDIST(x) calculates the total area under the Normal curve to the left of the point that is x standard errors to the right of the Normal curve mean.

p Valuexavg = 1 - NORMSDIST((17 - 15 ) / 0.566)

= 1 - NORMSDIST(2/0.566)

= 0.0002

The p Value (0.0002) is less than α (0.02), so the Null Hypothesis is rejected and the Alternate Hypothesis is accepted..

For a one-tailed test---> When the p Value is less than α, the actual value of the distributed variable falls outside the Region of Certainty and the Null Hypothesis is rejected.

This is the case here as shown in the Excel graph:

Click On Image To See Enlarged View

In subsequent articles to this blog, I will show some very useful ways of using various types of hypothesis tests in Excel to improve your marketing. If you are interested in getting a deeper understanding of how to use Excel to perform hypothesis tests, Chapters 8 and 9 of the
Excel Statistical Master go into a lot of detail with many examples of doing every type of hypothesis test in Excel.

Feel free to comment on this blog article. Your opinion is very important.

How To Analyze Your Twitter Follower Program With the Excel Histogram

2010-06-17T13:24:00.000-07:00

How To Twitter Better

With a Histogram in Excel

How’s your Twitter Follower acquisition program going? If you use Twitter as a prospecting tool, you need a convenient system in place to monitor how well your Twitter account(s) are picking up followers.

The Histogram in Excel is a tool custom-made for that job. All you have to do is record the total number of new followers that you pick up every day in Excel and then run a Histogram on that data once per month. You can visually compare Histograms from month to month to know immediately which direction your Twitter Follower Acquisition program is going.

What Is a Histogram?

A Histogram divides a large group of dissimilar objects into many smaller groups of similar objects. Excel allows you to arrange the smaller groups in these two ways:

1) By Size of Objects in Group - You can also arrange the groups by the size of the objects in each group. The group on the left will contain objects of the smallest size. Groups to the right contain objects of progressively larger size. This is the type of group arrangement that will be used to monitor your Twitter Follower Acquisition program:

Click On Image To See Enlarged View

2) By Number of Objects in Group - You can arrange the groups into a Pareto chart. In this case, the groups are arranged by the number of objects in each group. The group with the largest number of objects appears on the left side of the Pareto chart. Groups to the right get progressively smaller:

The embedded video below will provide step-by-step instructions on how to use the Histogram in Excel to monitor your Twitter Follower Acquisition program. Here is the video:

Here is a Step-By-Step Video Showing How to Determine If Your Twitter Follower Acquisition Program Is Getting Better Or Worse By Using An Excel Histogram:

(Is Your Sound Turned On?)

Steps To Using the Histogram to Monitor Your Twitter Follower Acquisition program:

1) Record the total combined number of new followers
you pick on all of your Twitter accounts every day. It is most convenient to record these daily figures on an Excel spreadsheet as follows:

2) Run a monthly Histogram on this data
. The Histogram will divide the overall group of daily follower pickup numbers into many smaller groups that have similar numbers of daily followers picked. The Excel Histogram will produce a number of smaller groups with containing objects (days) of similar size (number of followers picked up in a day). The groups are then arranged on a chart. The groups can be arranged by the size of the objects in the groups or by the number of objects in the groups (a Pareto chart). In this case, the groups will be arranged by the size of objects in the groups. Here is an example of such a Histogram:

3) Record each monthly Histogram on an Excel spreadsheet and then compare them
each month. Here is an example of a monthly comparison:

You can clearly see that April’s Twitter Follower Acquisition program did significantly better than May’s. Something must have happened between April and May which hurt the Follower Acquisition program. The Twitter account owner needs to figure out what happened that caused the decline. The Excel Histogram makes this comparison quick, easy, and accurate.

Some Tips About Using Twitter for Customer Acquisition

I use Twitter quite a lot for prospecting. I currently have 9 Twitter accounts and, at the time of this writing, acquire about 300 new followers total per day. That’s not so many but I keep it conservative to avoid attracting Twitter’s attention. Here’s what has worked for me so far:

First and foremost, play it safe.

Try to stay under Twitter’s radar by not being too aggressive in acquiring new followers. Twitter will suspend accounts if any of its limits are breached. It is now becoming harder and harder to get a suspended account reinstated. The larger of a Twitter following you have, the more you have to lose if Twitter suspends one or more of your accounts. It is probably best to stay well within the limits of the following rules:

1) Limit follows to 350 per account per day.

The maximum allowable number of new Follows per day per account is 1,000. This includes Follows sent out and Follow-Backs. Your best bet is to stay way below this limit to avoid triggering Twitter’s attention. If you limit the daily Follows sent out by any single account to 350 or less, you should have no problem. I use TweetAdder to automatically keep Follows plus Follow-Backs within this limit.

2) Keep your Following / Follower Ratio to 1.10
. After a Twitter account has acquired 2,000 followers, the 10% Rule applies. The number of people you follow can’t exceed the number of people who follow you by 10%. In other words, if one of your Twitter accounts has reached 2,000 followers, don’t allow the Follows sent out plus the FollowBacks to exceed 200, until more people begin to follow you or you unfollow a few people. It is best to try to keep your tff (Twitter Following / Follower) ratio to be just a tiny bit under 1.10. I use TweetAdder to automatically keep my tff ratio 1.09 or lower for my Twitter accounts that have more than 2,000 followers.

3) Wait at least 2 to 3 days to unfollow anyone.
Don’t unfollow anyone for at least one whole day after you begin following them. Twitter views this as churn. That’s a label you don’t want from Twitter. You’d be better off if you never unfollow anyone for at least 2 days, preferably 3. TweetAdder handles this for me pretty easily. The attached TweetAdder video shows how this is done.

4) Don’t set up more than 10 Twitter accounts from 1 IP address.
If you need to set up more than 10 Twitter accounts, Google “proxy IP address” or “proxy IP server” to find out how. This limit is why I have only 9 Twitter accounts. I’ve heard of people with many more accounts, but it’s risky unless you really know what you’re doing.

5) Take care if you use derivatives of the same gmail account
. If you have set up multiple Twitter accounts based on derivatives of the same gmail account (joejohnson@gmail.com, joe.johnson@gmail.com, and j.oejohnson@gmail all forward to the same email account but can be used to set up separate Twitter accounts), be aware that if any of those accounts are suspended, all of the accounts will be suspended.

6) Tweet no more often then hourly and 10X per day.
If you are using a tweet automation tool, it is best to tweet no more often than hourly and limit to 10 per day total per account. I use SocialOomph to automate my Tweets at regular hourly intervals during certain times of the day.

7) Don't let different accounts Tweet the same thing at the same time.
If you have multiple Twitter accounts and use an automation tool to Tweet at regular intervals, makes sure that no accounts are tweeting the same thing at the same time. In the past, I’ve had a few Twitter accounts suspended for this reason. I’m much more careful now when I’m setting up the timing, intervals, tweet content, and tweet rotation on SocialOomph. All accounts tweet at different times and with different tweets. The attached SocialOomph video shows how this works.

If you are using Twitter for prospecting, consider automating at least some parts of your Twitter interaction. If you are trying to achieve large numbers of Twitter followers, automation is almost mandatory. I’ve had good luck with the following 3 tools: TweetAdder, SocialOomph, and bit.ly. Here is a little description of each and how I use it, along with a video so you can take a look at how I use these tools.

TweetAdder – TweetAdder automates the following tasks for me:

1) Automates Follows sent and Follow-Backs for each account.

2) Automatically keeps my tff ratio at 1.09 for all of my accounts with more than 2,000 follows.

3) Automatically unfollows anyone who doesn’t follow me back within a time limit that I specify. I usually keep that time limit at 3 days for all accounts.

4) Provides a “safelist” of accounts I’m following that won’t be unfollowed.

5) Creates a “who to follow” list for each account based on any of the following criteria that I specify: Tweet keywords, Profile data, Location, Followers of another user, and Users followed by another user.

6) Allows for completely different settings on each of my Twitter accounts.

7) Can be used to automate tweets but I find that SocialOomph is the best tool for that.

8) Can be used to automatically send replies and direct messages, but I avoid doing this because can come across as “spammy” to recipients.

9) It’s quite easy to use and very intuitive. I probably spend 15 minutes total daily on all 9 of my Twitter account making little adjustments here and there, and then, with the push of 2 buttons, all automated tasks of all accounts run all the way to completion.

Here’s a video to show you how I use TweetAdder to manage Follows and Unfollows for all of my accounts:

Here is a Step-By-Step Video Showing How to Use TweetAdder To Add 300 New Followers to Your Twitter Program Every Day:

(Is Your Sound Turned On?)

If you are interested in TweetAdder, you can click here to check it out. It has a free trial. If you find it useful, there are 4 pay versions that can be purchased with a one-time fee as follows at the time of this writing: Managing 1 Twitter profile - $55, managing 5 Twitter profiles - $74, managing 10 profiles $110, and managing unlimited profiles - $187. I’ve purchased the unlimited version, but I’m only using it to manage 9 Twitter profiles at the moment.

I’ve also heard that another similar tool called Hummingbird does a great job at managing Follows and Unfollows, but I don’t have experience with it so I can’t provide any insight into it. I can say that TweetAdder has done a very fine job for me so far and I’m now averaging about 300 new followers a day with not much effort on my part.

SocialOomph
– SocialOomph works great to automate Tweets for all of my accounts. SocialOomph has so many uses that I probably haven’t tapped into even 30% of its functionality, but here’s what it does for me:

1) Schedules tweets for any Twitter account that will continuously repeat themselves at any interval 24 hours or greater that I specify. This makes SocialOomph a real Set-And-Forget tool, if you want that.

2) Creates a series of Tweets that I specify which will be sent out on a rotating basis at the same time every day (or at any interval 24 hours or greater). I use this function to prevent any of my Twitter accounts from sending out the same Tweet at the same time every day.

3) Auto-follows anyone who follows me.

4) Has a very good URL shortening service, but I’ve using bit.ly for a long time and have had good results with bit.ly so I’ll stick with bit.ly.

Here is a short video showing how I use SocialOomph set up a rotating series of Tweets that will be sent out by one of my Twitter accounts at the same time everyday:

Here is a Step-By-Step Video Showing How to Use SocialOomph To Automate Your Twitter Tweets and Get More Followers:

(Is Your Sound Turned On?)

If you are interested in using SocialOomph, click here to try a free version. The free version, however, does not provide the service of recurring tweets, which is the main reason that I like and use SocialOomph. Maybe I’m just lazy but I just love “Set and Forget.” You can easily upgrade from the free version to the pay version.

The pay version of SociaOomph costs $29.97 per month at the time of this writing. I’ve been using the pay version of SocialOomph exclusively to automate recurring and rotating Tweets for all of my Twitter accounts and it’s worked very well so far. Here is a link to SocialOomph if you are interested.

bit.ly – bit.ly is not only great for creating very short URLs but it also provides excellent analytics. I use Google Analytics tracking data to monitor anything happening on my site, but offsite, I use bit.ly to track clicks. I exclusively use bit.ly to track offsite clicks so all of the click-through information is maintained in one place.

To Sum It All Up:

The Excel Histogram is a fast and accurate analysis tool that will immediately tell you whether your Twitter Follower Acquisition Program is improving or not.

A great combination to build a larger Twitter following includes TweetAdder, SocialOomph, and bit.ly.

How To Find Out if Your Customers are Becoming More or Less Predictable in Their Spending With the Chi-Square Variance Test in Excel

2010-05-22T12:45:00.000-07:00

Chi-Square Population

Variance Test in Excel

for Marketing

It’s hard to predict exactly how much each one of your new customers will buy, but you probably have a good idea of the range that you would expect your customer’s spending to fall within.

What if you suddenly noticed a blip in recent customer order amounts which indicated that your customer spending spread might be widening? Is there a way know for sure whether the spending spread really has widened, or is this just a temporary aberration that grabbed your attention but may not mean anything?

In statistical terms, the question you would be asking is: Has the standard deviation of my customers’ order size increased? Good news --> There is a convenient statistical test you can quickly run in Excel to find that out. The test is called the Chi-Square Variance Test and is used to determine if the variance of a population has changed. Variance equals the standard deviation squared so if a population’s standard deviation increases, so does its variance, to an even greater degree.

The Chi-Square Variance Test is a great and simple way to determine whether your customers are more or less focused in their purchases of you products. More importantly, the Chi-Square Variance Test tells you whether your customers are being affected by something is changing what they buy from your company.

If variance of your customers’ spending has become smaller, your customer order size has become more predictable and more focused. If the variance has increased, your customer order size has become less predictable and less focused.

This Test Will Not Tell You What Changed, Only That Something Has Changed.

If the range (standard deviation) of customer spending on individual orders changes, the product mix that your customers normally purchase is changing. The underlying reason for this probably has important implications for the marketing program. If standard deviation (and therefore the variance) of order size changes, you will want to investigate further and find out why.

Here is a video that will demonstrate step-by-step how to perform the Chi-Square Variance Test in Excel to determine if the majority of your customers really have become more or less focused in their spending on individual orders.

Here is a Step-By-Step Video Showing How to Find Out If Your Customers Have Become More or Less Focused In Their Spending By Using the Chi-Square Variance Test in Excel:

(Is Your Sound Turned On?)

What Is the Chi-Square Variance Test?

The Chi-Square Variance Test and is used to determine if the variance of a population has changed. Marketers use the Chi-Square Variance Test to determine if the expected range of customer spending is changing. If so, something is affecting the buying habits of the customers.

The Chi-Square Variance Test consists of just 2 calculations that require only 4 inputs total.

These 4 inputs are:

1) Historic Standard Deviation, σ, of the population – This would be the long-time standard deviation of customer spending per order. It shouldn’t be too hard to calculate a standard deviation of past customer order size. Population Standard Deviation is usually denoted as σ, sigma.

2) Standard Deviation, s, of a recent large (at least 30) sample drawn randomly from the population. Make sure that the sample is random and is representative of the population from which the Population Standard Deviation was taken. Sample Standard Deviation is usually denoted as s.

3) The Sample Size, n.

4) The Degree of Certainty desired in the test. For example, you might want to be at least 95% certain of the outcome determined by the test.

The Chi-Square Variance Test requires measurements of standard deviation, not variance. That has no effect because, as mentioned above, variance is derived from standard deviation. Variance equals standard deviation squared.

The 5 Steps of the Chi-Square Variance Test

There are 5 steps in the Chi-Square Variance Test. They are;

Step 1) Determine the Required Level of Certainty, and, therefore, α, Alpha.

Step 2) Measure Sample Standard Deviation (s) from a large recent random sample drawn from the same population from which the Population Standard Deviation (σ) was derived. Sample size, n, must be at least 30.

Step 3) Calculate the Chi-Square Statistic.

Chi-Square Statistic = [ (n-1)*(s*s) ] / [σ*σ]

Step 4) Calculate the Curve Area Outside of the Chi-Square Statistic.

There are 2 possibilities:

a) If Sample Standard Deviation, s, is greater than the population Standard Deviation (σ):

Calculate the Area in the Right Outer Tail to the Right of the Chi-Square Statistic by this formula:

Tail Area Right of Chi-Square Statistic =
CHIDIST( Chi-Square Statistic, n-1 )

In this blog article and attached video, we will color the tail area outside the chi-Square Statistic with RED.

We will also color the area under the curve that represents alpha with yellow, as follows:

The 5% Alpha Area (Yellow) Resulting
From 95% Required Certainty

The Red Area Outside the Chi-Square Statistic
(Is Smaller Than the Yellow Alpha Area)

b) If Sample Standard Deviation, s, is less than the population Standard Deviation, σ,

then: Calculate the Area in the Left Outer Tail to the Left of the Chi-Square Statistic Tail:

Tail Area Left of Chi-Square Statistic =
1 - CHIDIST( Chi Square Statistic, n-1 )

The 5% Alpha Area (Yellow) Resulting
From the 95% Required Certainty

The Red Area Outside the Chi-Square Statistic
(Is Larger Than the Yellow Alpha Area)

The area under the Chi-Square curve that lies outside of the Chi-Square Statistic is sometimes called the P Value. For example, if 3% of the curve area lies outside the Chi-Square Statistic, then the P Value is 0.03.

Step 5) Analyze Using the Chi-Square Statistic Rule: If the area under the curve outside the Chi-Square Statistic is less than alpha, the population variance has moved in the direction of Sample Standard Deviation.

For example, if alpha is 0.05 (you require 95% certainty and alpha is therefore 0.05) and only 3% of the area under the Chi-Square curve lies outside of the Chi-Square Statistic, then you can now state with 95% certainty that the variance had moved.

The variance would have moved in the direction of the Sample Standard Deviation. If Sample Standard Deviation was measured to be greater the Population Standard Deviation and the curve area outside of the Chi-Square Statistic (3% = 0.03) was less than alpha (0.05), you can state with 95% certainty that population variance has increased.

To sum it up with charts:

If the red area fits inside the yellow area, we can state with the required degree of certainty that the population variance has moved in the direction of the sample standard deviation. In the case directly below, the red area does fit inside the yellow (alpha) area so we can state that the population varianced moved to the right (increased), with 95% certainty:

The Red Area Outside the Chi-Square Statistic
Is Smaller Than the Yellow Alpha Area

If the red area DOES NOT fit inside the yellow area, we CANNOT state with the required degree of certainty that the population variance has moved in the direction of the sample standard deviation. In the case directly below, the red area DOES NOT fit inside the yellow (alpha) area so we CANNOT state that the population varianced moved to the left (decreased), with 95% certainty:

The Red Area Outside the Chi-Square Statistic
Is Larger Than the Yellow Alpha Area

Here is the Test We Ran

Here is the problem definition: Customers on a commercial web site have historically had a standard deviation of 1.6 in the number of items they buy on individual purchase orders. The company’s Internet marketing manager took a random sample of 50 recent orders and measured the standard deviation of that sample to be 1.9 items per order.

The Internet marketing manager wanted to know with at least 95% certainty whether the population standard deviation had increased (had moved in the direction of the sample standard deviation).

The Required 4 Pieces of Information

1) Population Standard Deviation, σ, of item per order = 1.6

2) Sample Standard Deviation, s, of items per order = 1.9

3) Sample size, n = 50

4) Required Level of Certainty = 95%

The 5 Steps of the Chi-Square Variance Test

Using the 5-Step Chi-Square Variance Process, the Internet Marketing Manager determines within 95% certainty whether the population variance has increased as follows.

Step 1) Determine the Required Level of Certainty, and, therefore α, Alpha.

The Required Level of Certainty is 95%. Alpha, α, is 0.05.

Alpha = 1 – Required Level of Certainty = 1 – 95% = 0.05

Step 2) Measure Sample Standard Deviation (s) from a recent, large, representative random sample drawn from the same population from which the Population Standard Deviation (σ) was derived.Sample Standard Deviation, s, of items per order = 1.6. Population Standard Deviation, σ, of item per order = 1.6

Step 3) Calculate the Chi-Square StatisticChi-Square Statistic = [ (n-1)*(s*s) ] / [σ*σ]Chi-Square Statistic = [ (50 - 1) * (1.9 * 1.9) ] / [1.6 * 1.6] = 69.09766

Step 4) Calculate the Curve Area Outside of the Chi-Square Statistic

If Sample Standard Deviation, s, is greater than the population Standard Deviation (σ):

then calculate the Area in the Right Outer Tail outside of the Chi-Square Statistic by this formula:

Tail Area Right of Chi-Square Statistic =
CHIDIST( Chi-Square Statistic, n-1 )

Since s > σ,

Tail Area Right of Chi-Square Statistic =
CHIDIST( Chi-Square Statistic, n-1 )

Tail Area Right of Chi-Square Statistic =
CHIDIST( 69.09766, 49 ) = 3.07

So area under the curve outside the Chi-Square Statistic = 3.07%

The P Value = 0.0307

The Red Area Outside the Chi-Square Statistic
(Is Smaller Than Alpha) in the Outer Right Tail
So We Can State With 95% Certainty That
the Population Variance Have Moved to the
Right (Increased)

Step 5) Analyze Using the Chi-Square Statistic Rule: If the P Value (P Value = 0.0307), the area under the curve outside the Chi-Square Statistic, is less than α (α = 0.05), the population variance has moved in the direction of Sample Standard Deviation.

We can see that the red area fits inside of the yellow area on the outer right tail. In this case, the area outside the Chi-Statistic (3.07%) is less than Alpha (5%), we can state with 95% certainty that the population variance has increased.

Now the Internet marketing manager needs to determine the underlying reason why customer spending has become less focused.

Conclusion - The Chi-Square Variance Test Tells Whether Something Has Changed Your Customers' Buying Habits

Incidentally, all of the Chi-Square Probability Density Function graphs in this article had 49 Degrees of Freedom. Degrees of Freedom is derived from sample size and equals n-1 (50 - 1 = 49 Degrees of Freedom).

Here are links to other training videos of how to create interactive graphs in Excel of some of the other major statistical distributions:
How to Graph the Normal Distribution's Probability Density Function in Excel

How To Graph the Normal Distribution's Cumulative Distribution Function in Excel

How To Graph the Students t Distributions' Probability Density Function in Excel

How To Graph the Chi-Square Distribution's Probability Density Function in Excel

How To Graph the Weibull Distribution's PDF and CDF - in Excel

If you have any questions or comments about this article and attached videos, please post them below. Your opinion is highly valued

Graphing the Normal Distribution in Excel with User Interactivity

2010-05-10T12:57:00.000-07:00

Normal Distribution

Creating an Interactive

Graph in Excel

If you are doing statistical analysis of your marketing program, you will eventually want to plot some statistically-distributed data in an Excel graph. The most commonly-occurring statistical distribution in nature and, of course, in business, is the normal distribution.

This blog post and video will show you how to create a user-interactive graph of the normal distribution in Excel. The user of your graph will be able to vary the two parameters of the Normal Distribution - the mean and standard deviation - and then watch the changes instantly reflected in the Excel graph. Note the differences between the graph above and the graph below as a result of changes in the 2 user inputs of mean and standard deviation.

We’ll even this one step further. Sometimes you need to show a different between regions under the Normal curve. This article and attached video also provide step-by-step instructions on how to graph the Normal Distribution so that the outer 2% tails colored differently so they will stand out from the rest of the graph. After watching the video, you will be able to easily differentiate any region under the Normal curve by making it a different color.

Here is a step-by-step video showing how to create a user-interactive Excel graph of the Normal Distribution:

(Is Your Sound Turned On?)

The concepts covered in this article really need to be observed in the video for full comprehension. Below is an outline of the major steps appearing in the video that are needed to construct a user-interactive graph in Excel of the Normal Distribution with highlighted outer tails.

1) Create the X-Axis

You’ll notice on the video that when the user varies the mean or standard deviation inputs, it is the X-Axis that changes, not the graph. The graph will always show 3 standard deviations of normal distribution from the mean in both directions. It is the X-Axis increments that will change as a result of changes made by the user to the mean and standard deviation. The initial left-hand column starts at the top at -30 and finishes at the bottom at +30. This creates the basis of the 3 standard deviations of Normal Distribution that will show at all times on each side of the mean, regardless of the mean and standard deviation that the user has input.

2) Create 2 columns that will calculate the Normal Distribution’s PDF at each point of the X-Axis.

PDF is the Probability Density Function of the Normal Distribution. The data in each of these 2 columns are the Y values of two identical graphs, one sitting on top of the other.

3) Zero out the Y-Values on the outer edge(s) of the 2nd column.

The 2nd column sits directly on top of the 1st column. If you set any the Y-values for that graph to 0, you allow the identical graph directly underneath to show through. For example, if you zero out the outer 2% of the Y-values on top and bottom of the 2nd column, you are allowing the lower graph to show through for the outer 2% of each tail. You are, in effect, graphing the outer 2% of the Normal Distribution by deleting the outer 2% of the tails in the upper graph, letting the different-colored lower graph to show.

4) Set the color of all elements on the Excel chart as you see best.

For example, you might want to change the color of the upper or lower graphs, or the color of the background. You simply left-click on the element that you wish to change the color of and then select the color in the color dialogue box that pops up. The video shows an example of this being done.

That sums up the steps you’ll see in the video.

Here are links to other training videos of how to create interactive graphs in Excel of some of the other major statistical distributions:

How to Graph the Normal Distribution's Probability Density Function in Excel

How To Graph the Normal Distribution's Cumulative Distribution Function in Excel

How To Graph the Students t Distributions' Probability Density Function in Excel

How To Graph the Chi-Square Distribution's Probability Density Function in Excel

How To Graph the Weibull Distribution's PDF and CDF - in Excel

If you have any questions or comments about this article and attached videos, please post them below. Your opinion is highly valued!

If You Like This, Then Share It...

How To Find Out What Makes Your Customers Buy More Using the Chi-Square Independence Test in Excel

2010-05-06T10:25:00.000-07:00

Chi-Square Independence

Test in Excel

For Marketing

If there was one question that every single marketing manager and business owner would like an answer to, it might be this one: “What makes my customers buy more???” Anyone who has had that question on their mind will like this article. If you have been collecting data on your customers, you can use Excel to perform a statistical procedure called the Chi-Square Independence Test to find out what factors seem to have made a difference when your customers make larger purchases.

The attached video demonstrates how to perform the Chi-Square Independence Test in Excel to determine whether the size of a customer’s online order is related to the amount of time that the customer has spent on the web site. The video walk you through step-by-step in Excel how to perform the test and interpret the output:

Here is a Step-By-Step Video Showing Exactly How To Find Out What Makes Your Customers Buy More With the Chi-Square Independence Test in Excel:

(Is Your Sound Turned On?)

What Is the Chi-Square Independence Test?

The Chi-Square Independence Test can be quickly summed up as follows: This test determines whether two attributes of one object are related (not independent of each other). The object in mind is your customer. One of your customer’s attributes that we will evaluate is order size. The other attribute can be anything connected with individual orders that you consistently collect data on and that you would like to know whether order size is related to.

One caveat should be mentioned up front about this test. The Chi-Square Independence Test only shows if two attributes of an object are related. It does not prove causality. However, where’s there’s smoke, there’s fire. Even if one attribute that you are testing (in this case, customer’s time on your web site) does not directly cause the other (order size), there must be something related to time on the web site that does cause increased order size. This test will definitely point you in the right direction in determining what the most important factors are that cause larger orders.

Here's How We Did Our Chi-Square Independence Test

Here’s how we did the test that you’ll see in the video. First we took a random sample of 10,000 visitors to a commercial website from a much larger universe of visitors to that site in the same time period. We collected data on each of those customers. The data we collected for each customer was 1) how items that customer purchased and 2) how long that customer stayed on the web site during the visit that they ordered.

We will then use the Chi-Square Independence Test to determine within 99% certainty whether the customer’s order size is related to (not independent of) time spent on the web site. In this test we are making a few assumptions that are probably incorrect, but will be made in order to keep everything simple for demonstration purposes. We will assume that each visitor visited the site only once and that no other factors that could have influenced order size were varied during the test.

The 3 Overall Steps in the Chi-Square Independence Test

There are 3 overall steps in the Chi-Square Independence Test. They will be explained in greater detail below and are also shown in the video. These overall steps are:

1) Arrange the sampled data in a Contingency Table.

2) Calculate the Chi-Square Statistic for the Sampled Data.

3) Compare the above Chi-Square Statistic with the Critical Chi-Square Statistic. If the Chi-Square Statistic is greater than the Critical Chi-Square Statistic, we can state that the two attributes of the object are related (are not independent).

Step1) Arrange the Sampled Data in a Contingency Table

The first step after data sampling is to arrange the data on a Contingency Table. The video shows exactly how this is done. In the Contingency Table, all of the 10,000 site visitors sampled are placed into 1 of 9 groups of similar visitors. The Contingency Table has visitor data divided up into 3 columns based on number of items a customer purchased (0, 1, or 2) and into 3 rows based on the time that the customer spent on the web site (0 to 10 minutes, 10 to 20 minutes, and more than 20 minutes). Each of the 10,000 randomly-sampled site visitors will be placed into 1 of the 9 possible groups on this 3 x 3 Contingency Table. Once again, watch the video to get a clear picture of this.

The next part of Step 1) is to create a duplicate Contingency Table which will contain the number of visitors in each of the 9 groups that would have been expected based upon the totals for each row and column on the original Contingency Table. The expected number of visitors in each group is calculated from the following formula: (Total number of visitors in a row) x (Total number of visitors in a column) / (Total overall number of visitors). Watching the video will probably make that calculate easier to visualize.

Here are both the Actual and Expected Contingency Tables:

Step 2) Calculate the Chi-Square Statistic for the Sampled Data
We can now calculate the Chi-Square Statistic for the sampled data and also the Critical Chi-Square Statistic. Both of these values will be compared to determine whether the two attributes are related or not.

Watching the video is the easiest way to comprehend how to calculate the Chi-Square Statistic for the sampled data. Briefly, here is how the calculation is performed. There are two Contingency Tables. The first Contingency Table is a 3 x 3 matrix containing actual data from the customer survey. The 2nd Contingency Table is also a 3 x 3 matrix containing the expected number of customers in each group (each of the 9 cells of the matrix holds the data for each of the 9 visitor groupings).

We will label data from the 9 cells of the original Contingency Table as f0i (that is, f01, f02, f03,…, f09). We will label data from each of the 9 cells of the Expected Value Contingency Matrix as fti (that is, ft1, ft2, ft3,…, ft9).

The Chi-Square Statistic for the sampled data can now be calculated from the following formulas:

Chi-Square Statistic = Sum of (f0i - fti ) / fti as i goes from 1 to 9. Once again, the video provide a clear picture of this calculation. The Chi-Square Statistic for the sampled data in the test we are performing equals 794.3.

Now we need to calculate the Critical Chi-Square Value. The Chi-Square Critical Value is determined by 2 things: 1) the Degrees Of Freedom inherent to the Contingency Table and 2) the required degree of certainty.

The Degrees of Freedom inherent to any Contingency Table is calculated by the following formula:

DOF = (r – 1) x (c – 1) where r = number of rows in the table and c = number of columns in the table. In a 3 x 3 Contingency table, there are 3 rows and 3 columns. In this case, DOF = (3 – 1) x (3 – 1) = 4

We require a 99% Degree of Certainty. Alpha is therefore equal to 0.001.

Alpha = 1 – Required Degree of Certainty = 1 – 0.99 = 0.01

We calculate the Critical Chi-Square Value by plugging the Degree of Freedom and the Alpha into the following Excel formula:

CHIINV(DOF,Alpha) = CHIINV(4,0.01) = 13.28

We now have:

Chi-Square Statistic for the Sample Data = 794.3

Critical Chi-Square Value = 13.28

Step 3) Compare the above Chi-Square Statistic with the Critical Chi-Square Statistic

Compare the Chi-Square Statistic (794.3) with the Critical Chi-Square Value (13.28).

Since the Chi-Square Statistic is greater than the Critical Chi-Square Value, we can state with 99% certainty that the two attributes, order size and time on web site, are related. It would not be correct to state the time on site causes larger orders (although it might), but the two are definitely related. If time on site does not directly cause larger orders, time on site is probably closely related to something that does cause the larger orders.

Please post any comments you have on this article. Your opinion is highly valued!

If You Like This, Then Share It...

Logistic Regression - How To Predict If a Prospect Will Buy Using Logistic Regression in Excel

2010-04-28T08:57:00.000-07:00

Logistic Regression

Analysis in Excel

For Marketing

Wouldn’t it be great if there was a more accurate way to predict whether your prospect will buy rather than just taking an educated guess? Well, there is…if you have enough data on your previous prospects. The tool that makes this possible is called Logistic Regression and can be easily implemented in Excel.

Customer Quality Scores Are Created With Logistic Regression

Marketers use Logistic Regression to rank their prospects with a quality score which indicates that prospect’s likelihood to buy. The more data you’ve collected from previous prospects, the more accurately you’ll be able to use Logistic Regression in Excel to calculate your new prospect’s probability of purchasing.

Here is a video which will show you how to perform Logistic Regression in Excel and why it works. The example that will be presented in the video will also be covered below in the article:

Step-By-Step Video Showing How To Predict if a Prospect Will Buy Using Logistic Regression in Excel:

(Is Your Sound Turned On?)

What is Logistic Regression?

Logistic Regression calculates the probability of the event occurring, such as the purchase of a product. In general, the thing being predicted in a Regression equation is represented by the dependent variable or output variable and is usually labeled as the Y variable in the Regression equation. In the case of Logistic Regression, this “Y” is binary. In other words, the output or dependent variable can only take the values of 1 or 0. The predicted event either occurs or it doesn’t occur – your prospect either will buy or won’t buy. Occasionally this type of output variable also referred to as a Dummy Dependent Variable.

An Example of Logistic Regression In Action

Here is a marketing example showing how Logistic Regression works. The embedded video walks through this example in Excel as well:

Suppose that you have collected three pieces of data on each of your previous prospects. The data you have collected on each prospect was:

1) The prospect’s age
2) The prospect’s gender (1 = Male and 0 = Female)
3) Whether the prospect purchased or not (Did purchase Y = 1, Did not purchase, Y = 0).

Create the Predictive Equation

With the above data, you could create a predictive equation that would calculate a new prospect’s probability of purchasing by inputting this new prospect’s age and gender. This predictive equation will be in the form of:

P(X) = eL/ (1+eL)

P(X) represents the possibility of event X occurring.

The Logit

Event X is a purchase. In other words, P(X) is the probability that Y = 1.

P(X) has only one variable. That is L, which is called the Logit.

The Logit, L = Constant + A * Age + B * Gender

L, the Logit, has 3 variables: Constant, A, and B. They must be known before P(X) can be calculated. Those 3 variables can be found in Excel by using the Excel Solver. The Excel Solver will find the optimal combination of those 3 variables that causes the resulting P(X) to most accurately predict whether Y = 1 or 0 for all previous prospects.

Calculating the Logit Variables - A, B, and Constant

Here’s how the most optimal set of Logit variables (Constant, A, and B) are found in Excel:

Using Excel, each recorded prospect has the following calculation performed:

P(X)Y * [ 1 - P(X) ](1-Y)

The Y refers to Y = 1 if the prospect bought and Y = 0 if the prospect didn’t buy.

The P(X) is the probability of purchase that will be calculated using the equation listed above. In Excel, the P(X) calculation is initially performed by the Excel Solver using Logit variables (Constant, A, and B) which are not optimal. The Excel Solver will then continuously try new combinations of these variables until the optimal P(X) is found.

Optimizing the Logit Variables in the Excel Solver

Here’s how the Excel Solver knows when it has found the correct combinations of these 3 variables so that the resulting P(X) equation most accurately predicts whether Y = 1 or 0:

The equation P(X )Y * [ 1 - P(X) ](1-Y) is maximized when P(X) is most accurate. It approaches it highest value (1) when Y = 1 and P(X) approaches 1. It also approaches its highest value (1) when Y = 0 and P(X) approaches 0. When Y = 1 and P(X) = 1, that is a 100% correct prediction by P(X) that Y = 1. When Y = 0 and P(X) = 0, that is a 100% correct prediction by P(X) that Y = 0.

Each prospect has a separate P(X )Y * [ 1 - P(X) ](1-Y) value calculated for him or her.

The sum of each P(X )Y * [ 1 - P(X) ](1-Y) calculation for all prospects is taken.

The only variables that exist when calculating P(X )Y * [ 1 - P(X) ](1-Y) are Y and the variables of P(X), which are Constant, A, and B. Use the Excel Solver, these variable are adjusted until their values maximize the sum of all P(X )Y * [ 1 - P(X) ](1-Y).

The Final, Most Accurate Predictive Equation

When the sum of P(X )Y * [ 1 - P(X) ](1-Y) is maximized, then the final resulting P(X) equation is as accurate as possible at predicting whether Y will be 1 or 0.

The Excel Solver Dialogue Box

Stated another way, we now have a predictive equation P(X ) which uses the optimal combination of Constant, A, and B which most accurately calculates the probability that Y = 1 given a prospect’s age and gender.

The embedded video provides a clear picture of all of this in action in Excel.

The use of the Excel Solver does require some hand-tweeking to ensure that the most accurate answer is obtained. The video shows an example of this. Ultimately what the Solver is doing is adjusting variables Constant, A, and B to maximize the sum of the column of

P(X )Y * [ 1 - P(X) ](1-Y) equations. The answer obtained by the Solver should maximize that sum and provide realistic answers for the probabilities of each prospect, including the new one.

You'll Have To Tweek the Constraints in the Excel Solver

You’ll probably find that you have to experiment by applying constraints to the variables that Solver is adjusting in order to maximize the target sum. The variables that Solver adjusts are called Decision Variables. Solver allows you to create constraints on the value of any Decision Variable.

Adding a Constraint to the Solver

In the video, you will be able to watch how a Decision Variable is constrained to make the final answer more accurate. The Decision Variable called Constant was constrained to always remain above -25 during the Solver analysis. This resulted in the most accurate and realistic maximization of the sum of the P(X )Y * [ 1 - P(X) ](1-Y) equations.

Conclusion - Incredible Predictor but Not the Simplest Analysis

Logistic Regression is not the simplest type of analysis to understand or perform. Hopefully this article and video have provided a much clearer picture for you.

If you have any comments, questions, suggestions regarding the use of Logistic Regression, your input is welcome and appreciated.

If You Like This, Then Share It...

ANOVA - How To Improve Your PPC Marketing Using All 3 Types of ANOVA in Excel

2010-04-22T09:36:00.000-07:00

ANOVA Test

Done in Excel

To Improve Marketing

The 3 ANOVA functions in Excel can be real power tools for a marketer, if you know how to use them. Let’s take a look at what they can do for you. Below is a video that will show in detail how to use all 3 types of ANOVA that are built into Excel in order to improve your pay-per-click marketing campaigns. In the video, each of the 3 ANOVA types will be applied to the same data set. The video will demonstrate how the data is input, how the ANOVA is run, and how the output is interpretted for each ANOVA type. The article below also discusses the same information. Here is the video:

Step-By-Step Video Showing How To Improve Your Pay-Per-Click Marketing Using All 3 Types of ANOVA Built Into Excel

(Is Your Sound Turned On?)

What Is ANOVA?

Marketers generally use ANOVA to tell them whether changing one element of a marketing campaign actually had an effect on the outcome. ANOVA testing should be applied only when that element being tested has at least three variations. For example, suppose you wanted to test whether a product’s color makes a difference in sales. To answer that question using ANOVA, your product would have to come in at least three colors to run an ANOVA test.

ANOVA, Analysis of Variance, tells the marketer whether or not the test output had enough variance in it to support the claim that varying the tested element actually did make a difference in the output.

ANOVA Output

As is common in most statistical problems, ANOVA’s output does not provide a definitive Yes or No answer but gives the probability that varying an input had an effect on the output. In ANOVA, this probability is called the Degree of Certainty. ANOVA testing requires a Degree of Certainty to be specified up front.

The ANOVA test answers the question of whether varying an element affected the output within a required degree of certainty. The output of an ANOVA might be explained as follows, “Yes, the variance observed in the output was large enough to conclude within 95% certainty that varying the element being tested did affect the output. We can reject the Null Hypothesis which stated that varying the tested element did not have effect on the output.”

The 3 Types of ANOVA Built Into Excel

The first type is called Single-Factor ANOVA. This is used to test only one input element. This is how the data must be arranged on the Excel spread sheet for this type analysis. The yellow-highlighted cells below are those that are selected as the input for Single-Factor ANOVA in Excel.

Excel Data Ready For Input - Single-Factor ANOVA
(Yellow-Highlighted Cells Are the Input Range)

Excel Output of Single-Factor ANOVA

The second type is called Two-Factor ANOVA Without Replication and tests two elements simultaneously. This is how the data must be arranged on the Excel spread sheet for this type analysis. The yellow-highlighted cells below are those that are selected as the input for Single-Factor ANOVA in Excel.

Excel Data Ready for Input
Two-Factor ANOVA Without Replication
(Yellow-Highlighted Cells Are the Input Range)

Excel Output of
Two-Factor ANOVA Without Replication

The third and final type of built-in ANOVA is called Two-Factor ANOVA With Replication. This ANOVA test simply replicates a two-factor test in at least two places in order to test whether interaction between the two factors also has an affect on the output.

Excel Data Ready for Input

Two-Factor ANOVA With Replication

(Yellow-Highlighted Cells Are the Input Range)

Excel Output of
Two-Factor ANOVA With Replication

In Excel, we are going to conduct one experiment and run the output of that experiment through all three types of ANOVA so you can observe the differences in how the data is inserted and how the Excel output is interpreted in each case. The above video shows the output of this experiment being run through all 3 ANOVA tests in Excel and how the different ANOVA outputs are interpreted.

The ANOVA Test We Ran

In a nutshell, the test is conducted as follows: we are testing ads in a pay-per-click campaign to determine whether varying the headline, ad text, or interaction between headline and ad text affects the click-through rate. Click-Through Rate (CTR) of an ad equals the number of ad impressions divided by the number of clicks that the ad generated.

We will use Excel to apply all three types of ANOVA testing to the same set of output data so that we can observe the differences in how data needs to be input into Excel for each ANOVA type and how the Excel output is interpreted for each case.

Single-Factor ANOVA will be applied to the output to determine if the varying the headlines affected CTR. Two-Factor ANOVA Without Replication will applied to the output to determine if varying headlines OR varying the ad text affected CTR. Two-Factor ANOVA With Replication will be applied to the output to determine whether variation in headlines OR ad text OR interaction between headlines and ad text affected CTR.

Once again. viewing the embedded video is probably the easiest way to quickly understand how data is input into Excel and how the Excel output is interpreted for each test.

In the video, all three types of Excel ANOVA are applied to the same set of output data.

Here Is a Summary of the Test That Was Run:

Step 1) Create 5 Sets of Ad Text For 1 Ad Group

5 different sets of ad text were created to be used within a single ad group. Ads within a single ad group should be tightly focused on a single theme, product, and landing page. We tried as much as possible for that to be the case here. One key to successful ANOVA testing to remove all other variation from the test except the specific elements being tested.

Step 2) Create 3 Sets of Headlines for That Ad Group

3 sets of headlines were created to be used in this ad group and were then combined with each of the 5 sets of ad text. This created a total of 15 possible combinations of ad text / headline.

Step 3) Run All 15 Ad Text / Headline Combinations In 1 Ad Group

Each of these 15 combinations of ad text / headline were run on both the Google and Yahoo pay-per-click search networks under similar conditions until 1 million ad impressions on each network were reached for each of the 15 ad text / headline combinations.

Step 4) Prepare the Test Results For Excel ANOVA

The output of this test was recorded as shown in the video. The linked video shows how output data must be recorded for insertion into Excel ANOVA. Each type of ANOVA in Excel requires the input data to be formatted slightly differently.

Step 5) Run All 3 Types of ANOVA On Test Results

Single-Factor ANOVA was applied to the output from Google to determine within 95% certainty whether varying the headline affects the output. Two-Factor ANOVA Without Replication was applied to the Google data to determine whether varying headline or ad text affects the output. Finally, Two-Factor ANOVA With Replication was applied to the output from both Google and Yahoo to determine whether varying headlines, ad text, or the interaction between headlines and ad text affect the output.

Step 6) Evaluate the Excel Output For Each ANOVA

The Excel output from each ANOVA test run in Excel was then interpreted. Each element being tested (headlines, ad text, and interaction) will have a separate P Value that will appear in the Excel output. The P Value for each tested element can be interpreted as being the probability of the observed variance in the output was due only to chance and not the result of varying the tested element.

Here is the output for the Single-Factor ANOVA test we ran:

Note that the P Value associated with Headlines = 0.001307, less that the Alpha of 0.05. We can therefore reject the Headlines Null Hypothesis and state with 95% certainty that varying the Headlines affected Click-Through Rate.

Here is the output for the Two-Factor ANOVA Without Replication test we ran:

Note that the P Value associated with Headlines (in Blue) is 0.00528, which is less that the Alpha of 0.05. However, the P Value associated with Ad Text (in yellow) is 0.627, which is greater than the Alpha of 0.05.

We can therefore reject the Headlines Null Hypothesis and state with 95% certainty that varying the Headlines affected the CTR. We cannot, howver, reject the Ad Text Null Hypothesis and we cannot state with 95% certainty that varying the Ad Text affected CTR.

Here is the output for the Two-Factor ANOVA With Replication test we ran:

Note that the P Value associated with Headlines (in Blue) is 1.18 E-06 which is less that the Alpha of 0.05. The P Value associated with Interaction Between Headlines and Ad Text (in Green) is 0.0402 which is less that the Alpha of 0.05. However, the P Value associated with Ad Text (in yellow) is 0.156, which is greater than the Alpha of 0.05.

We can therefore reject the Headlines Null Hypothesis and state with 95% certainty that varying the Headlines affected the CTR. We can also reject the Interaction Null Hypothesis and state with 95% certainty that varying the Interaction Between Headlines and Ad Text affected the CTR. We cannot, howver, reject the Ad Text Null Hypothesis and we cannot state with 95% certainty that varying the Ad Text affected CTR.

Example of How To Interpret the P Value

For example, if the P Value associated with varying the headline has a value of 0.010, it means that there is only a 1% chance that the variance in the output could have occurred entirely by random chance and not by varying the headline. In other words, there is a 99% chance the varying the headline affected the output. If the degree of certainty that you required for this ANOVA test was 95%, then alpha equals 0.05 (1 – 95% = 0.05).

In the case that the P Value associated with headline equals 0.01, which is smaller than the alpha of 0.05, you can state that you are at least 95% certain that you can reject the Null Hypothesis which states that varying the headline had no effect on the output.

The Correct Interpretation of the ANOVA Test

The correct interpretation of an ANOVA is to state whether or not you can reject the Null Hypothesis (varying the element did not affect the output), not its corollary that you can now accept the Alternate Hypothesis (varying the element did affect the output).

The Big "However"....

However, we’re in business and not in statistics class, so if the P Value associated with an element is less than alpha, go ahead and state that varying the tested element did affect output within your required degree of certainty.

Once again, the embedded video above which will show you exactly how to setup, run, and interpret all three Excel ANOVA tests on the same set of pay-per-click ad data.

In a Nutshell, Use ANOVA in Excel To Find Out If Changes You Made To Your Marketing Campaigns Really Made a Difference.

Please feel free to submit any comments you have on this article. Your input is highly valued!

If You Like This, Then Share It...

ANOVA - Comparing Doing ANOVA in Excel with Doing It By Hand

2010-04-14T21:45:00.000-07:00

ANOVA Test

Done in Excel

Compared To By Hand

ANOVA is something you would do by hand, ONLY if you absolutely had to. I remember being forced to perform these calculations by hand in statistics class when I was getting my MBA. I also remember wondering why in the world we were doing that when we all knew Excel. Still don’t have an answer to that question. It seemed a little like doing multiplication on a slide rule even though a calculator was readily available. My father once showed me his old slide rule and how facile he had become with it. It was almost magic. I never got the urge to figure it out though. Sorry Dad.

The video below shows a statistical procedure called Single-Factor ANOVA (the simplest type of ANOVA) being solved in Excel and then solved by hand. I’m hoping that the torturous hand calculations in the video only serve to more strongly contrast the ease of doing statistics in Excel.

Step-By-Step Video Showing How To Do Single-Factor ANOVA In Excel and Also How To Do It By Hand:

(Is Your Sound Turned On?)

Why Excel Is A Good Starting Point To Teach Statistics

The point of this article and the linked video is to persuade statistics teachers to focus their efforts on using tools like Excel right from the start. Slogging through those hand calculations is almost never a good thing. Probably the fastest way to make a statistics student to seriously hate statistics is to force him or her to do calculation-intensive tasks like regression and ANOVA by hand.

As an Internet marketing manager who does a lot of statistics on the job, I am SOOO glad I learned how to do all of my statistic procedures on Excel. Believe it or not, statistics is actually kind of fun when you have such a convenient tool like Excel (I’ve been called a Propeller Head more than once). There are plenty of other fine statistical tools like Minitab, SPSS, and SAS. But….I (like almost any other business manager) have a pretty good grasp of Excel. Honestly, I don’t really have no desire to learn SAS (or any other statistical software that costs thousands of dollars) when I’ve got Excel. If I can use Excel instead, you bet I’m gonna.

Here’s a little info about the ANOVA test that was run in the above video:

What Is ANOVA?

ANOVA stand for Analysis of Variance. It is a test to determine if three or more variations of each of one or more factors have an effect on a population. ANOVA tests the Null Hypothesis of each factor. The Null Hypothesis of each factor states that varying the factor has no effect on a population. The ANOVA test results in either acceptance or rejection of the Null Hypothesis within a specified degree of certainty.

The single-factor ANOVA test in the linked video evaluates whether different closing methods affect the probability that a sale will close. The Null Hypothesis states that varying the closing method does not affect the number of sales that get closed. All other factors, including the abilities of the individual salespeople, are assumed to be the same.

The Null Hypothesis

Acceptance or rejection of the Null Hypothesis can be determined by either the P Value or the F Statistic obtained by the calculations. Both the P Value and the F Statistic are equivalent to each other and are nearly interchangeable. The video provide a detailed explanation of the following: We accept the Null Hypothesis if the P Value is greater than Alpha (Alpha = 1 – Required Degree of Certainty) or, equivalently, if the calculated F Statistics is less than F Critical. We reject the Null Hypothesis if the opposite is true. Rejection of the Null Hypothesis implies that variation of the associated factor did affect the outcome.

Doing ANOVA By Hand vs. By Excel

Doing ANOVA in Excel takes just a few seconds with little possibility of error if the data is inserted correctly. Doing ANOVA by hand takes a LONG time and has LOTS of opportunities for error. Here, the above video of step-by-step ANOVA video will, hopefully, will convince you of that.

Here Is the Original Problem to Be Solved With Single-Factor ANOVA:

A group of 4 salespeople used a different closing method exclusively each week for 3 weeks. The sales totals for each salesperson using each method are shown above. We need to determine within 95% certainty whether varying the closing method affected sales numbers or not. No other factors were varied during the 3-week duration of this test.

Here is the Problem Solved in Excel in One Step:

The Excel output shows the P Value associated with the closing methods to be 0.0144. This is significantly less than the alpha of 0.05, so we can reject the Null Hypothesis and state with 95% that varying the closing method did affect sales totals. Remember, it took less than 10 seconds to input the data from the excel spread sheet and get the above output. Compare this to doing the same problem by hand below and arriving at the same answer.

Now, Here is The Same Problem Done By Hand

Yup, same answer as Excel, but now I've got a headache!

Hopefully This Article Touched a Nerve...

Hopefully this article touched a nerve with the poor folks out there who were forced to do ANOVA by hand in statistics class. There might even be a few statistics teachers who would rather have taught ANOVA in Excel than having had to do it by hand in front of the class room. I've had to teach ANOVA by hand to a class or two and it wasn't the funnest thing I've ever done.

If you agree or disagree with this article, let the world know with your comments below. Your input is highly valued!

If You Like This, Then Share It...

ANOVA - How To Increase Click-Through Rate Using ANOVA in Excel

2010-04-05T23:57:00.000-07:00

ANOVA Test

Done in Excel

For Better PPC Marketing

If you have ever run a pay-per-click campaign, you’ve probably wondered which factors really made a difference in the click-through rates. Are the headlines really making a difference in CTR? Does the ad text have any affect on CTR? What about the interaction between ad text and headline?

The good news is that there is a statistical tool in Excel designed just for a test like that. The tool is called ANOVA: Two Factor With Replication. Here is a video which shows exactly how to perform this test.

Step-By-Step Video On How To Increase Your Click-Through Rate With ANOVA in Excel

(Is Your Sound Turned On?)

FREE DOWNLOAD - Click Here to Download the Excel Statistical Graphing eManual. That's 100+ Pages of Videos and Lessons of How to Graph All Major Statistical Distributions in Excel.

We Used ANOVA To Test for the Following

To sum up this test, we are determining whether one or both of two factors (headline and ad text) and/or the interaction between the two affected click-through rate. We are replicating the same test in two environments: on Google and also on Yahoo pay-per-click advertising programs. The replication creates the opportunity to evaluate of whether interaction between Headline and Ad Text affected CTR.

What Is ANOVA?

ANOVA, Analysis of Variance, is a test to determine if three or more different methods or treatments have the same effect on a population. The basic test of ANOVA is the Null Hypothesis, which states that varying a factor has no effect on the output. Each factor and the interaction between the two factors has its own separate Null Hypothesis.

The Null Hypothesis connected with the headlines states that choice of headlines has no effect on the measured output, the click-through rate. The null Hypothesis connected with the ad text states that choice of headlines has no effect on the output. The Null Hypothesis connected with the interaction between headline and ad text states that this interaction has no effect on the click-through rate.

Here's How We Ran Our ANOVA Test

The test was run as follows: 3 headlines and three sets of ad text were created. Altogether there were 9 possible combinations of headline / ad text. All 9 ads were run for approximately equal number of times on both the Google and Yahoo paid search networks. The Excel tool: ANOVA: Two Factor With Replication will then be run on the results to determine with at least 95% certainty whether headline, ad text, and/or their interaction had an effect on the click-through rate.

This is how the data needs to be organized on the Excel spread sheet. The yellow-highlighted cells are the input data for the Excel ANOVA.

The above video shows the Click-Through Rates results and also how to insert the data into Excel so the ANOVA test can be run on the data.

How To Interpret the ANOVA Output From Excel

The Excel output of the test is fairly simple to interpret. The linked video above shows exactly how that is done. In a nutshell, a factor (headline, ad text, or interaction between the two) is said to affect the output (click-through rate) if the P Value associated with that factor is less than the alpha. The alpha is derived from the level of certainty required. Alpha = 100% - Level of Certainty. For example, if a 95% level of certainty is required, then the alpha = 0.05 (100% - 95% = 5% or 0.05). The P Value associated with a factor equals the probability that the output occurred by chance.

The P Value Rule

As a general rule, if the P Value of a factor is less that the alpha, then we can reject that factor’s Null Hypothesis, which states that varying the factor has no effect on the output.

On the other hand, if the P Value of a factor is greater than the alpha, we cannot reject that factor’s Null Hypothesis. We cannot say that varying the factor had an affect on the output.
In this case, we are able to state with 95% certainty that the headlines and interaction affected the output but not the ad text.

If you have any comments on this article, your input is highly valued!

If You Like This, Then Share It...

Regression - The Two Crucial Steps to Excel Regression That Most People Skip

2010-03-24T19:45:00.000-07:00

Regression Analysis

Done in Excel

2 Most Important Steps

Running a Regression in Excel is fairly easy. So is running one incorrectly. There are two crucial steps that should always be performed on the data before any Regression should be run. Fortunately these two steps are very quick and easy to do in Excel. They are:

1) Graph the Data
2) Run Correlation Analysis On All Variables

Here is a video of this article showing how to perform all four steps to Regression in Excel, including the above two crucial steps at the beginning:

Step-By-Step Video Showing How To Do All 4 Steps of Regression in Excel, Including the 2 Crucial Initial Steps That No One Does, But Should

(Is Your Sound Turned On?)

Why You Need To Run The 2 Crucial Steps Before Doing Regression

Here’s why you need to run the two crucial steps prior to regressing any data in Excel:

Crucial Step 1) Graphing the Data

Whether or not you are using Excel to run a Regression, you should always graph the data before doing anything else. Eyeballing the data will allow you to quickly determine whether there is any relationship between the independent (input) variables and the dependent (output) variable. You also want to evaluate whether the graph generally appears to be linear or possibly quadratic. Excel’s Regression Tool works well only for reasonably linear data. Eyeballing the data upfront will tell you very quickly whether Excel’s Linear Regression is the right tool for the job.

Graphing The Data To Check If It Is Linear

The input and output variables will be graphed together. The y-axis of the chart will provide the scale for plotting of those values. The x-axis will provide a measure of whatever continuum was used, e.g. time, to collect the values of all of the variables. Excel’s charting function is the way to go here. The above linked video shows exactly how to chart all the data in Excel.

Crucial Step 2) Running Correlation Analysis on All Variables Simultaneously

There are two good reasons for doing this. First, we want to remove any input variables which are clearly not good predictors of the output variable. Second, we want to make sure that none of the input variables have a high correlation with (are good predictors of) other input variables.

Running Correlation Analysis on the Data To Prevent Collinearity and also To Remove Input Variables That Have Low Correlation With the Output Variable

Correlation of multiple variables is easily done in Excel using the Correlation Data Analysis tool. The linked video shows exactly how to do that.

Remove Input Variables That Have Low Correlation With Output Variable

After you have run Correlation Analysis on the data, you will want to remove any input variables that have a low correlation with the output variable. A Correlation Coefficient of with an absolute value of less than 0.4 (between -0.4 and +0.4) between the output variable and an input variable indicates that the input variable is not a good predictor of the output. That input variable should be removed from the Regression Analysis. The attached video provides an example of this.

Data Columns Before Removing Input Variable With Low Correlation To Output

Data Columns After Removing Input Variables With Low Correlation To Output

Remove Inputs Variables Highly Correlated With Other Input Variables

After looking at the Correlation Coefficients between the input and output variables, look at the Correlation Coefficients between the input variables themselves. You do not want to use pairs of input variables that are good predictors of each other in a Regression. This will cause a Regression error known as Collinearity or Multicollinearity. One variable from any pair of highly-correlated input variables should be removed prior to running the Regression Analysis. Variables can be considered highly-Correlated if the absolute value of their Correlation Coefficient is greater the 0.7 (greater than +0.7 or less than -0.7).

Adding New Input Variables To The Regression Analysis

Here are a few hints about adding new input variables to a Regression Analysis. First, build up a Regression by starting with a small number of input variables and add any new ones one at a time. Second, good new input variables noticeably increase Adjusted R Square and also lower Standard Error without significantly changing the existing Regression Coefficients.

When you are satisfied with the output of the data graph and the Correlation Analysis, go ahead and run the Regression with Excel. An example of how to do this is shown in the above video.

The Excel Regression Dialogue Box

The final step of Excel Regression is Analysis of the Excel output. Here is a link to another video which shows you how to quickly read the most important parts of the Excel Regression output: http://bit.ly/Quickly-Understanding-Excel-Regression-Output

Excel Regression Output With Color Coding Added

Conclusion - Plotting the Data and Running Correlation Can Be BIG Time Savers

Plotting the data and running Correlation Analysis prior to running a Regression can save you lots of time that you might otherwise have to spend making adjustments to your Regression after running it.

If you have any comments about this article, feel free to post them right here. Your input and opinions are highly valued!

If You Like This, Then Share It...

Regression - How To Quickly Read the Output of Excel’s Regression

2010-03-18T22:47:00.000-07:00

Regression Analysis

Done in Excel

How To Read the Output

There is a lot more to the Excel Regression output than just the regression equation. If you know how to quickly read the output of a Regression done in, you’ll know right away the most important points of a regression: if the overall regression was a good, whether this output could have occurred by chance, whether or not all of the independent input variables were good predictors, and whether residuals show a pattern (which means there’s a problem).

Excel Regression Output With Color-Coding Added

This video will illustrate exactly how to quickly and easily understand the output of Regression performed in Excel:

Step-By-Step Video About How To Quickly Read and Understand the Output of Excel Regression

(Is Your Sound Turned On?)

The 4 Most Important Parts of Regression Output
1) Overall Regression Equation’s Accuracy
(R Square and Adjusted R Square)

2) Probability That This Output Was Not By Chance
(ANOVA – Significance of F)

3) Individual Regression Coefficient and Y-Intercept Accuracy

4) Visual Analysis of Residuals

Some parts of the Excel Regression output are much more important than others. The goal here is for you to be able to glance at the Excel Regression output and immediately understand it, so we will focus our attention only on the four most important parts of the Excel regression output.

1) Overall Regression’s Accuracy

R Square– This is the most important number of the output. R Square tells how well the regression line approximates the real data. This number tells you how much of the output variable’s variance is explained by the input variables’ variance. Ideally we would like to see this at least 0.6 (60%) or 0.7 (70%).

Adjusted R Square – This is quoted most often when explaining the accuracy of the regression equation. Adjusted R Square is more conservative the R Square because it is always less than R Square. Another reason that Adjusted R Square is quoted more often is that when new input variables are added to the Regression analysis, Adjusted R Square increases only when the new input variable makes the Regression equation more accurate (improves the Regression equations’s ability to predict the output). R Square always goes up when a new variable is added, whether or not the new input variable improves the Regression equation’s accuracy.

2) Probability That This Output Was Not By Chance

Significance of F – This indicates the probability that the Regression output could have been obtained by chance. A small Significance of F confirms the validity of the Regression output. For example, if Significance of F = 0.030, there is only a 3% chance that the Regression output was merely a chance occurrence.

3) Individual Regression Coefficient Accuracy

P-value of each coefficient and the Y-intercept – The P-Values of each of these provide the likelihood that they are real results and did not occur by chance. The lower the P-Value, the higher the likelihood that that coefficient or Y-Intercept is valid. For example, a P-Value of 0.016 for a regression coefficient indicates that there is only a 1.6% chance that the result occurred only as a result of chance.

4) Visual Analysis of Residuals

Charting the Residuals

The Residual Chart

The residuals are the difference between the Regression’s predicted value and the actual value of the output variable. You can quickly plot the Residuals on a scatterplot chart. Look for patterns in the scatterplot. The more random (without patterns) and centered around zero the residuals appear to be, the more likely it is that the Regression equation is valid.

There are many other pieces of information in the Excel regression output but the above four items will give a quick read on the validity of your Regression.

If anyone has any comments or observations related to this article, feel free to submit them because your input and opinions are highly valued.

If You Like This, Then Share It...

Work-Arounds for Excel 2003 and Excel 2007's Biggest Statistical Omissions

2010-03-14T17:21:00.000-07:00

Just about anyone who has used Excel for any extended period will have a wish list of additions for Microsoft’s next version of Excel. There are two items that would probably top the list as glaring omissions for many statisticians. I really couldn’t believe it when I searched for them several years ago in Excel 2003. They are missing from Excel 2007 but you will find them in the Excel 2010 Beta that is available for download at the time of this writing (to access it, just Google “Microsoft Office 2010 Beta”).

This video goes into detail about these Excel omissions and shows the work-arounds for each:

Step-By-Step Video About Work-Arounds For the Biggest Statistical Omissions of Excel 2003 and 2007

(Is Your Sound Turned On?)

These two surprising omissions are the functions to calculate the Probability Density Function for both the t Distribution and the Chi-Square Distribution. The PDFs of the t and Chi-Square Distributions have a lot of important uses in statistics. The PDF of the t Distribution with all of its uses in small sample sizes is particularly important. If you ever want to graph the PDF of either of these two functions in Excel, you need a way to calculate the PDF in Excel.

This attached video above shows exactly how to create the work-arounds in Excel.

Since I really needed to use the Probability Density Functions of both the t and Chi-Square distributions, I had to build their formulas in Excel from scratch. Those formulas are pretty ugly. Fortunately you won’t have to deal unfriendly mathematics like that because those two formulas have been translated into usable Excel the above video:

The t Distribution

t Distribution's PDF Formula in Excel

Graph of the t Distribution's Probability Density Function

Chi-Square Distribution

Excel Formula for the Chi-Square Probability Density Function

User-Interactive Excel Graph of the Chi-Square PDF

The following 2 videos show these Excel formulas put to use creating user-interactive Excel graphs of these two distributions.

The t Distribution graphing video is:
http://bit.ly/Video-Graph-t-Distribution-PDF-in-Excel

The Chi-Square Distribution graphing video is:
http://bit.ly/Video-How-To-Graph-the-Chi-Square-Distribution-PDF-in-Excel

You’ll find that you’ll learn a lot about the distributions by varying the parameters and watching these changes reflected in the graphs. The Chi-Square Distribution curve is somewhat unique in that it resembles a wave rolling from left to right as its only parameter, the degrees of freedom (ѵ), is increased. The effect is shown in the video.

If you are a statistician who uses Excel, you now have two more tools at your disposal: functions to calculate the Probability Density Functions of both the t Distribution and the Chi-Square Distribution. These two distributions have lots of valuable uses in business. Here are some of the main ones:

The t Distribution is one of the main statistical tools for analyzing small samples. The Chi-Square Distribution is often used to determine whether two attributes of one object are independent from each. An example of this use might be to answer the question of whether time spent on a web site is related to the amount spent.

The Chi-Square Distribution is also useful in determining if there has been a change in the variance of a population. You might, for example, want to know the degree of certainty that you can state that your customers have tightened up their spending and are now focused more on purchasing certain items. These will be topics of later blog articles so stay tuned.

Feel free to submit any comments, suggestions, or ideas related to this article. Your feedback is definitely welcome. If you know if any other significant statistical shortcomings in Excel 2003 or 2007, please let us know in a comment. Your input and opinions are highly valued!

If You Like This, Then Share It...

How To Build a Much More Useful Split-Tester in Excel Than Google's Website Optimizer

2010-03-09T14:24:00.000-08:00

A Better Split Tester

Done in Excel Than

Google Website Optimizer

Google AdWords’ Website Optimizer is a great tool to run split-tests on landing pages in your AdWords account. You can, however, easily create a much more versatile split-tester with Excel that produces exactly the same result as the Website Optimizer but is much simpler to use.

This article and the video below will provide specific instructions on how to produce a split-tester in Excel that produces exactly the same result as the Website Optimizer because it runs the same statistical test (a one-tailed, two-sample, unpaired hypothesis test of proportion), but the Excel Split-Tester can be used in almost any marketing situation that would employ split-testing in any general way.

The Optimizer is a tool built into Google AdWords and can only be used in that medium.

Step-By-Step Video About How To Create an Excel Split-Tester That Is Much More Useful Than Google's Web Site Optimizer

(Is Your Sound Turned On?)

Google Web Site Optimizer in Action

Close-Up of the Above Screen

Excel Split-Tester Performing the Same Comparison

This is also the same Statistical test that the Excel Split-Tester performs

Example of Everyday Marketing Use of the Excel Split-Tester

The Excel split-tester that you’ll make can be applied to almost any marketing situation. For example, you could easily use your Excel split-tester to determine whether a change made to a direct mail campaign really made a difference.

The Google Website Optimizer runs the statistical hypothesis test on the number of clicks and number of conversion that each landing page elicits. The hypothesis test calculates the probability that the result obtained – one landing page having a higher conversion rate than the other – is true and not just the result of random luck.

How I Use the Excel Split-Tester

I use this Excel split-tester all the time in my job as an Internet marketing manager and I really enjoy its ease of use. All I have to do is plug a couple numbers right out of my AdWords account into the Excel split-tester and I have my answer in a second. The Excel split-tester has none of the set-up requirements that there are inherent with the Google AdWords Website Optimizer.

When I use the split-tester to test AdWords landing pages against each other, I will normally conclude that one landing page converts better when the split-tester states there is at least an 80% chance that the result obtained - one conversion rate is higher than the other – is a real and not just a chance occurrence.

The above video also provides a statistical derivation of the functionality of the split-tester.

Conclusion - It's the Greatest Thing Since Sliced Bread for the Marketer

If you are a marketer, you will get a lot of great use out of this extremely versatile and powerful tool.

Feel free to provide any comments to this article. Also, if you have any other ways of using Excel to optimize your AdWords account, let us know. Your input and opinions are highly valued!

If You Like This, Then Share It...

Regression - How To Do Conjoint Using Dummy Variable Regression in Excel

2010-03-09T14:14:00.001-08:00

Conjoint Analysis

Done in Excel

w/ Dummy Variable Regression

Conjoint Analysis is a great tool for marketers. Conjoint analysis quantifies how desirable each product attribute choice is relative to the other available choices for a single product. In other words, the marketer learns which product choices a consumer values most and by how much. In this article and the linked video, you will learn exactly how to perform Conjoint Analysis in Excel using Dummy Variable Regression. That may sound like advanced stuff but it’s really quite a bit simpler than you might imagine.

This video will make the entire procedure of doing Conjoint Analysis in Excel much easier to understand:

Step-By-Step Video Showing How To Perform Conjoint Analysis Using Dummy Variable Regression in Excel In Order To Find Out Which Product Attributes Your Customers Value The Most

(Is Your Sound Turned On?)

The ultimate objective of Conjoint Analysis is quantify the consumer’s degree of liking for each of the choices for one product. The “Utility” of an attribute is the value associated with the consumer’s degree of liking for that choice.

The 6 Steps of Performing Conjoint Analysis

A brief explanation of how Conjoint Analysis and Dummy Variable Regression are used together to arrive at the Utility for each product attribute is as follows below and also in the linked video above:

Step 1) List All Product Attributes For 1 Product

The marketer lists all of the available choices that a consumer has for one product. The marketer starts by listing all of the overall attribute categories such as color and add-ons. The marketer then lists all of the available choices within each attribute category. For example, here the marketer would be listing all available colors and add-ons.

List Of All Product Attributes

Step 2) Make a List of All Possible Combinations of Those Attributes

The marketer then creates a list of all possible combinations of choices available to the consumer for that one product.

Step 3) Have Consumer Rate Each Attribute Combination

This list of all possible combinations is handed to the consumer. The consumer rates each combination on a scale of 1 (least desirable) to 10 (most desirable).

Step 4) Prepare Completed Survey for Regression

The survey results are arranged so that Dummy Variable Regression can be run on them. Each product choice is assigned its own Dummy Variable and one Dummy Variable from each overall attribute category is removed. This will be explained below and also in more detail in the linked video.
Dummy Variables in a regression are variables that can only assume two values. One Dummy Variable must be created for each product choice.

Dummy Variables to Be Removed From Input Data To Prevent Collinearity

Step 5) Run Regression in Excel

Dummy Variable Regression is then run on the survey results data.

Step 6) Derive Attribute Utilities From Regression Output

The Utility for each product attribute is derived directly from the coefficients of the resulting regression equation.

Excel Regression Output

How To Derive The Utilites From the Output

An Example of Using a Dummy Variable

For example, if the product comes only in the colors red and white, There will be a Dummy Variable for red and one for white. The Dummy Variable for the color red can take values of only 1 or 0 because the product will either be red or not. The same applies for the white Dummy Variable, and all other dummy variables.

When the survey is returned, the survey data is converted into the proper layout for the Regression function in Excel. Each Dummy Variable assigned to a specific attribute will be assigned the value of 0 or 1, depending on whether that attribute was an element of the combination that is currently being rated. Watching this done in the linked video is probably the easiest way to understand how to do it.

The Problem of Collinearity - and How To Solve It

One problem can occur when Dummy Variables are inputs to a regression. The problem of Collinearity or Multicollinearity occurs when any independent variable can be used to predict the value of any other independent variable. For example, if the product comes in only red or white, you can predict whether the product is red if you know whether or not the product is white. This is Collinearity.

Collinearity and Multicollinearity are corrected by removing one Dummy Variable from each choice category. For example, if color choices are red or white, the Dummy Variable for one of those colors would be removed. Collinearity is then solved. You cannot predict whether of not the product is red if you do not know whether the product is white (because the Dummy Variable for white has been removed).

The data can now be run as a regular regression using Excel’s regression tool. The linked video shows how to do this in detail.

The regression is run and a regression equation is obtained.

The Product Utilities - The Measure of Customer Liking

The “Utilities” of each of the product choices are set to equal the value of the coefficients of the regression equation. The “Utility” is the degree of liking that the consumer attached to that product choice.

For example, the marketer will find out how important the color red was compared to each of the other product choices during the purchase decision. Utilities of product choices that were associated with the Dummy Variables that were removed to prevent collinearity will be assigned the value of 0.

We now have Utilities for each attribute. Now, the overall attractiveness of a particular combination of choices can be calculated by adding up the individual Utilities associated with the each of the choices. The sum of the Utilities for each combination is the regression’s prediction of consumer’s degree of liking for that combination of product choices.

The removal of the individual Dummy Variables does not affect the accuracy or completeness of the answer. Adding up the Utilities for each combination will produce a figure that will be very close to the consumer’s actual rating for that combination. An example of this is shown in the video.

Showing the Regression Equation Predicts Nearly the Same Score as the Customer's Ranking of Card 13, Even Though Dummy Variables Were Removed

Conclusion - Wow, That Was Easy !

That was easier than you thought it'd be, huh? Feel free to provide any comments to this article. Your input and opinions are highly valued!

If You Like This, Then Share It...

Excel Master Series Blog

Use the Excel t Test To Find Out What the Best Days To Sell Are

The t Test in Excel Can Determine What Your Best Sales Days Are

t Test - General Description

t Test for Two Samples Having Unequal Sizes and Variances

The Higher the t Value - The More Likely the Groups Are Different

The Lower the Combined Variance, the Higher the t Value

A Little Bit More About This t Test

Two-Tailed t Test Is More Stringent

One-Tailed t Test Is Less Stringent

Nonparametric Tests - Completed Examples in Excel

Nonparametric Tests Done in Excel

The Sign Test

Mann-Whitney U Test

Kruskal-Wallis H Test

The Spearman Rank Correlation Coeffcient Test

Nonparametric Tests - How To Do The 4 Most Important Ones in Excel

Nonparametric TestsThe Basics in Excel

- The Sign Test - The Mann-Whitney U Test - The Kruskal-Wallis H Test - The Spearman Correlation Coefficient Test

The Sign Test

How The Mann-Whitney U Test works:

Kruskal-Wallis H Test

How the Kruskal-Wallis H Test Works

Spearman Rank Correlation Coefficient Test

The Spearman Rank Correlation Coefficient Is Calculated As Follows:

Nonparametric Tests - When Should The Marketer Use Them

Nonparametric TestsWhen To Use Them in Marketing

When To Use Nonparametric Tests

These nonparametric tests will include: - The Sign Test - The Wilcoxon Signed Rank Test - The Mann-Whitney U Test - The Kruskal-Wallis H Test - The Spearman Correlation Coefficient Test

t Tests - How To Use the t Test in Excel To Find Out If Your New Marketing Worked

The t Test Simplified and Done in Excel

t Test - General Description

Two-Sample, Paired t Test

The t Test - What Is It?

The Higher the t Value - The More Likely the Groups Are Different

The Lower the Combined Variance, the Higher the t Value

A Little Bit More About This t Test

Two-Tailed t Test Is More Stringent

One-Tailed t Test Is Less Stringent

Doing The Paired Two-Sample t Test in Excel

One-tailed Test

Two-Tailed Test

Hand Calculation of the t Value and p Value

A Quick Normality Test Easily Done In Excel

The Normality Test Simple and Done in Excel

The Normality Test The Most Basics Ones

The Histogram - The Simplest Normality Test

The Normal Probability Plot - A Simple, Quick Normality Test for Excel

Statistical Mistakes You Don't Want To Make

Common Statistical Errors

1) Assuming that correlation equals causation

2) Not graphing and eyeballing the data prior to performing regression analysis

3) Not doing correlation analysis on all variables prior to performing regression

4) Adding a large number of new input variables into a regression analysis all at once

5) Applying input variables to a regression equation that are outside of the value of the original input variables that were used to create the regression equation

6) Not examining the residuals in regression

7) Only evaluating r square in a regression equation

8) Not drawing a representative sample from a population

9) Drawing a conclusion without applying the proper statistical analysis

10) Drawing a conclusion before a statistically significant result has been reached

11) Analyzing non-normal data with the normal distribution

12) Not removing outliers prior to statistical analysis

13) Not controlling or taking into account other variables besides the one(s) being testing when using the t test, ANOVA, or hypothesis tests.

14) Using the wrong t test

15) Attempting to apply the wrong type of hypothesis test

16) Not using Excel

17) Always requiring 95% certainty

18) Thinking it is impossible to get a statistically significant sample if your target market is large

19) Not taking steps to ensure that your sample is normally distributed when analyzing with the normal distribution

20) Using covariance analysis instead of correlation analysis

21) Using a one-tailed test instead of a two-tailed test when accuracy is needed

22) Not using nonparametric tests when analyzing small samples of unknown distribution

How To Solve ALL Hypothesis Tests in Only 4 Steps

Every Hypothesis Test Done in Excel in 4 Steps

Hypothesis Test Determines if Something Changed

Hypothesis Test - Solved With 4-Step Framework

Hypothesis Test Must 1st Be Classified

1) Mean Testing vs. Proportion Testing

2) One-Tailed vs. Two-Tailed Testing

3) One-Sample vs. Two Sample Testing

The t Test in Excel

Can Determine What Your

Best Sales Days Are

t Test for Two Samples Having
Unequal Sizes and Variances

Nonparametric Tests

Done in Excel

Nonparametric Tests

The Basics in Excel

- The Sign Test
- The Mann-Whitney U Test
- The Kruskal-Wallis H Test
- The Spearman Correlation Coefficient Test

Nonparametric Tests

When To Use Them in Marketing

These nonparametric tests will include:

- The Sign Test

- The Wilcoxon Signed Rank Test

- The Mann-Whitney U Test

- The Kruskal-Wallis H Test

- The Spearman Correlation Coefficient Test

The t Test

Simplified and Done in Excel

The Normality Test

Simple and Done in Excel

The Normality Test

The Most Basics Ones

The Normal Probability Plot -
A Simple, Quick Normality Test for Excel

Every Hypothesis Test

Done in Excel in 4 Steps

How To Twitter Better

With a Histogram in Excel

Chi-Square Population

Variance Test in Excel

for Marketing

Normal Distribution

Creating an Interactive

Graph in Excel

Chi-Square Independence

Test in Excel

For Marketing

Logistic Regression

Analysis in Excel

For Marketing

ANOVA Test

Done in Excel

To Improve Marketing

ANOVA Test

Done in Excel

Compared To By Hand

ANOVA Test

Done in Excel

For Better PPC Marketing

Regression Analysis

Done in Excel

2 Most Important Steps

Regression Analysis

Done in Excel

How To Read the Output

A Better Split Tester

Done in Excel Than

Google Website Optimizer

Conjoint Analysis

Done in Excel

w/ Dummy Variable Regression