Free Microsoft Excel 2013 Quick Reference

Tests for Normality in Excel

Are there any tests for Normality in Excel? I've used other statistical
packages and currently don't have access to them. Does Excel have this type
of capability and if it does, how do I use it?

Barb Reinhardt

Post your answer or comment

comments powered by Disqus
Happy new year to everyone!

I would like to know how I can test the hypothesis in excel, for example Tuesdays have lower volume than the average weekday?

Day Volume
Sun 2,650
Mon 2,581
Tue 2,337
Wed 2,972
Thu 3,166
Fri 3,189
Sat 2,408
Avg Day 2,753

Thanks in advance!

Testing for Dates in Cells. B1 needs to test if A1 is a valid date.

What are the Default {RGB} colors for graphs in Excel 2011?

I don't want an approximation. I want to match what is has used in some of the automatically generated graphs elsewhere.

in vba what is the similar function for SUBSTITUTE in excel? Can u pls help me?

I tried to record a macro for Solver in Excel. After recording the macro, I
ran it and the message was that the "SolverOK" which is the first word in the
body is not a defined Sub or function.

Hi ,
Actually i need the bulk updations in the comments in Excel 2003,
For eg.
In excel i have few cells in every cell i need to update specific word with
another word like find /replace.
please can you give me out needful information for my requirement.


does any one know the default alignment for values in excel?

Hi all, again, members of the Ozgrid family,

Another bunch of code, again in the large field of statistics, but now much less explanation is needed from my part. :-)

Here's the code to test a set of data on their normality. It's the Excel implementation of the so called Jarque-Bera test on Normality.

 = "Too few observations for a reliable Jarque-Bera test" 
Function JBStat(data As Range) As Variant 
     '    ___________________________________________________________________________________________________________________
     '   |                                                                                                                  
     '   |  Wim Gielis                                                                                                      
     '   |                                                                                       
     '   |  01/18/2007                                                                                                      
     '   |  Custom functions to test data on their normality: Jarque-Bera hypothesis test of normality                      
     '   |  Adapted and extended code from: Andreas Steiner,              
     '   |  Also on                                                                                 
    Dim n As Long, i As Long, elem As Double, S As Double, K As Double, DataMean As Double, DataStDev As Double 
    n = data.SpecialCells(xlCellTypeConstants, 1).Count 
    If n > 30 Then 
        DataMean = WorksheetFunction.Average(data) 
        DataStDev = WorksheetFunction.StDev(data) 
        For i = 1 To n 
            elem = (data(i) - DataMean) / DataStDev 
            S = S + elem ^ 3 
            K = K + elem ^ 4 
        Next i 
        JBStat = n * (((S / n) ^ 2) / 6 + ((K / n - 3) ^ 2) / 24) 
        JBStat = sError 
    End If 
End Function 
Function JBpValue(data As Range) As Variant 
    JBpValue = IIf(JBStat(data) = sError, sError, Round(WorksheetFunction.ChiDist(JBStat(data), 2), 2)) 
End Function 
Function JBTest(data As Range, SignificanceLevel As Double) As Variant 
    JBTest = IIf(Not (IsNumeric(JBStat(data))), sError, (SignificanceLevel < JBpValue(data))) 
End Function 
Function JBCriticalValue(SignificanceLevel As Double) As Double 
    JBCriticalValue = Round(WorksheetFunction.ChiInv(SignificanceLevel, 2), 2) 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
See the attached file for an application on some data.


Had some help before with this sheet, but i need to make my test work differently.

I attached the sheet in excel 2002 format, since its hard to explain but basically the formulas in n36:t36 need to test for duplicates in rows 5:31, but only if the values are in the duty table, if they are not then duplicates are OK if the Cell is empty, a number, or listed in the special items table!

My conditional formatting works, but I cant seem to figure out the test in the column below :-(




All of a sudden I don't hear any sounds with excel. When I go to control
panel and sounds I can click on the test button for all the normal sounds,
critical stop, default beep, etc. and I can hear them but those same sounds
for alerts in excel I get nothing. All other sounds work, like with games
and such. It seems to be isolated to excel and maybe other office suite
programs but I use excel alot and that's where I first noticed it.
any suggestions?

I have been trying to use a named INDRECT function with a solar power sheet I created in Excel 2010.
Here is the reference I use on my chart range: ='NET Meter Readings-f.xlsx'!MyRange3
The equation in MyRange3 is =INDIRECT(ChartData!$B$8):INDIRECT(ChartData!$B$9)
The cell equations for B8 and B9 are ="D"&ChartData!$B5 and ="D"&ChartData!$B6
This is driving me crazy.

I have attached the file 'NET Meter Readings-f.xlsx

I am a retired EE and have used Excel for engineering spreadsheets, but this is my first attempt at using named references and auto ranging in charts. I recently added solar electric panels on my roof and this is a means to track performance.


I have a data set and I want to know whether it is Normally distributed or not. How can I do this easily? I would prefer to be able to do it in Excel as I don't have any advanced statistical packages like SPSS.



I'm trying to write an SQL query in excel vba to pull in the last 20 service records for each customer in an access database. I'm having some difficulty with the query and hoped someone here could help.

My tables are basically:
Service table with fields: customer number, service date
Customer table with fields: customer number, customer name

I need to pull in the last 20 dates each customer was serviced like so:

customer number customer name service date
1 John 04/21/2005
2 Mary 04/15/2005
1 John 03/14/2005
2 Mary 03/01/2005
etc. until we have the last 20 dates per customer for each customer in the database.

The best thing I have so far is:

SELECT top 20 (s.scheduledate), c.customernumber
order by scheduledate DESC

But this doesn't work as I expected.

Any ideas or suggestions would be greatly appreciated.

Hi All,

I have a fair bit of code developed in Excel XP, but which needs to be converted for use in Excel 97.

I have encountered one error so far (and assume that there will be more when I can get the rest of the code to run).

The Function below works fine as is in XP, but in 97 says that the Function is not defined, and highlights StrReverse. When I declare StrReverse as String, I then get a Compile Error message - Expected array.

Function REVERSETEXT(text) As String

REVERSETEXT = StrReverse(text)

End Function

I'm not really sure what to do next - any suggestions?


In Excel I have a spreadsheet with the following fields all are formatted as Text except for the Date and Time ones which are formatted at Date and Time respectfully. However when I am linking it to MSACCESS the 3rd Appt Date, Time3, 4th Appt Date, Time4 are being shown in the Table Design as Text and the values are being shown as #Num! any suggestion on how to get MS ACCESS and MS EXCEL to work together.

1st Appt Date, Time1, 2nd Appt Date, Time2 all function properly and have the same formatting.

Student ID
Last Name
First Name
1st Appt Date
2nd Appt Date
3rd Appt Date
4th Appt Date

Hi All,
I'm trying to test for an existing Excel instance with a specific workbook. Now I may have to deal with several instances of Excel open at the same time on the workstation. I would like a way in VBA to look through what excel instances are open see if the desired workbook has been opened in them. This isn't to be confused with having multiple workbooks open in the same instance. I have tried several different ways in vain, some of them iterated below. I would appreciate any work code examples explaining what an dumkoff I am.

...this never detects the open workbook

Function OpenBook(sBookName As String)
    Dim k As Integer
    'Checks the names of all open  books
    For k = 1 To Workbooks.Count
        If Workbooks(k).Name = sBookName Then
            ' activate already open workbook
            Exit Function
        End If
    Next k
    ' open workbook
    RetVal = Shell(("EXCEL.EXE " + Chr(34) + sFileBasePath + sBookName + Chr(34)), 1)
'    Workbooks.Open (sFileBasePath + sBookName)
End Function
...this doesn't work either
Sub TryThis()
    Dim WIsOpen As Workbook
    On Error Resume Next
    Set WIsOpen = Workbooks("c:sheetsPlanners.xls")
    If WIsOpen Is Nothing Then
        MsgBox "I'm not Open"
        MsgBox "I'm already Open"
    End If
    On Error GoTo 0
End Sub

Hey guys,

I'm trying to create a chart in Excel 2007 that displays a fixed range of data from a group of similar worksheets. Each worksheet contains weekly performance figures that I need to display in the chart, and the chart needs to display the past six weeks' figures so that trends can be established and any areas of concern can be raised

The problem I'm having is trying to define a fixed range of six weeks for the chart to display. The previous weeks' figures are added to each worksheet at the start of the working week, yet inserting a new row into the spreadsheet extends the data range; six weeks becomes seven, seven becomes eight etc, which is not what I would like to achieve. Short of manually redefining each data range every week, is there a way to fix the data range so that the chart only displays six rows' worth of data from the other worksheets in the workbook?

Any help would be greatly appreciated!



For my youthmovement I'm making a userform in excel. Everything works but I just can't make the follwing thing. I'll try to explain as complete as possible.

If I have the following example names in a sheet:
column A, column B , column C ,colmn D
Peter, Selie ,HN, 1
Tom,Maat,KAB, 1
Kristof ,Gekko, JKN, 4

Then I fill in my userform with the following things:
combobox 1: Peter combobox 2: Selie combobox 3: HN Textbox 7: 1
next userform:
combobox 1: Peter combobox 2: Appel Combobox 3: JKN Textbox 7: 1

What I want to happens ,is the following (look to the changes, I placed them in bold):
column A, column B, column C, colmn D
Peter, Selie, HN ,2
Tom, Maat, KAB, 1
Kristof, Gekko , JKN 4
Peter, Appel, JKN, 1

I already get the names on the wright places but I cant make the program for a sum and let the program search if names or the same or different.

Everything is made with Visual Basic editor in Excel. It is made in excel because the most don't now acces.

Thanyou anyhow for reading en helping me out.

when using the =CONVERT functions of excel, where can I find a list of common
abbreviation. I have some like (sec for secons, in for inch, oz for fluid
ounce) but some are not what i thought. IE: Ton is not ton or pound is not
lbs, Lbs LBS.

hi, I am trying to find if record exists for values met in 2 different
columns, for use in a conditional format.
info have is:

=INDEX(ROW($AQ$1144:$AQ$1228),MATCH(TRUE,$AQ$1144: $AQ$1228=$AQ11,0))>ROW($A$1144)

not sure how got this to work in Cond. Format since (for a TRUE), when
testing in an external cell get a VALUE error; but, want to compare to
another column for OR( values, e.g.:

INDEX(ROW($AQ$1144:$AQ$1228),MATCH(TRUE,$AQ$1144:$ AQ$1228=$AQ11,0)),

oh yea: thanks.

Normally in Excel, when you hit tab, the next cell is highlighted, in mine it
skips several cells. Does anyone know where the setting for this is and how I
can change it back to normal?

Is there are formula in excel that will check a file for duplicate entries. I
have a huge list that I know contain duplicate addresses. I seem to recall
that there is such a formula but I can't remember how to do it. I am using
Excel 2003.

Thanks very much for your help.


The subject line really says it all, very unfortunately.

I'm using a Dell Inspiron 9300 with a nice GeForce Go 6800, latest
Dell-approved drivers (April 2006), plenty of horsepower; and of course
I'm fully up-to-date in my Windows XP Pro and Office 2003 Pro service
though now I'm actually using Office 2007 beta 2.

Suddenly I'm having to use Excel every day, and usually with BIG sheets
(often with hundreds of columns, tens of thousands of rows).

In searching to see how to turn on smooth/live/real-time
scrolling instead of the "helpful" stupid tooltip bubble with the row
or column number/letter (that then only shows the rows you've scrolled
to after releasing the scroll bar), I discovered that live scrolling
was standard In Excel versions 2002 and higher. Indeed, there's no way
to turn it off or on--it's just always on.

Except for me, somehow. It doesn't work at all. More precisely, it's
like Excel has turned off that feature (the scrolling still works, but
horribly--as though one were using Excel 97, with the actual scrolling
occuring only after one lets go of the bar, so one simply CANNOT scan a
spreadsheet without going single page by single page).

Can any expert out there suggest any settings, registry patches, or
other fixes?

I asked this question a few months ago (before I got Office 2007) and
no one replied--I hope someone has some ideas this time. This is
incredibly annoying and really hurts productivity with Excel.



The subject line really says it all, very unfortunately.

I'm using a Dell Inspiron 9300 with a nice GeForce Go 6800, latest
drivers (April 2006), plenty of horsepower; and of course I'm fully
up-to-date in my Windows XP Pro and Office 2003 Pro service packs.

I've not used Excel much, but suddenly I'm having to use it a very
great deal. In searching to see how to turn on smooth/live/real-time
scrolling instead of the "helpful" stupid tooltip bubble with the row
or column number/letter (that then only shows the rows you've scrolled
to after releasing the scroll bar), I discovered that live scrolling
was standard In Excel versions 2002 and higher. Indeed, there's no way
to turn it off or on.

Except for me, somehow. It doesn't work at all--I get to party like
it's 1997 every day, and I don't much like it.

Can anyone suggest any settings, registry patches, or other fixes?



No luck finding an answer? You could always try Google.