Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need LINEST function to be applied with blank cells to get the slope parameter as well as the associated standard error. I can get the slope parameter with a normal function that may resolve the problem via other complementary functions, but not its standard error, I think. Is there any solution for using LINEST with blank cells in array form? Any ideas, please?

share|improve this question
I'm not clear what you are looking for - what do you mean by "associated standard error"? Can you give an example? – barry houdini Sep 26 '12 at 21:11
Unless by blank you mean 0 (with which the formula does work) I think you a trying to do what is just not possible (say derive A+B where you only know A) - unless of course your standard error is not as in regression analysis, which is where @barryhoudini's question comes in! – pnuts Sep 26 '12 at 22:05

I’m guessing you have something like

                                                Excel worksheet with missing data

where B = 2×A+3, but with some B data missing.  Filter out your blank Y (B) values:

filter dialog box->resulting filtered data view

Now select your data, type Alt+;, copy, and paste somewhere (probably a new sheet would be good).  You now have just your complete data:

                                              data with no rows with missing data

and you should be able to get your stats from that.

share|improve this answer
Thanks! This does exactly what I needed it to do. What exactly does the Alt+; shortcut do? I know it works, but I can't figure out what exactly it's doing. – stephenwade Jan 31 '13 at 6:41
@stephenwade: Its name is “Select Visible Cells” or “Select Visible Cells Only”. I guess the simplest explanation is that, if you have a bunch of cells selected, and some of them are hidden (by explicitly Hiding rows or columns, or through Filtering), this command de-selects the hidden ones. You can also perform this function by Home (tab) –> Editing (panel) –> Find & Select –> Go To Special –> Visible Cells Only. You can add it to your Quick Access Toolbar if you want (look for “Select Visible Cells” in the “Commands Not in the Ribbon” group). – Scott Jan 31 '13 at 20:40

Your Answer


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.