Excel provides a great many built-in worksheet functions such as LOG, ABS, ROUND, etc. In particular, there are a great many statistical functions such as AVERAGE, CORREL, NORM.DIST, etc. A list of the functions that are most useful for our purposes is given in Excel Capabilities.
As mentioned in Excel Spreadsheets, functions can be entered into a cell manually or by clicking on the symbol fx to the left of the formula bar to access Excel’s function building capability. Alternatively, these capabilities can be accessed via Formulas > Function Library.
Function arguments
Most worksheet functions take one or more arguments. E.g. the worksheet function ABS takes one argument, and so the value of the worksheet formula =ABS(A1) is the absolute value of the value in cell A1. Thus, if A1 contains the entry -7, then =ABS(A1) has the value 7.Â
When a function takes more than one argument, then these arguments are separated by a comma. E.g. the function CORREL takes two arguments, each of which is an array. Thus the value of the formula =CORREL(A1:A10,B1:B10) is the correlation between the data in ranges A1:A10 and B1:B10.
For Excel environments where a comma is used as the decimal separator (instead of a period), a semi-colon is used as the separator for arguments in a function. E.g. in the Italian version of Excel, 0,5 represents the decimal value one-half (instead of 0.5) and the correlation formula specified above must be written as =CORREL(A1:A10;B1:B10).
Differences between versions of Excel
Excel 2010 includes all the functions of Excel 2007 plus a number of additional functions as explained in Built-in Excel 2010 Statistical Functions. Excel 2013 also adds a few additional functions. Etc. On this website, we will tend to use those functions that are common to all versions of Excel starting with Excel 2007, although we will highlight differences where this is appropriate.
One of the criticisms of Excel 2007 is that in certain extreme cases, some of the built-in functions are not sufficiently accurate. This was corrected in Excel 2010. New algorithms were implemented to improve the accuracy of these functions and to eliminate any known errors. Versions of Excel starting with Excel 2010 also provide more consistent names for the functions.
Real Statistics supplemental functions
While Excel provides a number of statistical functions, as has been noted previously, it lacks some important capabilities provided by standard statistical packages such as SPSS and SAS. The Real Statistics Resource Pack provides a collection of supplemental statistical functions to augment the built-in capabilities.
To access these supplemental statistics functions you need to first install the file called xrealstats.xlam as described in Free Download.
Reference
Microsoft Support (2012) Excel functions (by category)
https://support.microsoft.com/en-au/office/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb
Can u please provide me how to work out with Shapley Owen Decomposition in excel.
As this function is not there in excel, do I need to download it and then insert add on function?
Please help
Hello Gouri,
You need to replicate the steps described at
https://www.real-statistics.com/multiple-regression/shapley-owen-decomposition/
in Excel. Otherwise, you need to download and install Real Statistics, a statistics add-in to Excel, and use the SHAPLEY worksheet function as described on the above webpage. You can download and use Real Statistics for free. See the following webpage:
https://www.real-statistics.com/free-download/real-statistics-resource-pack/
Charles
i had saved xrealstats.xlam in programs folder initially and then moved to C:\Users\username\AppData\Roaming\Microsoft\AddIns.. I gave the location in trustcenter and added in the add-ins ( in options). But my excel menu is not showing the add-in nor am i able to use the functions therein. What can be the possible problem (i am using office 2016) ?
Is there any help available for each function w.r.t how the data should be arranged to run the functions ?
Hello Saibal,
Does Excel appear in the Add-ins ribbon after you press the key sequence Crtl-Shft-m ?
There are three levels of help:
1. Enter the formula name in any cell and press the key sequence Ctrl-Alt-a
2. Enter the formula name followed by a left parenthesis and click on the Insert Function icon fx on the Home ribbon
3. Look up your function on the appropriate webpage from the Tools menu on the Real Statistics website. If the information there is not sufficient click on the link to be directed to the webpage for that function.
Charles
when and why do you use statistical functions in a Worksheet? This is my question my teacher asked me today and I have no clue.
Kelsie,
Sorry, but I don’t understand your question.
Charles
I am currently using Excel 2008 on a Mac; will that system with the material presented here?
Steve,
There is a MAC version of the Real Statistics Resource pack, but it does not work with Excel 2008. The reason is that the Real Statistics Resource Pack uses Excel’s VBA, but Excel 2008 doesn’t support VBA.
Charles
As a grateful previous user, still using OSX Yosemite version 10.10.5, I am now looking forward to establishing that the 2-parameter Weibull function is indeed a good fit to data on the human mortality distribution as a function of age. First of all this will involve using a known iterative routine to extract the Weibull shape parameter by the principle of maximum log likelihood.
John,
I understand from looking at the Internet that Excel users have had problems when using OSX Yosemite version 10.10.5. Has this now been fixed?
Charles