Linest and the if function

[stretch2011]

I've been working on this for about a week now. I am new to this site, but have read through previous linest problems. Sadly, I couldn't incorporate any to my specific case.

I am trying to set up a worksheet so that it gives a graph with a regression line and the R^2 (accomplished),calculates a low, ideal, and high values for the different regression lines (accomplished), and chooses the best regression line given R^2(accomplished).

The problem: Setting this up so that no formulas need to be changed given different quantities of cells. I had it working fine for a 10 shot process, but if any cells were left blank (i.e. 8 shot process) it returned a #value. I managed to use an if statement to work around this if 1 cell is blank. Although I must say it seems like the value if true and value if false should be switched (doesn't work that way). If excel had a loop function my life would be so much easier.

I can provide an example worksheet that has left out all the fancy stuff and focuses on creating a linest function that works for different amounts of cells, but I am not seeing where to attach documents on this site.

Thank you for any help you may provide.

 

[Aladin Akyurek]

Thank you Aladin, very helpful.

You are welcome.

GooberTrop: I am not, I'm doing all the regressions except for polynomials.


One more question if I may.

Do you know the linest, or logest equation for an exponential regression and a power regression?

Let me rephrase that. Do you know the linest, or logest "function" for an exponential regression and a power regression? I know the y= equations.

See the links GooberTron posted and the Excel help file on LOGEST, GROWTH, TREND, and FORECAST.

 

[stretch2011]

Those are very nice GooverTron, but my problem with it the growth functioin is it doesn't return all the statistical data.

I can manipulate the logest and linest functions to give me the correct r^2 and slope, or r^2 and y intercept, but never all 3 for either regressions. I used the index method to get the correct values, double checking against the graph. I was hoping to get the statistical info too, but if not then it is no huge deal.