Below is a macro that removes names with #REF errors. This is an
example of a simple macro that solves a problem in Excel 2003 and earlier
versions.

Range names can be corrupted if a cell that they refer to
gets deleted. This doesn't mean that the cell value gets deleted, but the cell
itself is removed from the sheet.

If a range name refers to cell A1 and you delete
the whole of column A, then the name will display the #REF error because the
cell it referred to no longer exists.

Excel 2007 and 2010 both allow you to delete multiple names
at once. You can open the Name Manager (Ctrl + F3) and then sort by Value by
clicking the Value heading and then select all the names with a #REF value and click
Delete. It's a fairly easy process.

Not so in Excel 2003. It's a boring, time consuming task,
because you can't sort names by value and you can only delete names one at a
time.

The macro below will remove all range names that have the
#REF error in the blink of an eye. The macro asks if you want to continue
before running. It works in all versions.

Sub DeleteAllNamesWithREFError()

'deletes all the names in the active workbook with a #REF
error

'confirms before running

Dim N As Name

    If
MsgBox("Are you sure?", vbYesNo + vbDefaultButton2, "Confirm
macro") = vbNo Then Exit Sub

    For Each N In
ActiveWorkbook.Names

        If
InStr(N.Value, "#REF") >0 Then N.Delete

    Next N

End Sub

The attached file has the macro and some names with errors.
Clicking the button will remove the names with errors.

Popularity: 1% [?]

If you need to get existing data into Excel, I can guarantee you the last thing you’d ever want to do is retype it. And copying from another program or from the web and pasting directly into a cell in a workbook might leave you with nightmarish looking results.

Luckily for you, there’s a much better way to get that data into Excel. Importing! Read on to learn about the Excel Text Import Wizard.

…(read more)

Popularity: 1% [?]

I just emerged from a marathon 33-day writing session where I finished five book manuscripts. One of those books is about a hot new add-in for Excel 2010 called PowerPivot.

For an example of the types of data that you can mash up with PowerPivot, take a look at my guest blog post at PowerPivotPro.com.

And, if you happen to be reading this before February 14, please consider voting for my entry here.

Popularity: 1% [?]

On the Contextures blog, Gregory suggested using the WordPress plugin, CodeColorer, for formatting the Excel VBA code examples.

In Twitter, Dick Kusleika said that he’s using CodeColorer too, and it works well. I decided to test the plug-in here, before installing it on the Contextures blog.

The CodeColorer feature list looks promising, especially these items:

• syntax highlighting of code in comments
• code protect from mangling by WordPress (for example, quotes, double-dashes, etc would look just right as you entered)

Installing the CodeColorer Plugin

The installation was quick and easy, using the WordPress dashboard. After activating the plugin, I went to the its settings page, to see what could be adjusted. What’s the fun in having a new toy, if you don’t try to break it?

There is a preview of the code, at the bottom of the settings panel. I changed the Theme, from the default of Slush and Poppies(!) to Vibrant Ink. The preview only changed after I clicked the Save Options button.

Yikes! That’s not the look I’m going for.

Twitlight (yes, TwiTlight), looked about the same. At the other end of the code formatting scale, Dawn is a bit too subtle.

So, back to Slush and Poppies it is. Is it just me, or would Slush and Puppies be a better name?

The Formatted Code

Here’s the snippet of code that I copied from Excel VBA.

And this is how it looked in Excel

Formatted Code in Comments

The CodeColorer plugin is supposed to format the code in the comments too. You can test it here, and please let me know if you have any trouble, or suggestions.

To format your code in the comments, use this syntax:

Thanks!

______________

Popularity: 1% [?]

The INDIRECT function is another one of those badly named,
but incredibly powerful Excel features. See how to do the impossible with it.  

Here's the problem.

• we have 12 sheets (one for each month)
• the sheets are named with the full month name
• the sheets are identically laid out
• on a summary sheet we need to extract the value
  from the same three cells in each month sheet (36 cells in total)

There is no easy way to do this. One of the less difficult
ways to do this involves the INDIRECT function.

(I have attached an example of the solution to this blog -
see the bottom of the blog. The sheets only have values in the thee cells required.)

The INDIRECT function allows you to build a reference to a cell
or range using text. The text can come from other cells and this is what makes
the INDIRECT function so flexible.

Excel converts the text between the brackets of the
INDIRECT function into a cell reference or range
reference.

In our example we can create a single formula that we can
drag down and across to create our links to the 12 sheets. I know of no other
way to create such a formula without resorting to VBA (macros).

The INDIRECT function can take a little bit of practice to
get used to and the easiest way I have found to build the cell references is to
first create a normal cell reference and use that as the basis to create the text version.

See the image below, which is the summary sheet that we will
build.

The sheet names are listed in the B column and the cell references that
we require are shown in row three. The INDIRECT function will allow us to
combine the sheet name and the cell reference to create an actual link to that
cell.

To see the structure of the cell reference we require we
will create a normal link to another sheet. In cell C4 press the equals key and
click on the July sheet and select cell B2. Then press enter.

Examining the reference created, we can see the layout that
we need to replicate using the INDIRECT function.

=July!B2

We do not need the = sign but we will need to create everything else in the cell reference. We
will use the & symbol to join the text together to create the
above cell reference.

A simple  formula in
cell C4 that will work is

=INDIRECT($B4&"!"&C$3)

But I recommend you use

=INDIRECT("'"&$B4&"'!"&C$3)

The "'"&$B4&"'!"&C$3 creates
the text reference 'July'!B2 and the INDIRECT function instructs Excel to convert this into a normal cell reference. The second example uses single inverted
commas. This handles sheet names that contain spaces.

This formula can be copied across and down to complete the
table.

The first INDIRECT formula above will work in this situation,
but does not handle spaces in sheet names. The second INDIRECT formula works in
all situations and I recommend you use it just in case spaces are added to the
sheet names in the future.

Any spelling mistakes in the sheet names listed in column B
will result in an error message being displayed.

Changing one of the cell references in row three will
extract that cell reference from each sheet. So this table is easy to expand
simply by adding more cell references in row three and copying the formula
across.

I've added a total row and a check row to ensure that the
table is correct. The check row uses a 3D SUM formula that I discussed in an
earlier blog. The formula in C17 is

=SUM(July:June!B2)

Warning:

The INDIRECT is a volatile function. This means that
EVERYTIME Excel calculates the INDIRECT will re-calculate – whether it needs to
or not. Most Excel functions are not volatile and will only calculate if they
need to. Hence, if you use a lot of INDIRECT functions it will impact your calculation
speed. This is less of an issue with today's fast PC's.

Popularity: 1% [?]

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 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.

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.

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:

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:

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:

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

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.

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.

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: 

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:

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!

 

Popularity: 1% [?]

I recently completed a project where the client asked me to design a pie chart which can be filled with colors according to selections made by the user. Each pie slice represents a task which the user had to complete. Once the user marks a task as “Completed” the pie slice should be filled with color, otherwise the slice should be white. Each task has a different importance and the client wanted the pie slices to be sized accordingly.

In my example there are 10 tasks. The data is arranged in columns as follows:

• Column A – The list of tasks.
• Column B – The status set by the user for each task.
• Column F – Relative importance of each task (in percentage). Determines the size of the slices.

I added a pie chart with column A as the axis labels and column F as the values series. I formatted the data series in the chart to have no fill. Then I added the following code in the sheet module where the chart resides:

Private Sub Worksheet_Change(ByVal Target As Range)

PieColor = Array(10973765, 4409002, 5154185, 9394289, 11507777, 4031707, 13609363, 9606097, _
    9883065, 12426153)

If Not Intersect(Target, Range(“B1:B10″)) Is Nothing Then

    For i = 1 To 10

        If Cells(i, 2).Value = “Completed” Then
            ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = PieColor(i – 1)
        Else
            ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = -2
        End If
    
    Next i

End If

End Sub

This is how the chart looks like when tasks 1, 3, 6 and 9 are marked as completed:

(Click on the image above to view its original size)

Because the code resides in the sheet module under the Worksheet_change event, every task which the user changes to “completed” is being updated dynamically in the chart. If the user clears the “completed” status, the slice of that certain task would return to be white.

Insiders will shortly receive from Eric the file which contains this chart.

Popularity: 1% [?]

I recently created an add-in to create a variance/covariance matrix for Excel 2007. If you follow that link, you will be able to read about the add-in and how to use it. The purpose of this post is to announce that I have created the same add-in for prior versions of Excel.
Note that both versions of the add-in are now updated so that they will calculate either a population or sample variance/covariance matrix. This version of the add-in works in Excel 2003. It should work fine in other recent versions, though I haven’t tested it in anything prior to 2003. It only differs from the Excel 2007 version in that it adds a Var/Cov Matrix menu item to the Data menu.



[[ This is a content summary only. Visit my website for full links, other content, and more! ]]

Popularity: 1% [?]

We know you’re pressed for time, and you want what you read to be worth the effort. Our question to you is this: What kind of blog post about Excel works best for you? Do great tips help you work smarter? Maybe code examples unlock your creative side? Or do guest posts from Excel MVPs hit home? Take this poll, and be heard. 

…(read more)

Popularity: 1% [?]