One Sample Kolmogorov-Smirnov in Excel

Hi everyone,

Attached you will find one sample Kolmogorov-Smirnov in Excel, many people have asked how to do this in excel without using a statistical software, so enjoy it!,

Regards!

Hello, everyone!

Yes, we did this One-Sample Kolmogorov-Smirnov in Excel, just to prove it could be easily witten. We checked it against SPSS.
Obviously you can get the same information from a lot of "professional" statistical packages... but it's Excel you have on your computer, right?

For those not familiar with the basics, just remember that if your scale data (example: weight in kg) do not have a gaussian (bell shaped) distribution, you should NOT describe them in terms of average, variance and confidence intervals. So, to find out if your data adapt to this "normal" distribution, you should run a KS test. If the probability (p) of your data's distribution being normal is to low (let's say, <0.05) you should make use of more "robust" descriptive statistics such as median, interquartile range and identification of outliers.
Hope this helps.

masagus70,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

many people? i dont know anyone who has ever used the term One Sample Kolmogorov-Smirnov!

Very interesting - will definitely take a look at this. Think I may already have a statistical toolset that can do this (and I know Matlab would cover this but I don't have Matlab) but I'm always keen to learn how to do new things. I'd been looking to get a better understanding of this (someone on here explained to me how to do a Chi Squared test the other week which was helpful).

----

After taking a look at this something does seem to be slightly missing. Re: one of the posts above the formula doesn't stack up as you ultimately don't get the following:

D max+
D max-
D max abs
Z - which is very significant
P - which is very significant

and as the sheet is locked you can't edit things (which I was most interested in - e.g. I was keen to see the process behind the test, not having a strong mathematical background but a reasonable understanding of excel). Hope this helps. Don't know what caught my eye about this post (but just thought I should have a look).

--------

one further clarification on this and it was possibly my mistake or lack of clarification from the OP I expect that this sheet really only works with 2007 or newer as it has a xlfn.IFERROR(MAX(OFFSET($AJ$9,0,0,$J$10,1)-OFFSET($AK$9,0,0,$J$10,1)),"") - which as most of us 2003 users know won't work in 2003 (as IFERROR doesn't exist) without a substantial workaround. This sheet may be fine for 2007 onwards.

------

Final edit for all of the pre 2007 users out there you can re-work the IFERROR formula to (in the case of the above) something like this:=IF(ISERROR(MAX(OFFSET($AJ$9,0,0,$J$10,1)-OFFSET($AK$9,0,0,$J$10,1))),"",MAX(OFFSET($AJ$9,0,0,$J$10,1)-OFFSET($AK$9,0,0,$J$10,1))) - further details in the thread: http://www.excelforum.com/excel-gene...in-2003-a.html
and thanks to Martin for his time and the pointers. I've re-worked the sheet and it looks about right but where I only know roughly what I'm doing mathematically I can't say that it's 100%. Hope this helps someone.

Originally Posted by Larry Murphy

Hi

Where did you get the table of critical values and are more extensive tables available online?

Hi Larry!

After much searching, I found a reasonable table on the web at that time.
Now, trying to answer your question, I located...
http://www.aug.edu/~sbajmg/quan6610/...t%20method.pdf

Caution, though: there is discussion about improvements to the underlying formula.
You might want to take a look at...
http://www.itl.nist.gov/div898/handb...on3/eda35g.htm

Hope it helps.

ggartz

Wigwig,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

Hi Wigwig,
I will look through my Excel files (not teaching statistics any more,
retired).
If I find something of possible interest I shall let you know.
Tell me who you are, where you are and what you are doing.
Greetings from Prague (formerly Monterrey, Mexico).

Georg Gartz