Creating your own formulas in Excel

[zlatan100]

Can anyone help me with how I can create and define my own formulas in Microsoft Excel, and also how I can de-cipher formulas in Excel made by others.
Right now I have a spreadsheet where the formulas "JarqueBera" and "WeightedVolatility" has been used, without explanation of the underlying calculations.
Help is much appreciated.

[d3fiant]

googled it and found the following which looks to be linked to what you are asking

function JBpvalue = JarqueBera(Variable)
%
% JBpvalue = JarqueBera(Variable)
%
% returns the p-value of the Jarqu-Bera
% test of Normality.
%
% NOTE: a low p-value (e.g. 0.03) => NOT Normal
% a high p-value (e.g. 0.1) => Normal
%
%
% Copyright(c): PNath@London.edu 25-Jan-2001
%


NoOfDataPoints = prod(size(Variable));
Variable = reshape(Variable, NoOfDataPoints, 1);
VariableSkewness = skewness(Variable);
VariableKurtosis = kurtosis(Variable);

JB = NoOfDataPoints.*(VariableSkewness.^2/6 + (VariableKurtosis-3).^2/24);
JBpvalue = 1-chis_cdf(abs(JB),2);

also http://www.hoadley.net/options/develtoolsaddin.htm

[zlatan100]

Thanks,
But, I don't know what you mean by JBpvalue = 1-chis_cdf(abs(JB),2) - what is chis_cdf?

But also, do you know how you can, in Excel, see what lies behind these self-defined formulas?

regards,
Thor

[Funkstar]

What verison of office are you using.

In 2007, its under Formulas -> Defined Names -> Name Manager.

This lets you see and edit the underlying formulas

[zlatan100]

I use 2003 and 2007 versions.
In the name manager, there are only defined areas (so that a shortcut or name refer to a specified are).
But one can also make own formula, with own definitions / underlying calculations - this is what I am looking for. And I've spent two days on this now, and I am losing my patience.

Any ideas?