Statistical analysis (non-linear regression) of price data; make excel sheet (with graph) where user can tweak the degree of curve-fitting
Closed - This job posting has been filled and work has been completed.
Ok statsty excel people...
I have a set of around 50 datapoint-triplets (this will grow to around 100): 'name', 'popularity' and 'price paid'.
I'm attaching (a) an Excel file with the datapoints, in which you will also see a fourth column, showing price paid divided by popularity and some average stats.
The output I need from you is:
_an Excel graph/chart plotting the data, and some kind of regression curve through the data-points...I don't want a straight line result for this (see attachment (b) for what I am conceptualising...and yes, this is probably qualifies as the worst kind of data-mining, please don't hold it against me!)
_an Excel sheet clearly showing the raw data, showing all formulae required for the workings, and with changeable variables boxes for the user to play around with (further explanation of which 'variables' to be user-changeable below)
_your plain-English explanation of how it works, including a reasonable quantity of follow-up email Q&A style with me until I get it (I'm reasonably logical and have some stats knowledge, but not so handy with Excel)
The 'variables' I want to be able to play with essentially how 'fit'/smoothed the curve is...and how important particular datapairs are...for example I want to be able to tell it to either ignore or down-weight the importance of statistical outliers—defined by stdev from some 'average' relationship.
Also, it's within the brief scope for you to work out what the appropriate basic metrics are for the regression curve, but again, I want to be able to play with these...say, if you choose to base the regression curve on the MEDIAN of price paid per popularity, I need you to 'code' this as a variable, such that I could easily play with it, or better yet, be able to 'toggle' it with alternative metrics such as the MEAN, at will.
I also need to be able to accommodate those future datapairs so please make it easy to add them.