Pop-Up Thingie

>>> Magnum BBS <<<
  • Home
  • Forum
  • Files
  • Log in

  1. Forum
  2. Usenet
  3. MSOFT.PUB.EXCEL.WORKSHEET
  • Re: LINEST with r2 = -1.18 ???

    From Ryan Petitt@21:1/5 to Jerry W. Lewis on Tue May 2 09:08:31 2023
    On Wednesday, September 26, 2001 at 9:39:44 AM UTC-4, Jerry W. Lewis wrote:
    LINEST() (also SLOPE(), INTERCEPT(), VAR(), STDEV(), LOGEST(), TREND(), FORECAST(), etc.) uses a numerically unstable algorithm. With
    challenging data (such as yours), rounding error has accumulated to the point that none of its calculations (slope, intercept, etc.) can be believed. In your case, you were lucky enough to get an impossible R^2,
    so that it was obvious that there was a problem. There may still be a problem even with data that give more reasonable R^2 values. These
    problems with Excel's algorithms have been well documented for years
    (cf. Sawitzki, 1994, "Report on the reliability of data analysis
    systems" Comput. Statist. Data Anal. 18:289-301) yet Microsoft continues
    to ignore them.
    Harlan Grove's matrix formulation simply recreates the same problem.
    DEVSQ(), COVAR(), and CORREL() are the only 2nd moment functions in
    Excel that are numerically reliable. For simple linear regression, use
    the following formulas instead of LINEST(), SLOPE(), INTERCEPT(), RSQ(), STEYX(), etc.
    slope = COVAR(y,x)/DEVSQ(x)*COUNT(y)
    intercept = AVERAGE(y) - slope*AVERAGE(x)
    rsq = CORREL(y,x)^2
    SSreg = rsq*DEVSQ(y)
    SSresid = (1-rsq)*DEVSQ(y)
    df = COUNT(y)-2
    F = SSreg/SSresid*df
    steyx = SQRT(SSresid/df)
    se1 = steyx/SQRT(DEVSQ(x))
    seb = steyx/SQRT(1/COUNT(y)+AVERAGE(x)^2/DEVSQ(x))
    This approach has the added advantage over LINEST that it allows missing values in the data range. However that cuts both ways, because they
    will give a wrong answer if there are data pairs where only x or y (but
    not both) are missing.
    Similarly, for univariate statistics use the following formulas instead
    of VAR(), VARP(), STDEV(), and STDEVP()
    var = DEVSQ(x)/(COUNT(x)-1)
    varp = DEVSQ(x)/COUNT(x)
    stdev = SQRT(var)
    stdevp = SQRT(varp)
    Since Microsoft has already programmed routines that would be superior
    to their unstable routines, it is puzzling why they continue to maintin redundant inferior code. The unstable formulas that Excel programed are mathematically exact (with infinite precision), so my formulas will
    agree with the Excel functions for non-challenging data sets. When they disagree, the dedicated Excel functions are wrong.
    There is no DEVSQA function, there is no hel for VARA(), VARPA(),
    STDEVA(), or STDEVPA() other than doing those calculations manually.
    If you are wedded to using LINEST(), then test to see if
    STDEV(x) = SQRT(DEVSQ(x)/COUNT(x))
    STDEV(y) = SQRT(DEVSQ(y)/COUNT(y))
    PEARSON(y,x) = CORREL(y,x)
    If all three of these are approximately true (say to at least 12 figures each), then LINEST() can probably be believed for simple linear
    regression. Figuring out when LINEST() can be believed for more complex models is not so simple.
    Jerry
    Richard Nolan wrote:
    Having used LINEST for Linear regression, I think
    successfully a few times, I now have a data set that
    returns an r2 value of -1.18, which is not possible. I can
    look at the data and tell r2 must be +, not negative.

    Are there two logic problems with LINEST. (a) r2 can never
    greater than +/- 1, and (b) I can see the relationship is
    +, not -.

    This answer was invaluable! Even 20+ years later... the LINEST replacements were especially helpful for a layman trying to get results from a non-contiguous data set. However, I think the seb formula has a typo:
    seb = steyx/SQRT(1/COUNT(y)+AVERAGE(x)^2/DEVSQ(x))
    should be
    seb = steyx*SQRT(1/COUNT(y)+AVERAGE(x)^2/DEVSQ(x))

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From TEAM DC@21:1/5 to All on Wed Nov 22 23:30:04 2023
    Are you looking to buy Quality Pain, Anxiety pills, depression Medications and Research Chemicals for research purposes? Look no further! We guarantee the quality and authenticity of our products with a 99.99% assurance. In addition, you can also
    purchase legal hallucinogens, Anxiety or Stress meds at a fair price.

    The JASON Group is making it possible so all patients in all communities can be able to benefit from their best medical supplies and contant medication supply all over the United States. Our stores are open to help you get all your medical needs without
    any delay. Ship worldwide (USPS, FedEx, Aramex, UPS, and DHL


    ORDER DIRECTLY ON OUR WEBSITE https://www.jasonscottpharmaceuticals.net/

    Buy Xanax Bars Online: https://www.jasonscottpharmaceuticals.net/

    Oxycodone powder : https://www.jasonscottpharmaceuticals.net/product/oxycodone-powder/

    Buy Oxycodone tablets: https://www.jasonscottpharmaceuticals.net/product/oxycodone-powder/

    Buy Alprazolam Powder : https://jasonresearchlab.com/


    Buy Alprazolam Powder Online: https://www.jasonscottpharmaceuticals.net/

    Buy Percocet Online: https://www.jasonscottpharmaceuticals.net/

    Buy Diazepam Online: https://www.jasonscottpharmaceuticals.net/

    Buy Valium Online: https://www.jasonscottpharmaceuticals.net/

    Spravato Esketamine Nasal Spray : https://www.jasonscottpharmaceuticals.net/product/spravato-esketamine-nasal-spray/

    Buy Ritalin 20mg Online: https://www.jasonmeds.com

    Buy Tramadol Pills/ Capsules Online: https://www.jasonscottpharmaceuticals.co

    Buy Ketamine Nasal Spray Online: https://www.jasonscottpharmaceuticals.net/

    Buy DMT .5ml Purecybin – 300mg DMT Online: https://www.jasonscottpharmaceuticals.net/

    Calypsol Ketamine 500mg/10ml : https://www.jasonscottpharmaceuticals.net/product/calypsol-ketamine-500mg-10ml/

    Buy Dmt Online: https://jasonmeds.com/product-category/micro-dose-lsd/

    Fubinaca Powder for sale https://jasonresearchlab.com/

    Buy LSD online: https://www.jasonscottpharmaceuticals.net/

    Buy Adderall XR Capsules/Pills Online: https://www.jasonscottpharmaceuticals.net/

    Buy Quality Adderall XR 30MG Capsule Online: https://www.jasonscottpharmaceuticals.co

    Spravato Esketamine Nasal Spray : https://www.jasonscottpharmaceuticals.net/product/spravato-esketamine-nasal-spray/

    Buy DeadHead Chemist DMT Vape Cartridge: https://www.jasonscottpharmaceuticals.net/

    Actavis Promethazine Codeine cough syrup : https://www.jasonscottpharmaceuticals.net/product/actavis-promethazine-codeine-cough-syrup/

    Buy 5-MEO DMT .5ml 150mg Mushrooms Canada Online: https://jasonmeds.com/product-category/micro-dose-lsd/

    Where to Order Xanax 2mg bars pfizer Online, Buy Mexican Blues 30S, Black tar H, Clear, Yayo, Website: https://www.jasonscottpharmaceuticals.net/

    China White, Percocets, Valium, Adderall(IR & XR), Methadone, Hydrocodone, Diazepam, Dilaudid, Oxycotin, Roxycodone, Subutex, Klonpin, Soma, Order Ritalin Online at https://www.jasonscottpharmaceuticals.co

    Our Telegram: https://t.me/jason_touchdowns
    Discreet shipping: https://www.youtube.com/shorts/B-dr9wiO46I
    See Shipping: https://www.youtube.com/channel/UCnZ0zwLKNTPW2_FzzEXgmIw

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • Who's Online

  • Recent Visitors

    • Bob Worm
      Tue Jun 10 18:05:17 2025
      from Wales, Uk via Telnet
    • Plume
      Tue Jun 10 17:23:42 2025
      from Uk via SSH
    • Bob Worm
      Tue Jun 10 13:56:21 2025
      from Wales, Uk via Telnet
    • Centurion
      Tue Jun 10 04:44:57 2025
      from Berea, Ohio via Telnet
    • Bob Worm
      Mon Jun 9 23:08:44 2025
      from Wales, Uk via Telnet
    • Bob Worm
      Mon Jun 9 21:49:37 2025
      from Wales, Uk via Telnet
    • Plume
      Mon Jun 9 20:39:48 2025
      from Uk via SSH
    • Michal Wronka
      Mon Jun 9 19:31:41 2025
      from Wroclaw, Poland via Telnet
  • System Info

    Sysop: Keyop
    Location: Huddersfield, West Yorkshire, UK
    Users: 489
    Nodes: 16 (2 / 14)
    Uptime: 56:22:08
    Calls: 9,673
    Calls today: 4
    Files: 13,719
    Messages: 6,170,994

© >>> Magnum BBS <<<, 2025