How to use excel to find out an algebraic equation. "What is the formula they're using?"

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

I'm not aware of any way to just ask Excel "What algebraic equation fits these data?"

Excel can solve regression problems, where we give Excel an algebraic expression (for example, price=A*age+B*quality+C) and ask Excel to find values for A, B, and C that provide the "best fit" of the data. If we want to use an ordinary least squares regression algorithm we can use Excel's built in LINEST() function to perform the regression. If we want to use a different regression algorithm, then we can use Excel's built in Solver utility to perform the optimization. But, in this scenario, we always start with the algebraic expression and find the parameters for that expression that give us the best fit. We cannot start without having some idea what the algebraic expression ought to look like.

One nice thing about spreadsheets is, when we get a calculation set up and working, we can usually make copies of that row/column/tab and repeat the process with a few changes. In this case, if we want to try a few different algebraic expressions, we can set up our spreadsheet to perform one regression, then make copies, changing the algebraic expression in each copy. This gives us a chance to compare regressions over different expressions and choose the one we like. Of course, this will quickly become overwhelming if we have no idea what expression to use and decide we want to try all the possible formulas.

If you know what kind of algebraic expression you want to use, we should be able to help program a regression for that algebraic expression.

When I don't know what kind of algebraic expression I want to use, I will usually start by graphing the data, with the intent of trying to create some "contour plots." The goal is to see if I can look at a graph of the data and, knowing the general shape of different families of algebraic curves, make some educated guesses about what kinds of algebraic expressions might be good candidates. As a first try, I might start by creating two scatter charts -- one showing price vs. age and one showing price vs. rating. See if those two charts give me any insights into how price varies with age and rating and see what that leads me to do next.