Results 1 to 5 of 5

Thread: Creating your own formulas in Excel

  1. #1
    Registered User
    Join Date
    Aug 2007
    Posts
    3
    Thanks
    0
    Thanked
    0 times in 0 posts

    Post Creating your own formulas in Excel

    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.

  2. #2
    ***** Lurker
    Join Date
    Aug 2005
    Posts
    722
    Thanks
    2
    Thanked
    15 times in 15 posts
    • d3fiant's system
      • Motherboard:
      • GB X58A-UDR3 FB11
      • CPU:
      • Core i7 950
      • Memory:
      • Corsair 12GB DDR3 1600
      • Storage:
      • 2x 120GB OCZ Agility SSD + 500GB SP F3
      • Graphics card(s):
      • GB Windforce GTX670 2GB
      • PSU:
      • 850W Akasa
      • Case:
      • Fractal R3
      • Operating System:
      • Windows 7 x64 HP Retail
      • Monitor(s):
      • 24" 1920x1080 Iiyama LED
      • Internet:
      • 60MB VM

    Re: Creating your own formulas in Excel

    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

  3. #3
    Registered User
    Join Date
    Aug 2007
    Posts
    3
    Thanks
    0
    Thanked
    0 times in 0 posts

    Re: Creating your own formulas in Excel

    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

  4. #4
    Does he need a reason? Funkstar's Avatar
    Join Date
    Aug 2005
    Location
    Aberdeen
    Posts
    19,874
    Thanks
    630
    Thanked
    965 times in 816 posts
    • Funkstar's system
      • Motherboard:
      • Gigabyte EG45M-DS2H
      • CPU:
      • Intel Core2Quad Q9550 (2.83GHz)
      • Memory:
      • 8GB OCZ PC2-6400C5 800MHz Quad Channel
      • Storage:
      • 650GB Western Digital Caviar Blue
      • Graphics card(s):
      • 512MB ATI Radeon HD4550
      • PSU:
      • Antec 350W 80+ Efficient PSU
      • Case:
      • Antec NSK1480 Slim Mini Desktop Case
      • Operating System:
      • Vista Ultimate 64bit
      • Monitor(s):
      • Dell 2407 + 2408 monitors
      • Internet:
      • Zen 8mb

    Re: Creating your own formulas in Excel

    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

  5. #5
    Registered User
    Join Date
    Aug 2007
    Posts
    3
    Thanks
    0
    Thanked
    0 times in 0 posts

    Re: Creating your own formulas in Excel

    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?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel + Database + Automated
    By Kezzer in forum Software
    Replies: 4
    Last Post: 29-03-2007, 04:54 PM
  2. Excel windows
    By Steve in forum Software
    Replies: 4
    Last Post: 25-07-2006, 12:44 PM
  3. M$ Excel masters..
    By XA04 in forum Help! Quick Relief From Tech Headaches
    Replies: 9
    Last Post: 19-09-2005, 10:48 AM
  4. Replies: 6
    Last Post: 12-08-2005, 08:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •