Sometimes when you have a difficult formula like the one in Episode 386, you end up creating the formula in several steps. Once you have the formula working, but spread out over several columns, use the technique in this episode to bring everything back together into one megaformula. Episode 387 shows you how.
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.
On Friday’s netcast we had that really hard question from Ken in Chicago, about how to figure out the second Tuesday of November in every year.
And to figure out the solution, I ended up with four different formulas going across the worksheet.
Now is there some way that I can combine that into one single formula?
There's a couple of tricks that we can use to do that, that involve the copy and paste, but not the typical copy and paste, you have to copy and paste from the Formula bar.
So, when we set this up, I set up a column in column A that actually had the numbers from 1988 going forward.
Now if I wanted to make that be a simple formula, I can actually use ROW(A1988) which becomes a live formula, I could copy that down.
And basically then, without having to enter the numbers, I have a formula that will give me the right numbers.
So what I do is, I came out to my final formula that, you can see here, references C1 and B1.
And basically what I'll do is, I'll go back to the formula in C1, hit F2 to put it in Edit mode, and then select the characters in the formula bar except for the =, everything but the equal sign.
I'll hit Ctrl+C to copy, and then go back to my original formula in Edit mode, and every place where there's a C1, I'll select the character C1 and hit Ctrl+V to paste the C1 formula in.
So there's C1, I hit Ctrl+V, there’s C1, I hit Ctrl+V, OK.
And now I'm left with some references to B1, so let me go back and take a look at the B1 formula.
I'll edit that, select all the characters except for the =, Ctrl+C, and go back to my original formula, and everywhere where I have a B1 I'll Ctrl+V, B1 Ctrl+V, B1 Ctrl+V, and finally at the end, B1 Ctrl+V. And then, now I have some references to A1, I'll go back to A1, copy that formula, Ctrl+C, and then do the same thing, everywhere where I have an A1 going to paste, there we go.
So I'll copy that formula down, OK, there we go, Enter that.
And now, if that formula worked, we should be able to get rid of all the cells that were precedents of that formula, and there we go, everything still works.
When someone comes along to this formula, they'll be amazed at how long and complicated is, and they'll think “Wow, you must be MrExcel!” Great way to basically build a formula over several columns, and then once you get them, use the F2 key along with Ctrl+C. Make sure to copy just the characters from the formula bar, and then paste them into the final worksheet, and get your answer.
Hey, thanks for stopping by, we’ll see you tomorrow for another netcast from MrExcel!
On Friday’s netcast we had that really hard question from Ken in Chicago, about how to figure out the second Tuesday of November in every year.
And to figure out the solution, I ended up with four different formulas going across the worksheet.
Now is there some way that I can combine that into one single formula?
There's a couple of tricks that we can use to do that, that involve the copy and paste, but not the typical copy and paste, you have to copy and paste from the Formula bar.
So, when we set this up, I set up a column in column A that actually had the numbers from 1988 going forward.
Now if I wanted to make that be a simple formula, I can actually use ROW(A1988) which becomes a live formula, I could copy that down.
And basically then, without having to enter the numbers, I have a formula that will give me the right numbers.
So what I do is, I came out to my final formula that, you can see here, references C1 and B1.
And basically what I'll do is, I'll go back to the formula in C1, hit F2 to put it in Edit mode, and then select the characters in the formula bar except for the =, everything but the equal sign.
I'll hit Ctrl+C to copy, and then go back to my original formula in Edit mode, and every place where there's a C1, I'll select the character C1 and hit Ctrl+V to paste the C1 formula in.
So there's C1, I hit Ctrl+V, there’s C1, I hit Ctrl+V, OK.
And now I'm left with some references to B1, so let me go back and take a look at the B1 formula.
I'll edit that, select all the characters except for the =, Ctrl+C, and go back to my original formula, and everywhere where I have a B1 I'll Ctrl+V, B1 Ctrl+V, B1 Ctrl+V, and finally at the end, B1 Ctrl+V. And then, now I have some references to A1, I'll go back to A1, copy that formula, Ctrl+C, and then do the same thing, everywhere where I have an A1 going to paste, there we go.
So I'll copy that formula down, OK, there we go, Enter that.
And now, if that formula worked, we should be able to get rid of all the cells that were precedents of that formula, and there we go, everything still works.
When someone comes along to this formula, they'll be amazed at how long and complicated is, and they'll think “Wow, you must be MrExcel!” Great way to basically build a formula over several columns, and then once you get them, use the F2 key along with Ctrl+C. Make sure to copy just the characters from the formula bar, and then paste them into the final worksheet, and get your answer.
Hey, thanks for stopping by, we’ll see you tomorrow for another netcast from MrExcel!
