What is Real Statistics Using Excel?
Real Statistics Using Excel is a practical guide for how to do statistical analysis in Excel plus free statistics software. This software package extends Excel’s built-in statistical capabilities. In particular, it will enable you to more easily perform a wide variety of statistical analyses in Excel.
What does Real Statistics Using Excel consist of?
Real Statistics Using Excel is comprised of the following four components:
Real Statistics Resource Pack
An Excel add-in that extends Excel’s standard statistics capabilities. It provides you with advanced worksheet functions and data analysis tools. This will enable you to more easily perform a wide variety of practical statistical analyses. The software supports Excel 2007, 2010, 2013, 2016, 2019, 2021, and 365 for Windows. It also supports Excel 2011, 2016, 2019, 2021, and 365 for the Mac. The current version is Rel 9.3 (released 1 December 2024). There is also limited support for Excel 2002 and 2003.
Real Statistics Website (i.e. this site)
- Lets you download a free copy of the Real Statistics Resource Pack
- Provides tutorials and descriptions of how to perform a variety of statistical analyses using built-in Excel capabilities. You will also learn how to use the supplemental capabilities provided by the Real Statistics Resource Pack.
- Presents numerous examples in the form of Excel worksheets which you can download to your computer
For the student and the novice, the Real Statistics website is an excellent tutorial for learning the basic concepts of statistics and how to do statistical analysis. For all users, it provides a step-by-step guide for how to do statistical analysis in the Excel environment and the tools necessary to carry out these analyses.
You can use this website to learn how to perform statistical analyses in Excel even without using the Real Statistics Resource Pack. But we recommend that you download the resource pack so that you can have access to its powerful capabilities.
Real Statistics Examples Workbooks
Consists of fourteen Excel files that contain all the examples shown on the website. These example files can be downloaded for free (click here). Each example focuses on a specific statistical concept and has been designed to demonstrate simple concepts before moving on to more complicated topics.Â
Over time, the webpages on the Real Statistics website are being updated so that you will be able to download an Excel file with any of the examples found on that particular webpage.
Real Statistics Community
Each webpage has a Comments section where you can make suggestions, identify errors, ask questions to others in the community, or request their advice.
You can also get updates about new releases and other information via the @Real1Statistics twitter feed.
How do I get started?
Step 1: If you elect to use the Real Statistics Resource Pack, click on the following icon. You will be given the opportunity to download and install for free the Real Statistics Resource Pack.Â
Once you have downloaded and installed the Real Statistics Resource Pack, you will be able to use the supplemental capabilities with your copy of Excel. This is described throughout the rest of the website and is summarized in Using Real Statistics Functions and Real Statistics Data Analysis Tools.
If you choose not to download the resource pack or examples now, you can do so later at any time.
Step 2: Browse through the website to learn how to perform a wide range of statistical analyses in Excel using standard built-in as well as supplemental Real Statistics capabilities. We suggest that you begin by clicking on the Website Introduction (and especially Organization of the Website). These will explain how to navigate the website to get the information you need to run any specific statistical test or learn about any particular topic.
Why do statistical analysis in Excel?
The reasons for choosing Excel are as follows:
- It is widely available and many people already know how to use it
- It is not necessary to incur the cost of yet another tool
- There is no need to learn new methods of manipulating data and drawing graphs
- It already contains some basic statistics functions and data analysis tools
- It is much easier to see what is going on since unlike the more popular statistical analysis tools very little is hidden from the user
- Excel provides the user with a lot of control and flexibility
This makes Excel an ideal tool for learning statistical concepts and performing some basic statistical analyses. Unfortunately, its built-in statistics capabilities are limited. As a result, many prefer to use statistical tools such as R, SPSS, or SAS for carrying out more advanced statistical analyses.
We created the Real Statistics Resource Pack to address these shortcomings. This software package contains various supplemental tools that enable you to carry out a wide range of advanced statistical analyses without leaving the Excel environment. You can download the Real Statistics Resource Pack free of charge from this website.
I used the Real Statistics Resource Pack to perform power analysis for a scientific publication. How should I cite ‘Real Statistics using Excel’?
William,
See Citation
Charles
thanks for your effort. I had successfully installed the RealStat.Xamls on my pc .I used it to create box plot. But now when i tried to create more blox plots with new data, crtl-m command is not working. Please help
Jiang,
If Ctrl-m worked once, I can-t think of any reason why it would stop working. Does the formula =VER() still work? If so what value does it return?
Charles
Dear Charles,
thanks for this wonderful website and package. I would like to use William’s test to analyse my data, is there a way using Real Stats ?
Best,
David
David,
The Real Statistics software doesn’t yet support William’s test. I am not familiar with this test, but I believe it has some similarity to Dunnett’s test, which is supported by the Real Statistics software.
Charles
Dear Charles
Many thanks for your efforts.
have to to optimize a parameter Y.
We have 6 parameters (X1, …, X6) but for each one we use different value (dose).
X1: pH, i used pH=4,7 pH=5,3 pH=5,75 pH=6,04 pH=6,68
X2: T°, i used: 38°C 41°C 42°C 43°C 45°C
X3: Whey percent: 0% 2% 3% 4% 5%
X4: Ribiflavine effect (B2)/niacine (B3) (g/l): 0/0 1,0/1,0 1,5/1,5 2,0/2,0 2,5/2,5
X5: MgSo4/MnSo4 effetc: 0/0 0,1/0,01 0,2/0,02 0,3/0,03 0,4/0,04
X6: Acid folic (B9 g/l): 0 0,2 0,4 0,6 0,8
How can we have to use to get a function for the relation: Y=f(X1,X2,X3,X4,X5,X6)
The goal is to detrmine the best parameter (X1…X6) to get Max(Y).
Thanks,
Abdelkader,
Sorry, but I don’t understand the scenario very well. In general, Excel’s Solver can be used to address these sorts of problems.
Charles
I’m trying to do a call (center) volume monthly forecast for a 24-hour period. Trying to do it with indicator variables in EXCEL is not possible due to the limitation of 16 variables in performing a regression. Does your add-in handle more than 16 variables in computing a regression?
Donald,
Yes, the Real Statistics add-in handles a lot more than 16 variables in computing regression.
Charles
Dear Charles,
Thank you so much for your efforts. I was struggling in excel on some basic statistical functionality in excel. Your website was very useful.
Thanks again.
Victor
Dear Prof., Charles,
I want to compare 9 sampling sites, each site has different number of ponds, and each pond has different number of organisms, I want to ask what statistical test would be suitable for that type of data, regards
Musrat,
It depends on what sort of hypothesis you are trying to test. Some sort of ANOVA is the likely answer.
Charles
Dear Dr Zaiontz,
When I opened the example workbooks, a warning message popped up tell me that “Complied error in hidden module”. How can I fix this problem? I am using Office 365 excel 2016 32 bit.
Regards
Colin
Colin,
The usual reason is that Solver is not activated. Press Alt-TI and see whether Solver is on the list of addins with a check mark next to it.
If it is, then please answer the following questions:
1. What do you see when you enter the formula =VER()
2. What happens when you open a blank Excel file?
Charles
Hello Charles, great website. Perhaps you can provide the answer to my problem. I don’t quite understand how to input the data on an Excel table in order to calculate ICC and Kappa (or actually, if I correctly understood, Fleiss) for 4 different observers who reviewed 14 different xrays and filled up a questionnaire with 7 questions (question 1, 5 and 6: possible answers A,B,C; question 2 and 3: possible answers: A,B,C,D,E,F,G; question 4: possible answers A,B,C,D,E,F; question 7: possible answers yes/no) then repeated the same questionnaire after two weeks (each of them was blinded to their own previous answers and the xrays were randomly presented). Then, all 4 observers reviewed 14 different CT scans (same patients) and filled up the same questionnaire (2 times, at 2 weeks distance).
Each observer recorded in total 392 answers at the end of the four sessions (196 in each of the two radiographic sessions and 196 in each of the two CT imaging sessions). In total 1568 answers were recorded.
I assume there is a specific way to insert the data. Can you please clarify? Also, how can I calculate p value between the ICC ….I should come out with some sort of conclusion: ” Inter- and intraobserver agreements regarding primary
treatment plans did not signifi cantly differ between radiographs and radiographs plus
CT scans (0.5 vs 0.4, respectively; P.05). The addition of CT did not signifi cantly
change the impression of the amount of displacement per case. By adding CT, more
patients who were assigned nonoperative management were reassigned to operative
treatment (P.033)” . …Btw, I still don’ understand…isn’t kappa supposed to be used for 2 observers max? Why then most of the articles I read report “CT data set of 5 different fractures was presented to 57 evaluators” and then they present conclusion as “The proportion of intraobserver agreement was 82%, with Cohen kappa of κ = 0.748 (P < .001). " Am I missing something? It seems confusing.
Isabella,
If you send me an Excel file with your data and the results you obtained, I will take a look at it and try to clarify some of the issues that you have raised.
Cohen’s kappa is restricted to two observers max, but there are other versions of kappa that allow more than two observers. They are probably referring to Fleiss’ kappa.
Charles
Hello Charles,
thanks for your kind reply. I just sent you the file, as you requested.
Best,
Isabella
Charles,
First of all, thanks for a terrific package – one of the best (at any price).
I have a niggling problem that every time I start Excel: Realstats is unavailable until I uncheck and then recheck the Real-Stats Add-in box. I have the xlam file in the recommended location and I believe I have the correct security settings. What might I have wrong?
Jonathan,
Glad that you like the Real Statistics software.
Are you using other Excel addins?
Charles
Hi Charles:
I just downloaded what I think is the latest version of the Real Statistics Resource Pack.
I am conducting planned comparisons and desire a Bonferroni correction (data satisfy normality and homogeneity of variance). However, when I select either the Bonferroni or Dunn/Sidak and run (with ANOVA and one or more of the planned comparisons selected) selected, I only see the t-test contrasts; not Bonferroni as shown on-line. Would you know off hand what I might be doing wrong?
Thanks kindly!
Keith
Keith,
The Bonferroni test is simply the t test contrasts with a correction for experiment-wise error. Thus, the only change that you will see is to the alpha value. If you have k levels (i.e. k treatments/groups), then the corrected alpha value is alpha/(k-1). This makes the assumption that you will perform k-1 tests. If you plan to perform a different number of tests, then you should change the alpha value to alpha divided by the number of tests that you will perform.
Charles
Hi Sir I really happyou to see this website to im prove our knowledge.I need some guide line in online Survrey of reading strategy of Anderson(2009)
In the study I have 60 sample from 150 population for the givenext populati on I have selected online survey of reading strategies 30 item and linert scale is 3 kindly tell me what will be the cron bach alpa of this questionnaire and how will we check its validity?
Regards
See the following webpage
Cronbach’s Alpha
Charles
Hi Dr. Charles,
Please help me this issue.
I did one experiment with the design as follows:
– I planted 6 rice plants in one plastic container and each container treated with one chemical, there were 10 chemicals (= ten containers). After 3 days, I measured lesion length on two leaves/plant. And I did this experiment twice to confirm the results.
– My coworkers said that I have 6 technical rep/1 biological rep. Is it true? What kind of statistical test should I perform for this experiment (RCD or RCBD or others)?Thank you in advance for your advice. Best regards
Hung Bui,
The answers to your questions depends on the details. In any case, please look at the following webpage>
Design of Experiments
Charles
Should I use RCBD with Trial as Block to run this kind of experiment design that I mentioned? Thanks for your advice
Hung Bui,
Probably so, but I have not followed all your details.
Charles
porque meu pede senha pra libera
Rodrigo,
You don’t need a password. See
Password prompt
Charles
Hi All,
I would like to add to my excel ribbon the Real Statistics macro to a location of my choosing, not the default location.
Has anybody managed to do this and if so what method was used.
Appreciate any help thanks.
Yes, you can do that. I and talk from the perspective of Excel 2013 and 2016. I have done this for my other macros.
You can got to Excel Options and then Customize Ribbon. Select the Main Ribbon Tabs where you want your macro to sit, also you can choose a cool image for the macro from the given set of images.
Hope this helps. Let me know if you get stuck somewhere.
Dr. muchas gracias, por mejorar y ampliar la versión, excelente servicio, que Dios le bendiga.
Dr. thank you very much, for improving and expanding the version, excellent service, may God bless you.
Hi firstly thanks for this. I have installed the version for excel 2016 on office365 excel 2016. I have successfully installed it and can see it on the add in menus, Alt-TI. SOme functions also appear on Fx wizard. However 2 problems
1. the error message : compile error in hidden module: Solvercode appears
2. Clt-m does not work
What should I do? Thanks
Chan,
When you press Alt-TI do you see Solver on the list of addins with a check mark next to it?
Charles
Hi Charles
Yes. I can see it and have ticked it. Just the error message just keeps appearing. Thanks!
Hi Chan,
Sorry about the problems you are experiencing.
1. This one is very strange. The usual reason for error 1 is that Solver wasn’t installed, which is clearly not your situation. I plan to issue a new release of Real Statistics (Rel 5.1) in a few days. Perhaps these problems won’t appear in the new release (let’s hope ?!)
2. That Ctrl-m doesn’t work usually means that you have another Excel addin that uses this sequence. Does the Addins ribbon appear? Does it contain access to Real Statistics? See https://real-statistics.com/real-statistics-environment/accessing-supplemental-data-analysis-tools/
That some functions appear on the fx wizard and other not is due to the fact that I haven’t yet written all the code to make the remaining functions appear. This is a time-consuming (and boring) process and so I haven’t yet spent enough time on it.
Charles
Dr. muchas gracias, por implementar diseño 2^k y correspondencias en análisis multivariado, sin embargo no puedo desplegar los menús, con el paquete de Real Statistics, por favor podrÃa explicarme.
Dr. Thank you very much, for implementing 2 ^ k design and correspondences in multivariate analysis, however I can not deploy the menus, with the Real Statistics package, please explain.
Gerardo,
The 2^k design will be included in the next release of the Real Statistics software. This will be available before the end of August.
Charles
Ok, Dr, thank you very much, and the Correspondence Analysis too?
Yes, the next release will contain this capability and a number of others.
Charles
Thanks for this! I am including box plots generated by this program in a publication. Can you tell me how to correctly cite the software?
Kathy,
See Citation
Charles
Hi Charles,
I just wanted to take the time to say that I have found your website very resourceful and helpful in my work. Thank you for publishing the content. I see you are often solving other people’s problems in the comment section as well, which shows dedication to your field. You do good work, sir.
Best,
Joshua C.
Thank you very much, Joshua. I appreciate your kind words.
Charles
Hallo Mr. Zaiontz,
I was wondering, is there an already built function that deals, at least patially with Augmented Dickey-Fuller Table critical values?
Kind regards
Ivan,
Yes, it is the Real Statistics function ADFCRIT. See the following webpage for details>
https://real-statistics.com/time-series-analysis/stochastic-processes/dickey-fuller-test/
Charles
Thank you for the response. When analyzing the example, for instance DF0, value for tau-crit = -1,95522, while the equivalent value in the Dickey-Fuller table, should be -1,95. How do you come up with the small difference of 0,00522 between the example value and the table value?
Ivan,
Where are you getting the -1.95552 value from?
Charles
Real-Statistics-Time-Series-Examples
Dickey-Fuller Test
Example 1 (no constant, no trend)
cell O8 – tau-crit = -1,95522
Ivan,
I believe that the table is simply a little less accurate than the ADFCRIT formula.
Charles
I would like to create a realistic income distribution. The range of income would be 0 to $300,000 with a mean of $40, and I imagine an SD of $10,000. What is the best way to accomplish this?
Gregory,
It really depends on what you mean by a “realistic” income distribution. A mean of $40 and standard deviation of $10,000 doesn’t seem realistic.
Charles
Dr. buenos dÃas, como puedo trabajar experimentos factoriales 2^k y fracciones con Real statistics?
Dr. Good morning, how can I work 2 ^ k factorial experiments and fractions with Real statistics?
Gerardo,
Sorry, but Real Statistics doesn’t support 2^k factorial designs yet.
I just didn’t have time to do this when I added various Design of Experiments capabilities a few months ago. I will get back to this shortly.
Charles
Doc. Thank you very much
Gerardo,
I plan to add this capability in the next software release.
Charles
Hello Charles,
I am conducting a correlational analysis using a likert scale. I am having difficulty finding out if the variables are linear or not. How can I create a scatterplot to show if a categorical variable is linear? This is a very important step in my project. If I can’t determine linearity or nonlinearity my entire dissertation will produce incorrect data. Almost all the independent variables are categorical. Please help.
Thanks
Nocolette,
The linearity assumption is probably not violated when using dummy variables to code categorical variables. In any case, the following webpage may be helpful to you:
https://stats.stackexchange.com/questions/124580/how-to-prove-linearity-assumption-in-regression-analysis-for-a-continuous-depend
Charles
Thanks Charles,
I really appreciate you taking the time to respond to my question. I have a few more.
After dummy coding the variables, is it then ok to do Pearsons Correlation? If yes should I appply the same assumptions test as I would prior to conducting a Pearsons correlation analysis? If say for example there are outliers in one of the categories, what test should I run? Looking forward to your response.
Nicolette
Nicolette,
I don’t see any reason why you couldn’t use Pearson’s Correlation (although you probably need to use multiple correlation). Whether this is the appropriate statistic depends on what you are trying to accomplish. Outliers could be a problem whether or not you have categorical variables. One approach for dealing with outliers is to use Spearman’s correlation or Kendall’s tau.
Charles
Hello Charles,
I appreciate your response. I have a few more questions. Is it ok to dummy code categorical variables that has more than one categories? For example level of education (bachelors degree, masters degree, Doctorate)?
What if I dummy code the variables , and after which I conduct the assumptions test and the variables are not homogenous and also they fail the normality test. Is it ok to conduct the Spearman rho test?
What if the dummy variables pass the normality test but failed the homogeneity of variance test? What test should be conducted then?
I want to test the relationship between teachers attitudes towards inclusive practice (topic).
Sorry for asking so many questions. I want to make sure the results section in my project is correct. I am somewhat confused at this stage. So I really appreciate your help.
Nicolette,
Depending what sort of analysis you plan to conduct, you can can certainly dummy code categorical variables. This is commonly done with demographic variables of the type you describe.
I don’t quite understand the rest of your questions. What you do really depends on what sort of tests you plan to conduct. Based on your previous comments, it seems that you want to calculate the correlation between two variables. Is this still your situation? You don’t need to worry about normality or homogeneity of variances to calculate the correlation. If you want to test whether the correlation is significantly different, then normality can be relevant. If you need to use dummy variables, then the problem problem becomes a regression problem. I am speculating here since I don’t really know your situation.
Charles
Hello Charles,
Thank you so much for responding to my queries. You have been a big help to me. Thank you for your time.
Nicolette
Nicolette,
Glad I could help you.
Charles
Hi sir..
I have a question about correlation. In my research I am not considering about the ages of my sample. I have 180 respondents and their ages are between 15-53 years old. Can I just do the Pearson correlation test without specific the age? This is because one of the assumption of correlation is homocedasticity.
You don’t have to take age into account when calculating the correlation, but whether you should or shouldn’t do this depends on what you intend to use the correlation for.
Charles
Dear Charles!
Thanks very much for your Real Statistics: I am already using many tools in my job (agricultural research).
Do you plan to add other designs to Design of Experiments (graeco-latin square, criss-cross, incomplet block, lattice, etc) ?
Thanks for all
Mohamed
Mohamed,
Eventually I will add additional designs. I am am always expanding the number of tests that are supported.
Charles
Thank you again
Dear Sir,
Yours is one of the most useful websites that I’ve encountered in a long time and, with its excellent add-in and guide has allowed many of my collegues and myself to obtain a considerable degree of autonomy in performing a number of tasks involving statistical methods in the field of Health Sciences Education. Thank you very much from Chile!
Hi Charles,
I just wanted to chime in and say thank you as well. Your website and tools have been a great help to me in analyzing fisheries data. I have been meaning to learn R for years now but the steep learning curve and a general lack of time has kept me from embarking on that goal. Fairly comprehensive and easy tools like yours make it even harder to motivate myself to go down the R road because much of what I do can be done with your package. And I echo many others here that your clear, concise explanations for the how and why of many tools are excellent and very valuable. Kudos for the great work, and thank you most of all for making it freely available!
cheers, Mike
Thank you very much Mike. I appreciate your message and will continue to try to make clear explanations about additional statistics concepts.
Charles
I just stumbled across this awesome website, and I have to acknowledge with gratitude your hard work and skill. Your discussions are marvels of clarity, as are the spreadsheets. I’ve taught introductory applied statistics for over 30 years, and have long advocated for using Excel for exactly the reasons you mention. I even developed a small set of tools to share with my beginning students–but nothing as systematic and thorough as what is here. With your permission, I’ll be sharing your website in the future with all of my students and colleagues.
William,
Thank you very much for your kind remarks. I have tried my best to create explanations that are easy to follow and tools that are easy to use and easy to understand.
Please feel free to tell you students about the website so that they too can learn from the site and download the software from the site.
Charles
Hello,
I need to help in fixing a problem i have in my data. I am running a gravity model – regression for trade analysis between France and selected partner. I have a data panel in excel with dummies and transformed values.
The issue is if I run the regression the out have #num! in the probability column and the somethings the significant value also has this #num!. how to solve this in my this issue. I am very new to econometrics
Emmanuel,
It is very likely that the FDIST (or F.DIST.RT) formula that is calculating the p-value has an overflow problem. If you send me an Excel file with your data and analysis, I will try to figure out what is happening. You can find my email address at Contact Us.
Charles
Hello! When I start Reliability Testing to know the Cronbach’s alpha I mark the following discrimination cutoff must be a number between 0 and 5. What can I do?
Hermes,
I don’t understand your question. The following webpages may be helpful>
Cronbach’s Alpha
Item Analysis
Charles
Charles,
How do I post a new message on a new thread for this forum? I don’t find instructions.
Thanks.
Joel
joelmweinstein@me.com
Joel,
Just make your comment. This forum is quite informal and so there aren’t rigorous rules. I do suggest that you make your comment on a webpage that is related to the topic of your comment.
Charles
Thank you for providing a much needed summary in the field of statiscs in excell. It’s an excellent refresher and reference, having had statistics courses a long time ago…
I am facing a problem and would like to know how would you proceed to solve it.
I have monthly sales data for over 4 years for over 1000 products ( yes, a big table) and would like to forecast sales for 2017.
Problem. Some products show seasonality and growth. Others are less stable.
I cannot go over each product and test them for each forecasting it would never end.
Can you help?
Youssef,
If you want to forecast all the products, you need to forecast all the products. I don’t know a simpler way to do it, at least based on the information you have provided.
Charles
Hi Charles,
Not sure how to begin this new thread on your website so I posted it as a reply to the most recent question. I just downloaded the Mac version of Real Statistics and I am anxious to get started. My experimental design is a 2-way repeated measures ANOVA, similar to your example #?. It is an entirely within subjects variability. In brief, peripheral vision is measured (dependent variable = area) in 12 subjects, varying two factors, Optical correction (three levels) and Target size (three levels). Example #? goes into detail on the derivation of formulae for this type of ANOVA, but I did not find instructions on the website demonstrating how the data sheet should be arranged, or explicit instructions on how to perform the desired analysis using the drop-down windows.
Thanks very much for your help.
Joel
Joel,
I don’t know which example you are referring to, but in any case when you have one within and one between subjects factor, then this test is supported in the Real Statistics Repeated Measures Anova: mixed data analysis tool. If you have two within subjects factors, then you can look at the Examples workbook (which you can downlaod for free) for the spreadsheet that implements this test, but it has not yet been added to the Real Statistics software.
Charles
Doctor, buenos dÃas que grata y excelente sorpresa, ver que el capitulo de análisis discriminante está en construcción.
Doctor, muchÃsimas gracias nuevamente, sus aportes son una bendición para la comunidad cientÃfica e investigadora.
Doctor, good morning it is a pleasant and excellent surprise, to see that the chapter of discriminant analysis is under construction.
Doctor, many thanks again, your contributions are a blessing to the scientific community and researcher.
Gerardo,
Good to see that this is useful to you.
Charles
Dr Thank you very much
How do I get a number for significance level, when I correlate data pairs i Excel?
Preben,
See https://real-statistics.com/correlation/two-sample-hypothesis-testing-correlation/
Charles
impressive, excellent, outstanding, …
Just a random bunch of words of how I would describe your website.
But even those words do not describe it good enough.
Respect!
Hi,
I was wondering if you knew of any way (maybe using macros or something) to find a max value at regular intervals in a single column in Excel.
Thanks.
Tirupan,
Please clarify what you mean by “max value at regular intervals”.
Charles
I need to find the max in the one column for the starting 300 rows for 11 times and then the max in next 220 rows, and then continue this pattern (11 times find max in 300 rows and one time in 220 rows)? Will a ‘IF’ or ‘WHILE’ should be used?
Sorry, but I still don’t know what you mean by “the max in the one column for … 300 rows for 11 times.” If the starting 300 rows are in range A1:A300, do you mean the max of A1:A11 and then the max of A12:A22, etc.?
Charles
I mean find max for A1:A300, A301:A600……until A3001:A3300 and so on for 11 times and then A3301:A3520, and then keep repeating this sequence for the rest of the data.
Tirpan,
Here is one way to do this, but it requires a little work. Since the second part is similar, I will only tackle the first part, namely find the max for A2:A301, A302:A601, …, A3002:A3301. Note that I started with cell A2 instead of cell A1.
1. Place the number -1 in cell B1. Insert the formula =B1+1 in cell B2.
2. Place the number 0 in cell C1. Insert the formula =IF(MOD(B2,300)>0,C1,C1+1) in cell C2.
3. Insert the formula =IF(C2=C1,””,MAX(IF(C2:C$3301=C2,A2:A$3301,””))) in cell D2.
4. Highlight the range B2:D3301 and press Ctrl-D.
Column D will contain the values that you are looking for.
Note that you really don’t need column B and instead can use values of ROW(), but this is a bit more difficult to understand. If you are using Excel 2016, you can simplify the formula in cell D2 by using the MAXIFS function.
Charles
Hi Charles!!! Congrats on your software!
I’ve tried to use the Multinomial Logistic Regression but there sonthing wrong. I’m using Excel 2016. I’ve installed the add-in and downloaded the sample file. I’ve tried to open but the file apear with errors on calculating. I’ve created another file with just 4 columns and the errors persists. I’ve re-installed the software and the error Was not fixed. Can you help-me? Can you make avaiable a file that is working? Thanks a lot!
Best regards!
Marcos
Marcos,
I just tried using the Multinomial Logistic Regression data analysis tool with the data in Figures 1 and 3 of the following webpage and everything worked fine
https://real-statistics.com/multinomial-ordinal-logistic-regression/real-statistics-functions-multinomial-logistic-regression/
If you send me an Excel file with the data you used (especially the new data that you created) along with the results you achieved, I will try to figure out what is going wrong. You can find my email address at Contact Us.
Charles
Hi Charles!
I’ve re-installed the (Office 2016 – office 365) and I think that the problem can be related to that.
I’ve followed the complete path to use the File and the Option and process of configuring the Trusted Location and everething to fix but I didn’t get that rigth.
I’ve oppened the Excel file and the software can’t find some formulas that are pointed to the RealStats.xlam file, as that formulas “DIAG” on tab “MLogit 2”.
I’ll try to reinstall the Office 365 and than follow the situation… Any other idea?
Thanks again!
Marcos
Marcos,
What do you see when you enter the formula =VER() in any cell?
Charles
Charles,
The message retreieved is #NAME?. It seems that
Excel does not recognize the file RealStat as source of the formulas.
As alternative I’ve installed Office 2013 and made the process of configuration of the file and it worked fine but now I’ve got another error now in Excel sayng there is a problem with supplement file and asking to Disable the file. I’ve download another file but the error es the same.
By the way I’m using Excel in Portuguese.
Thanks again.
Marcos
Charles I think I’ve got the problem. I don’t know why but when I Install the RealStat.xlqm the references to the formulas is pointting to an specific path =’C:\Users\Charles\AppData\Roaming\Microsoft\AddIns\RealStats.xlam’!FTEXT(F6)” So because I don´t have this path the formula doesn’t work. I’ll create another user on my computer to check if it works.
See ya.
Marcos
Hello Sir.
I would like to know if we have post hoc analysis for Scheirer-Ray-Hare Test? Is it okay if I run two-way ANOVA for ranked data and perform Tukey’s HSD Test?
thanks.
Gilbert,
I don’t have a definitive answer for you. If you are performing a post-hoc analysis on the row or column factor, you probably can use a post-hoc test for the Kruskal-Wallis test. I don’t know what approach to use for a post-hoc analysis of the interaction factor, which is why you would want to use the Scheirer-Ray-Hare test in the first place.
Charles
I have the problem in Microsoft Office 2007 of window 7. when I install the realstat package 2007, it asks the ‘solver password and realstat password’ in my excel file. And then,I have ‘compile errors in hidden module, frm Matric’ in using the realstat, add-in. How can I solve the problem? Please!
The usual reason for this is that while you have downloaded the software, you haven’t yet installed it. The installation instructions can be found on the same webpage from where you downloaded the software.
To check whether the software is installed correctly, press Alt-TI and make sure that Solver and RealStats-2007 appear on the list of add-ins with check marks next to them.
Charles
Thank you very much for your informative reply.
Hello
I am now using the Microsoft 2016 in window. I have successfully installed the real statistics add-ins but I can not tap in quick access tool bar and it does not appear when I press the ctrlM and I can not use it.I would like to know how to solve this problems.
Nilar
Nilar,
What do you see when you enter the formula =VER() into any Excel cell?
When you press Alt-TI do you see RealStats and Solver on the list of add-ins with check marks next to them?
Are you pressing Ctrl-m or Ctrl-M ?
Charles
Hi Charles – first, great program. Really appreciate the time and effort you put into it. I just upgraded to Microsoft Office 2016 and after working the first time, am having the same problem. Pressing Ctrl-m or M – nothing happens. When I type in =VER(), what shows up is #NAME?. I’ve confirmed that both RealStats and Solver are in the add-ins with check marks. It also shows up on the options screen as being enabled. Any ideas?
Thanks, Tony
Tony,
This is very strange. If when you press Alt-TI you find that both RealStats and Solver are in the list of add-ins with check marks next to them, then at least =VER() should work. The only thing I can suggest is that you deinstall Real Statistics and start the installation process again.
Charles
Charles – appreciate the quick response. After stumbling around a bit what I got to work was to go to the Data Menu and then the Edit Links sub-menu. When that window opened, I clicked on “Open Source”. Another window then pops up warning about potential threat. Click “OK” on that. That worked. I restarted Excel to see if the settings would stick, but they didn’t and I had to re-do those steps. I’m sure there is away to not have to do it every time, but at least right now I’ve got a workaround. Hopefully this helps others using Office 2016 and have the same problem.
On a separate topic, have you considered adding a Probit Regression option to your Logistic Regression? It is more commonly used in econometrics than Logistic Regression.
Thanks again, Tony
Tony,
Perhaps the following webpage can be helpful:
https://www.excelcampus.com/vba/add-in-ribbon-disappears/
I do plan to add probit regression. It has been on my list for a long time, but something elese always seems to pop up and so I never seem to get to it.
Charles
I would suggest that;
1) you install the “RealStats.xlam” file in a “trusted” location
2) you unblock the “RealStats.xlam” file
the two steps above are well described in: https://www.excelcampus.com/vba/add-in-ribbon-disappears/
In order to to see the addin menu in Excel you should also additionally run Excel as an Administrator otherwise the ctrlM will work but the addin menu will not be on the ribbon
Thanks Paolo. I’ll try it. Appreciate the answer.
Hi Charles – thanks for adding the probit regression. I just had the opportunity to make use of it, so appreciate you adding it to the last release. Many Thanks, Tony
Tony.
Good to see that it was helpful to you.
Charles
Is there a way (hopefully easy) that allows for the prediction model for binary logistics regression to be accessed and used by another similar data set ? I am finding if I use from 2-10 independent variables the regression works but the coeff are always placed in a different column so I have to manually input the equation when I need it to be automatically done. Any suggestions would be helpful.
Bill,
Sorry, but I don’t understand the issue that you are raising. Can you please clarify.
Charles
Hello, I would like to know how to solve this problem.
Compile error in hidden module: LogisticRegression.
This error commonly occurs when code is incompatible with the version, platform or architecture of this application.
Before this when I wanted to add real statistics in Add-ins I had mistake “Can’t find project or library”. Then I pressed OK and I received other message “enter password”. After that I see real statistics in Add-ins but it doesn’t work =(
Anastasiya,
You should never need to supply a password. When you press Alt-TI do you see both Solver and RealStats in the list of addins with check marks next to them?
Charles
It works now=) thanks!
Charles,
I am opening an excel file, and when I click on data>data analysis I don’t get any options more than I get from excel. Is there another method to access Realsatats functions?
You need to either press Ctrl-m or use the Addins ribbon.
Charles
Dear Charles; I just downloaded and installed Real-Stat, and I went thru excel-file-options-add-ins, etc. But when I open excel file and click data analysis I still don’t find it. What did I miss?
Ahmed,
Are you trying to open the RealStat file or some other Excel file? You should not try to open the RealStat file. You need to open some other Excel file.
Charles
I have two dimensional river data for two rivers, and on the 2D plot — S vs. C — it is clear that data cloud for one river is distinct from the data cloud for the second river. I’m struggling with the setup for demonstrating statistically significant difference between the two clouds. The two dimensions are independent, the number of data points for each river are different, they are not paired. Is there an appropriate template in your downloads that will work for a plug and chug analysis?
Peter,
If you want to compare the means of these two data clouds you can use the two independent sample t test.
If you want to compare the two distributions, then you can use the two sample Kolmogorov-Smirnov test.
Both of these are described on the Real Statistics website and are included in the Real Statistics software package.
Charles
I don’t see how the t test can be used when there are two dimensions to the data cloud. All the examples test for significant difference between the means of a single variable/measurement. It would be appropriate for determining if there was a significant difference in mean weight between juniors and seniors in high school. But what if you plotted both height and weight of juniors and seniors and wanted to know if the two populations were significantly different in both dimensions combined, i.e. maybe there isn’t significant difference when considering either height or weight independently, but you want to know if both together are distinctive? I was told that MANOVA was appropriate for that scenario, but I’m having a hard time translating the jargon into setting up an analysis for my specific case with river samples in which there are two measurements per sample. It looks kind of like Example 1 of the “MANOVA Basic Concepts” if you eliminate Column D (herbicide) and eliminate two of the soils (salty and clay) and want to know if yield plus water in combination are significantly different between loam and sandy. Am I on the right track?
Thanks for the help. Intuitively, I generally understand what I want out of statistics, but it often feels like a foreign language when it comes to the application of them.
Okay, I think I finally sorted it out with MANOVA.
Thanks for making the tools accessible!
Good to hear, Peter.
Charles
Charles
I am getting this error when installing
Microsoft Excel cannot access the file ‘C:\Users\David Harris\AppData\Roaming\Microsoft\AddIns\RealStats.xlam
The file name or path does not exist
The file is being used by another program
The Workbook you are trying to save has the same name as a currently open workbook
Any suggestion on how to fix this issue
David,
Perhaps you already have RealStats installed and you are trying to install a new version. In this case, you shouldn’t repeat the installation instructions. Simply replace the old file containing RealStats by the new file.
Charles
Hi,
Thank you very much for this helpful site. Well I have some questions my research is about factors affecting students’ pursuit of STEM, what do you think is the best treatment for the data where my DV are from Likert/summated scale and my IV is a nominal data (yes/no if they will or will not pursue STEM).
Is a point biserial ok? (My panel wants me to use correlation.)
or is logistic regression more appropriate?
Thank you in advance! 🙂 🙂
additional question:
is it ok that I only got 20 yes out of 300 respondents?
Sorry, but I don’t have a definite answer for you. Logistic regression may not converge to a solution with such unbalanced data.
Charles
Javin,
It really depends on what sort of questions you are trying to ask, but correlation and logistic regression both might be reasonable approaches. Note that for this sort of data point biserial correlation is equivalent to correlation.
Charles
Thank you 🙂 🙂
Hi,
Thank you very much for your reply back then plus thank you for this free downloadable add ons in excel , it’s a great help. Well I have a question again my panel wants me to use this formula t=(r√n−2)/√( 1−r^2) how do i interpret the result of this?
They also ant me to use one way anova to see the difference between the response of those who sad yes and those who said no but i have unequal sample size, does it matter?
Thank you again! God bless
Javin,
1. The formula formula t=(r√n−2)/√( 1−r^2) is used to test whether the correlation coefficient for a paired sample is significantly different from zero. See the following webpage for more details
https://real-statistics.com/correlation/one-sample-hypothesis-testing-correlation/correlation-testing-via-t-test/
2. You can use one-way ANOVA even when the sample sizes are unequal. In such cases, you should make sure that you are not violating the homogeneity of variance assumption since the test is more sensitive to violations of this assumption when the group samples are unequal.
Charles
Hello sir, my question is little different it was asked me by interviewer the question is, “say the statistical reason that why airplane fly 2000 feet upon the earth”
Sorry, but I don’t understand your question. Airplanes don’t fly 2000 feet above the earth, they fly much higher than that.
Charles
Hi Dr. Charlz
I am a PG student carrying out a study on Burden and Coping among care givers of stroke survivors. The question I am about to post may seem naive but its quite confusing for me
The objectives are
1. To assess the burden among caregivers of stroke survivors.
2. To identify the coping strategies among caregivers of stroke survivors.
3. To find the association between burden and coping strategies among caregivers of stroke survivors.
4. To find the association between burden and selected demographic variables among caregivers of stroke survivors.
5. To find the association between coping strategies and selected demographic variables among caregivers of stroke survivors.
Statistician recommended to use Chi square for Objective 4, ANOVA for Objective 5 and Independent t-test for Objective 3. I am doubtful on why ANOVA and t-test for the objectives 5 and 3 , I suggested using chi square.
The coping checklist is categorized as Problem focussed,Emotional focussed and Problem and emotional focussed.
Burden is categorized as No burden,Moderate Burden and Severe Burden
I assume your questions whether to use your approach or the statistician’s approach. In order to answer I would need to know the nature of the data that you are collecting.
Charles
Yes thats the doubt I have in mind
Regarding the data, i have 3 separate tools-2 standardised and 1 for sociodemographic variables
Section A: Socio personal data of the participant
Caregivers socio personal data consisted of 7 items including age, gender, education, marital status, employment status, monthly income, family type.
Section B: Clinical data sheet
Clinical variables consisted of 8 items including relationship with the patient, total caregiving time(hrs/day), duration of care, availability of sub caregivers, family history of stroke, level of dependency, type of disability and duration of illness.
Tool 2: Burden Assessment Scale(BAS) .Instrument consists of 40 items on burden with 3-point Likert scale Scaled not at all , to some extent, very much. The score range from 40-120 . Higher scores indicates greater burden
0-40 – no burden
41-80 – moderate burden
81-120 – severe burden
Tool 3: The Coping Checklist (CCL)
The coping checklist (CCL) has 70 items describing a broad range of behavioral, cognitive and emotional coping responses that an individual might use to handle stress. The responses are scored dichotomously in a yes/no format, indicating whether a coping behavior is present or not. The total no. of items reported by an individual is indicative of the size of coping repertoire. Further refinement of the tool resulted in seven subscales, one of problems focused coping, five of emotion focused coping and one social support subscale, which is a combination of both problems focused and emotion focused coping.
Based on what I have understood, the fact that the coping strategy is a score between 0 and 70 makes me think that ANOVA is the correct tool.
Charles
Hello sir,
Thanks for making this tool available.
Is there a way to work on SARIMA model?
What I see in the add-in is just ARIMA without the seasonal component.
Sorry Emmanuel, but I have not yet added a seasonal component to ARIMA. I do explain seasonality for ordinary linear regression and for ETS models. See the following:
Regression with Seasonality
Holt-Winters Forecasting
Charles
Dear Doctor Zaiontz,
I have two factors, i.e. gender (male/female) and generation (old/mid/ young). The result of two-way anova revealed no significance. Could I conduct planned t-tests? If yes, could I use “contrasts for two factors” on the website to do this?
Thank you for your time!
Rebecca,
Yes, you can use contrasts or Tukey’s HSD. See the following webpage:
https://real-statistics.com/two-way-anova/follow-up-analyses-for-two-factor-anova/
Charles
Dear Dr. Zaiontz,
I have one dependent variable (binary categorical) and 7 independent variables; 6 of them are binary categorical and the seventh independent variable is age. I want to use correlation to determine if the independent variables affect the dependent variable (falls). Can I use multiple correlation for this? My adviser wants me to use correlation.
Thank you,
Azuka
Azuka,
Yes, you can use the multiple correlation for this. See the following webpage:
Multiple Correlation – Advanced.
Charles