# Math help: create a formula from input data **URL:** \ **Category:** Factual Questions\ **Created:** [October 15, 2007, 3:52pm UTC](https://boards.straightdope.com/t/math-help-create-a-formula-from-input-data/422794 "2007-10-15T15:52:47Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![steadierfooting](https://avatars.discourse-cdn.com/v4/letter/s/43a26b/32.png) [@steadierfooting](https://boards.straightdope.com/u/steadierfooting)\ **Post date:** [October 15, 2007, 3:52pm UTC](https://boards.straightdope.com/t/math-help-create-a-formula-from-input-data/422794/1 "2007-10-15T15:52:47Z")
I’ve been trying to search google for help on this, but I feel I’m not using the correct search terminology. My coworkers often come up with absurd topics, then graph it and come up with a formula. The topic today was scoring situational hotness vs. actual hotness, cause like, a girl at a bookstore is ‘hotter’ than outside. Or conversely, an unattractive girl is more attractive in an all guys school. So what I’d like is a formula that converts ‘hotness’ to the result of the situational hotness based off of the input data: H S H 0 H+.25 1 H+.25 2 H+0.5 3 H+0.5 4 H+0.5 5 H+1 6 H+1 7 H+1 8 H+2 9 H+1 10 H+0 I remember in my physics lab in college there’s a way to come up with an approximate formula using logs (maybe?) So what I’m looking for is an approximate answer to the question, but if it is too complex because of the randomly assigned bias, then a website pointing to instructions on how to accomplish this for future endeavors. Oh uhhh, totally work related too, I swear. ---
**Author:** ![mnemosyne](https://avatars.discourse-cdn.com/v4/letter/m/c4cdca/32.png) [@mnemosyne](https://boards.straightdope.com/u/mnemosyne)\ **Post date:** [October 15, 2007, 4:42pm UTC](https://boards.straightdope.com/t/math-help-create-a-formula-from-input-data/422794/2 "2007-10-15T16:42:00Z")
Have you tried putting your values into Excel, with your independent variables being 0-10 and your dependents being 0.25 - 10? Plot that graph, then see if the linear, log, exponential or other approximations available match the data closely enough for your purposes. I don’t have Excel, and OpenOffice doesn’t seem to have that function, so I can’t try it for you. ---
**Author:** ![Santo\_Rugger](https://avatars.discourse-cdn.com/v4/letter/s/e95f7d/32.png) [@Santo\_Rugger](https://boards.straightdope.com/u/Santo_Rugger)\ **Post date:** [October 15, 2007, 5:05pm UTC](https://boards.straightdope.com/t/math-help-create-a-formula-from-input-data/422794/3 "2007-10-15T17:05:49Z")
I don’t get it? ---
**Author:** ![steadierfooting](https://avatars.discourse-cdn.com/v4/letter/s/43a26b/32.png) [@steadierfooting](https://boards.straightdope.com/u/steadierfooting)\ **Post date:** [October 15, 2007, 5:13pm UTC](https://boards.straightdope.com/t/math-help-create-a-formula-from-input-data/422794/4 "2007-10-15T17:13:20Z")
I do have access to excel, and looked around for options like that, but couldn’t find anything… ---
**Author:** ![scr4](https://avatars.discourse-cdn.com/v4/letter/s/59ef9b/32.png) [@scr4](https://boards.straightdope.com/u/scr4)\ **Post date:** [October 15, 2007, 5:19pm UTC](https://boards.straightdope.com/t/math-help-create-a-formula-from-input-data/422794/5 "2007-10-15T17:19:36Z")
There’s no universal way to do it. In sciences, you usually have some idea of what the function looks like, and you fiddle with the parameters to make it fit the data. For example, if you think it the data should be on a straight line, you use y=a x + b, and find the values of **a** and **b** that fit the data. Look up “least squares fitting” if you want the actual method. If you have no idea what function to use, you have to pick one that has the right shape. You end up with an “[empirical formula](http://en.wikipedia.org/wiki/Empirical_formula)” - i.e. a formula that describes observations without _explaining_ why it’s that way. If it’s a smooth curve, a polynomial is a common choice. (I.e. use “y = a + b x + c x[sup]2[/sup]…”, as many as you need/want.) ---
**Author:** ![steadierfooting](https://avatars.discourse-cdn.com/v4/letter/s/43a26b/32.png) [@steadierfooting](https://boards.straightdope.com/u/steadierfooting)\ **Post date:** [October 15, 2007, 5:57pm UTC](https://boards.straightdope.com/t/math-help-create-a-formula-from-input-data/422794/6 "2007-10-15T17:57:46Z")
I found out how to do it in excel, it involved using scattergraphs in stead of line graphs, and messing around with some of the options to create the formula using ‘Add Trendline’. SCR provided a good starting point to learn how to do it on my own. ---
**Author:** ![Snarky\_Kong](https://avatars.discourse-cdn.com/v4/letter/s/a183cd/32.png) [@Snarky\_Kong](https://boards.straightdope.com/u/Snarky_Kong)\ **Post date:** [October 15, 2007, 6:34pm UTC](https://boards.straightdope.com/t/math-help-create-a-formula-from-input-data/422794/7 "2007-10-15T18:34:06Z")
So what’s the independent variable if the dependent is hotness? ---
**Author:** ![ultrafilter](https://avatars.discourse-cdn.com/v4/letter/u/3d9bf3/32.png) [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)\ **Post date:** [October 15, 2007, 8:54pm UTC](https://boards.straightdope.com/t/math-help-create-a-formula-from-input-data/422794/8 "2007-10-15T20:54:06Z")
[QUOTE=scr4] If you have no idea what function to use, you have to pick one that has the right shape. You end up with an “[empirical formula](http://en.wikipedia.org/wiki/Empirical_formula)” - i.e. a formula that describes observations without _explaining_ why it’s that way. If it’s a smooth curve, a polynomial is a common choice. (I.e. use “y = a + b x + c x[sup]2[/sup]…”, as many as you need/want.) [/QUOTE] In general, we try to discourage cubic and higher-order least squares models because the coefficients become hard to interpret, and they don’t behave well between the points they were fitted to. Based on the data in the OP, I’d say a quadratic model would probably fit well without being too complicated. ---
**Author:** ![Chronos](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/chronos/32/134_2.png) [@Chronos](https://boards.straightdope.com/u/Chronos)\ **Post date:** [October 15, 2007, 11:22pm UTC](https://boards.straightdope.com/t/math-help-create-a-formula-from-input-data/422794/9 "2007-10-15T23:22:54Z")
> [@](#): > > In general, we try to discourage cubic and higher-order least squares models because the coefficients become hard to interpret, and they don’t behave well between the points they were fitted to. Right, given any N data points, you can always find a polynomial of degree N-1 which will fit them exactly (and an infinite number of polynomials of higher order). But even though that polynomial will go exactly through all of the data points, it’ll go wild and crazy in between them, and even wilder and crazier outside of the fitted region. Plus, it’ll be just as easy to just give someone the list of original data points as to give them the function, so you’re not making things any simpler. You always want to fit things using a function with much fewer parameters than you have data points. ---
**Author:** ![Squink](https://avatars.discourse-cdn.com/v4/letter/s/b5e925/32.png) [@Squink](https://boards.straightdope.com/u/Squink)\ **Post date:** [October 15, 2007, 11:32pm UTC](https://boards.straightdope.com/t/math-help-create-a-formula-from-input-data/422794/10 "2007-10-15T23:32:03Z")
[QUOTE=ultrafilter] In general, we try to discourage cubic and higher-order least squares models because the coefficients become hard to interpret… [/QUOTE] However, if you _want_ a perfect fit, and to hell with the consequences, it’s hard to beat the [Lagrange Interpolating Polynomial](http://mathworld.wolfram.com/LagrangeInterpolatingPolynomial.html). ---
**Author:** ![ultrafilter](https://avatars.discourse-cdn.com/v4/letter/u/3d9bf3/32.png) [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)\ **Post date:** [October 16, 2007, 12:13am UTC](https://boards.straightdope.com/t/math-help-create-a-formula-from-input-data/422794/11 "2007-10-16T00:13:54Z")
[QUOTE=Squink] However, if you _want_ a perfect fit, and to hell with the consequences, it’s hard to beat the [Lagrange Interpolating Polynomial](http://mathworld.wolfram.com/LagrangeInterpolatingPolynomial.html). [/QUOTE] If all you care about is the fit, just draw the line segments between adjacent points. Doesn’t get much simpler than that. ---
**Author:** ![Squink](https://avatars.discourse-cdn.com/v4/letter/s/b5e925/32.png) [@Squink](https://boards.straightdope.com/u/Squink)\ **Post date:** [October 16, 2007, 12:48am UTC](https://boards.straightdope.com/t/math-help-create-a-formula-from-input-data/422794/12 "2007-10-16T00:48:38Z")
[QUOTE=ultrafilter] If all you care about is the fit, just draw the line segments between adjacent points. Doesn’t get much simpler than that. [/QUOTE] **steadierfooting** wants a _formula_.