+ Reply to Thread
Results 1 to 5 of 5

Bell Curve Regression question

  1. #1
    Forum Contributor
    Join Date
    01-20-2015
    Location
    NB, Canada
    MS-Off Ver
    365
    Posts
    164

    Bell Curve Regression question

    What I have here is some data I collected on how a pitchers strikeout rate progresses as he ages.

    I am using a polynomial regression equation, however, I am not satisfied with my results.

    The issue that I have is that this equation weights all age groups as equal, however I have a count of how many times each age group was recorded.
    Prime ages 24-32etc have been recorded more, therefore their values should hold more weight.

    I have ran two tests:

    First with all age groups (17-39), the second with age groups that only have at least a count of 30 (17-34)

    Is there any way to make this regression equation more accurate ? Is their anyway to weight it ?

    Thanks
    Joe
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    05-06-2015
    Location
    Sri Lanka
    MS-Off Ver
    2016, 2007
    Posts
    135

    Re: Bell Curve Regression question

    please re-upload the file. it's corrupted. however, why don't you use statistical software instead excel? (minitab, SPSS, SAS...)

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,418

    Re: Bell Curve Regression question

    Is there any way to make this regression equation more accurate ? Is their anyway to weight it ?
    I'm not enough of a statistician to know the ins and outs of "weighting" points in something like this. When I have done "weighted" regressions, I have recreated the regression matrix so that each point has as many copies as weights. In your case, that would amount to 6500 data points. Which is ok, assuming Excel's LINEST() function can handle that large of an input matrix. If that is what you mean by "weighting", I think that will be the only way to approach it using the LINEST() function -- "expand" the regression matrix so there are, for example, 282 copies of the age 18 entry.

    If you are allowed to look beyond the LINEST() function, you can set the regression up using Solver. Select an objective function, guess at some parameters (you could conceivable use the unweighted regression as a starting point), compute your objective function, then call Solver and tell it to minimize/maximize your objective function by changing the regression parameters. Your spreadsheet currently has a column for "residuals" (column G). A simple sumproduct of the residuals with the weights (=SUMPRODUCT(G7:G29,B7:B29)) might make a suitable objective function. Put that somewhere, then call Solver and tell it to minimize this sumproduct() function by changing D31:D33.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Forum Contributor
    Join Date
    01-20-2015
    Location
    NB, Canada
    MS-Off Ver
    365
    Posts
    164

    Re: Bell Curve Regression question

    Thanks MrShorty,
    So I ran both methods, honestly I'm not entirely sure what I'm doing here, or which one looks best.
    I uploaded my excel sheet again with the weighted regression on sheet 2 and the solver on sheet 3.

    Dineth - I'm not sure why the file was corrupt for you, I was able to download it, as was MrShorty. I didn't use a stats program becauseI am unfamiliar with these programs as I have little background in statistics.


    Is their anything else I can try ?
    any other forums I should ask?
    If I had to choose the best method, which do you think fits best, ? (sorry, I know this is a Excel forum and not a math forum)
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,418

    Re: Bell Curve Regression question

    I cannot readily think of anything. The data are so "noisy", especially towards the ends, that all three regressions look "equally good" to me. I might even suggest that a quadratic might be "overfitting" the data, and you could limit yourself to a straight line and get an equally good fit.

    It looks like an interesting analysis.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Bell curve question
    By daveyc18 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 02-26-2016, 05:46 PM
  2. Replies: 2
    Last Post: 02-19-2015, 02:00 PM
  3. How to make a bell curve/statistical curve????
    By pittstonacl in forum Excel General
    Replies: 1
    Last Post: 08-04-2014, 10:49 AM
  4. Normal distribution curve / Bell curve
    By LAG1 in forum Excel General
    Replies: 0
    Last Post: 05-24-2012, 07:20 AM
  5. A bell curve
    By Nighteg in forum Excel General
    Replies: 0
    Last Post: 02-15-2012, 09:47 AM
  6. Bell Curve
    By Gre in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 11-09-2011, 04:24 AM
  7. Charting a Skew Bell Curve (statistics question)
    By sussexsilver in forum Excel General
    Replies: 0
    Last Post: 07-03-2009, 08:52 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1