Take the 2-minute tour ×
Mathematics Stack Exchange is a question and answer site for people studying math at any level and professionals in related fields. It's 100% free, no registration required.

I am trying to implement Microsoft Excel's GROWTH function in JavaScript. This function calculates predicted exponential growth by using existing data. What makes it tricky is that it must work with multiple sets of known_x's values. I could not find any reference equation. Any suggestions? The part that might be relevant is the reference equation. I cross-posted the question here because someone suggested it in response to the same question originally posted on stackoverflow.

Thanks in advance for your help.

share|improve this question
    
I think this is irrelevant here. –  Ram Jan 4 '13 at 17:57
    
The part that might be relevant is the reference equation. I cross-posted the question here because someone suggested it in response to the same question originally posted on stackoverflow. Please accept my apologies if this was a mistake. Here is the original question: stackoverflow.com/questions/14161990/… –  ismael Jan 4 '13 at 18:03
    
Ah, I see. Maybe a different title would be helpful as you want help with the algorithm, but it looks like a coding question. –  gnometorule Jan 4 '13 at 18:04
    
@ismael Thats good, please add your above comment in the question itself that might help. –  Ram Jan 4 '13 at 18:04
2  
What if you took the logs of the y-values and used a least-squares algorithm to find the best-fit line? Exponentiate and you'd get a fit (with a slightly weighted distance scheme, but perhaps this isn't so important?) –  mixedmath Jan 4 '13 at 18:20
show 5 more comments

1 Answer 1

up vote 3 down vote accepted

Looks like the Excel GROWTH function is doing two things:

  1. fitting a function of the form $y(x) = b \cdot m^x$, where $b$ and $m$ are unknown constants, to a set of $(x_i,\,y_i)$ data points, and
  2. evaluating the resulting function at a given set of points.

Step 2 is trivial once you've found $b$ and $m$, so I won't go into that. The documentation for GROWTH doesn't specify how the data fitting is done, but a simple and obvious method would be to reduce the exponential fit to a linear one by taking the logarithm of the $y$ values, and then using simple linear regression to fit the function $\log y(x) = \alpha + \beta \cdot x$, where $\alpha = \log b$ and $\beta = \log m$, to the data points $(x_i,\, \log y_i)$.

(The values of $b$ and $m$ obtained in this way don't generally minimize the sum of squared errors $\sum_i (y(x_i) - y_i)^2$, but they do minimize the sum of squared logarithmic errors $$\sum_i (\log y(x_i) - \log y_i)^2 = \sum_i \left(\log \frac{y(x_i)}{y_i}\right)^2,$$ which one might argue to be a more natural measure for fitting an exponential growth function to data, anyway.)

Ps. I've posted an actual JavaScript implementation of this method on Stack Overflow. The results of the function match the example worksheet on the GROWTH documentation to within the numerical accuracy shown (which, admittedly, isn't much), suggesting that this may indeed be the method used by Excel.

(In fact, while I don't have Excel to test it with, downloading the worksheet and testing it in LibreOffice Calc gives results that match the JS code to about 12 significant digits. The remaining discrepancy is likely due to numerical inaccuracies in one or both implementations.)

share|improve this answer
    
You rock! Thanks a ton. I used your code and gave you credit at stoic.com/formula and in the codebase at stoic.com/formula/lib/formula.js –  ismael Jan 4 '13 at 21:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.