# Off Topic > Tips and Tutorials >  >  One Sample Kolmogorov-Smirnov in Excel

## gerardoissa

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!

----------


## snowboard

I'm a n00b too, but I don't see any attachment. I'm working on this myself, so I will post an *actual* working attachment when/if I get it working.

Pretty easy to do KSI tests in Matlab, but I need to do it for a bunch of different datasets, so Excel fits my needs better in this case.

----------


## e4excel

Hello Guys,

I am not aware of the aforesaid test but because I found this link intersting out of curiosity I thougt of suggesting something..
Like me there would be so many who would not hail from a Typical Mathematical or a Statistical Background..
So can you guys actually cite an example with the above application..Just a humble request..

Warm regards
e4excel

----------


## ggartz

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.

----------


## splowe

What is my null distribution isn't normal?  I want to compare a sample to a Pareto.

Thanks for the spreadsheet and any advice you can give.

----------


## Cutter

Welcome to the forum, splowe.  Unfortunately you've inadvertently broken one of the forum rules.  Please read the following.  Thanks.

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

----------


## masagus70

hello guys my name is agus, postgraduate student. i am interested to joint with this forum because help me to understood in my study. hope all of you don't mind to sharing the knowledge to me.
thank you

----------


## masagus70

Sir
i have already opened your K-S sample, and very helpul.
could you give me the formulation in excel format to determine : 
D max+
D max-
D max abs
Z
P

thank you sir

----------


## arlu1201

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.

----------


## Richardlevins

Thanks for sharing the spreadsheet .It is really useful

----------


## martindwilson

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

----------


## mrvp

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.

----------


## gerardoissa

Hi again, everybody!
Some of you have asked for an "open" KS worksheet.
Here it goes. Minor improvements have been made to the first one.
Hope it helps.

Georg GARTZ
ggartz@gmail.com

----------


## znf

Hi,

I'd like to use your open spreadsheet for Kolgomorov-Smirnov but I have nearly 6000 data points. Is there any way to use it for more than 1000? I tried to simply copy the formulae down the page but there were some errors.

Many thanks,
ZNF

----------


## ronymusic6

> Hi again, everybody!
> Some of you have asked for an "open" KS worksheet.
> Here it goes. Minor improvements have been made to the first one.
> Hope it helps.
> 
> Georg GARTZ
> ggartz@gmail.com



Thanks for sharing your knowledge!!

----------


## ggartz

Hi everybody,
I am posting a refreshed version of the Kolmogorov-Smirnov sheet.
Password is 1234, in case you want it open.
It allows for the introduction of up to 4000 data.

Georg Gartz
ggartz@gmail.com

----------


## Larry Murphy

Hi

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

Thanks

----------


## ggartz

> 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

Hallo,
I have been on the site Excel Forum, I saw your file for statistical analysis KS, is really well done.

I'm looking for a file of analysis of variance univariate and multivariate analysis.
For the case you have made ​​a file in excel?
thanks

----------


## arlu1201

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.

----------


## wigwig

Thanks for the reply, but I am new and research is always a bit difficult

----------


## ggartz

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

----------


## wigwig

Hallo Ggartz,
thanks for the reply, I had also written on your personal email.
I studied statistics at university in Italy, and now I have to use the test to work. So in this period still studying statistics.
Since I am not very knowledgeable about statistics, I'd like to use excel on the tests to suit my needs.
I started studying the tests as Fischer, Student T, Kolmogorov S., Mann Whitney U, Variance, and univariate and multivariate linear regression ....
As you can see is a very easy approach of statistics, but enough to get started.
Thank you, Regards

----------

