Back in podcast 621, I wondered why the function arguments box wont work with an array formula. It turns out that it works fine unless you switch applications while the dialog box is shown! In Episode 632, we take a look at why the function did not work, when it does work, and a cool workaround.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Let's revisit a question that came up in podcast 621.
Back then we were talking about using the function arguments and when we have an array formula.
So we display function arguments, fill in the function arguments and you see down here in the function argument says that it's going to return an array.
Now the person who asked the question said when I press "control shift enter" It does not correctly enter the formula as an array and when I tried it, it also didn't work man.
So I you know, kind of asked at the end of that podcast I said, you know, is there any way to get this function arguments to work?
Two people wrote in you know.
Joel Berg was the first one. He said what are you talking about?
Just press "control shift enter" and it works.
And I was starting to argue with with Joel and I said no it doesn't.
But then I tried it and sure enough it worked perfectly Now it turns out that when I tried it the first time, I had used function arguments, and then I had switched back to the email.
So I'd used Alt-Tab to go back to the email.
Alt-Tab to come back here to excel and even though function arguments was visible above the screen I had not returned focus. It turns out I had to click in the title bar.
Just something that simple returns focus and then "control shift enter" sure enough enters the value.
So pretty wild if you've ever had the function arguments not work, and it happens that you've switched to another application somehow you're losing focus.
It seems to be certainly a problem in Vista and I have excel 2003 run in Vista and it works there.
This older machine which is running Windows XP seems to handle it.
Ok, and so it's actually seems to be a newer problem.
Now a couple of other guys, Bob Meany and Michael Gervin wrote it and they said, you know, Hey, you know, if you want to avoid the the array formula completely, you know LINEST is going to give you two values basically in a row.
So you could ask for the index of LINEST create the function and in this case say we want the first row first value that will give us the slope and then ask for the index of LINEST.
Enter the function again and this time ask for the first row second value. That'll give us the y-intercept um, and they said, you know You can just avoid the whole problem of entering array forms is to begin with.
Ask for the specific values and you can get them easily without using "ctrl shift enter" So either way that finally clears up that problem back in podcast 621.
Thanks to everyone who wrote in, will be sending excel master pins out and thank you for your contributions Thanks to you for stopping by and we'll see you next time for another net cast from MrExcel.
Let's revisit a question that came up in podcast 621.
Back then we were talking about using the function arguments and when we have an array formula.
So we display function arguments, fill in the function arguments and you see down here in the function argument says that it's going to return an array.
Now the person who asked the question said when I press "control shift enter" It does not correctly enter the formula as an array and when I tried it, it also didn't work man.
So I you know, kind of asked at the end of that podcast I said, you know, is there any way to get this function arguments to work?
Two people wrote in you know.
Joel Berg was the first one. He said what are you talking about?
Just press "control shift enter" and it works.
And I was starting to argue with with Joel and I said no it doesn't.
But then I tried it and sure enough it worked perfectly Now it turns out that when I tried it the first time, I had used function arguments, and then I had switched back to the email.
So I'd used Alt-Tab to go back to the email.
Alt-Tab to come back here to excel and even though function arguments was visible above the screen I had not returned focus. It turns out I had to click in the title bar.
Just something that simple returns focus and then "control shift enter" sure enough enters the value.
So pretty wild if you've ever had the function arguments not work, and it happens that you've switched to another application somehow you're losing focus.
It seems to be certainly a problem in Vista and I have excel 2003 run in Vista and it works there.
This older machine which is running Windows XP seems to handle it.
Ok, and so it's actually seems to be a newer problem.
Now a couple of other guys, Bob Meany and Michael Gervin wrote it and they said, you know, Hey, you know, if you want to avoid the the array formula completely, you know LINEST is going to give you two values basically in a row.
So you could ask for the index of LINEST create the function and in this case say we want the first row first value that will give us the slope and then ask for the index of LINEST.
Enter the function again and this time ask for the first row second value. That'll give us the y-intercept um, and they said, you know You can just avoid the whole problem of entering array forms is to begin with.
Ask for the specific values and you can get them easily without using "ctrl shift enter" So either way that finally clears up that problem back in podcast 621.
Thanks to everyone who wrote in, will be sending excel master pins out and thank you for your contributions Thanks to you for stopping by and we'll see you next time for another net cast from MrExcel.
