MZI1A at YouTube has a column of completed projects; she also needs to show a column of projects that are incomplete.Today in Episode #1536, Mike 'ExcelisFun' Girvin and Bill 'MrExcel' Jelen set out to solve the issue and show the column of incomplete projects.
Dueling Excel Podcast #103...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
Dueling Excel Podcast #103...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
Transcript of the video:
Hey, welcome back it's another dueling Excel podcast live from Highline Community College.
Episode 102.
I'm Bill Jelen from MrExcel, here in the office of Mike Excel Is Fun Girvin.
This is 10, 3, 103 then where does the time go.
Mike: It's late.
Bill: Last week was 102, I feel like I've been here for six weeks.
Mike: Oh! No, it's great.
Bill: I wear the same shirt every time.
Mike: That's great we been have lots of fun.
Bill: mzi1a at YouTube as a list these are all the projects that are done and she needs to get a column over here of which projects are not done.
So, so I'm gonna do equal IF, ISNA, VLOOKUP the row of A1, which is the geekiest way of putting in number one over in this table here, press F4 comma 1 comma false, realize I could have done that with a MATCH, but it's too late I've already typed the VLOOKUP, if that is N/A, that means that's missing we actually need that number.
So, row of A1 otherwise we want to quote quote I'll copy down 12 cells.
So, we're missing 3 8 and 11.
Now, we want to get that in a nice little column.
So, check this equal SMALL.
The SMALL of that column comma row of A1, copy that down, okay how about equal IFERROR that whole thing just give me a quote quote, there we go.
So, later on if 2 is missing, it shows up in the list isn't that sweet, there you go.
All right, Mike.
Mike: Wow! That's gonna be a lot, a lot simpler than what I do.
Thanks MrExcel.
All right! So, I'm gonna take this here's some numbers here and I'm going to try and do it in single formula and a copy down and have those numbers pop out I need to, in a sense compare these numbers in a column to another column of numbers, but with all the numbers.
So, it should be one to twelve.
I'm going to build those numbers inside of a formula using ROW and then INDIRECT, in text one colon and double quotes ampersand, then I'm going to say MAX.
The MAX of whatever's in here.
Now, what this will do is this will create one to twelve, INDIRECT will created as a text reference and ROW, will convert the text reference to a reference and row will give me the number side hit, hit F9.
There's my numbers one to twelve, I need to match these against those. So, Control+Z.
I'm gonna copy this little piece right here. Now, I'm going to MATCH.
I'm going to use the MATCH function. I'm going to say hey, lookup value yeah that's an array one to twelve.
I'm going to say are any of those numbers over here comma 0, close parenthesis.
If I highlight this and hit F9, I get a bunch of N/A's and the N/A's are what I want 1, 2, N/A.
So, I'm going to Control+Z, and say ISNA, that gives me some trues and falses.
Now, I'm gonna put this inside the SMALL and I'm gonna say inside the SMALL, I'm going to say IF and that ISNA, tells me a true where the number is supposed to be if logical test comma and now, I'm gonna say just this part right here that's hard to see it's the green one.
This is the part that gives me 1 to 12 this hole ISNA, whatever gives me the true, where there should be a number and then I'm going to paste that, close parenthesis on the IF and then I'm gonna say that's the array comma and then I'm in cell C1.
So, I'm going to take see dollar sign 1 colon C1, I can already see I have a semicolon right and then there I close that off, Control+Shift+Enter, it doesn't look like it's working there's a bunch of zeros.
Bill: They are missing zeros and ones done projects area they forgot about projects.
Mike: You know, they forgot a lot they really, really on that one a lot.
Oh! Look at that I typed too many commas Control+Shift+Enter and then that's not it either.
Oh! Look at that, so we better put some dollar signs the hazards of shooting videos late at night and through all cross my fingers, Control+Shift+Enter and then we say if they're all right, Control+Shift+Enter and then.
Bill: Okay here's what I understand and you're gonna help me out here finally, you got a nice array formula and then you wrap an IFERROR around that which is not an array formula.
How does it know that the IFERROR is not an array and everything on the inside isn't array, how does it know that.
Mike: No because small is delivering a single value here, but there, there's certain parts it's an argument thing so.
Bill: Follows down to a single value.
Mike: Small single bit down to single value, but you know equals INDEX if you put an array right there, or in SUMPRODCUT, SUMPRODUCT those arguments understand arrays and don't need Control+Shift+Enter, but if you do equals IF, that right argument right there only accepts a single value.
If you put an array in there no matter what, it's going to require Control+Shift+Enter.
You know, Microsoft functions kind of our inconsistent sometimes but as far as I can tell it comes down to it arguments in a function.
Throw it back to MrExcel.
Bill: All right! Well, hey, I want to thank everyone for stopping by.
We'll see you next time for another dueling excel podcast from MrExcel and Excel Is Fun.
Episode 102.
I'm Bill Jelen from MrExcel, here in the office of Mike Excel Is Fun Girvin.
This is 10, 3, 103 then where does the time go.
Mike: It's late.
Bill: Last week was 102, I feel like I've been here for six weeks.
Mike: Oh! No, it's great.
Bill: I wear the same shirt every time.
Mike: That's great we been have lots of fun.
Bill: mzi1a at YouTube as a list these are all the projects that are done and she needs to get a column over here of which projects are not done.
So, so I'm gonna do equal IF, ISNA, VLOOKUP the row of A1, which is the geekiest way of putting in number one over in this table here, press F4 comma 1 comma false, realize I could have done that with a MATCH, but it's too late I've already typed the VLOOKUP, if that is N/A, that means that's missing we actually need that number.
So, row of A1 otherwise we want to quote quote I'll copy down 12 cells.
So, we're missing 3 8 and 11.
Now, we want to get that in a nice little column.
So, check this equal SMALL.
The SMALL of that column comma row of A1, copy that down, okay how about equal IFERROR that whole thing just give me a quote quote, there we go.
So, later on if 2 is missing, it shows up in the list isn't that sweet, there you go.
All right, Mike.
Mike: Wow! That's gonna be a lot, a lot simpler than what I do.
Thanks MrExcel.
All right! So, I'm gonna take this here's some numbers here and I'm going to try and do it in single formula and a copy down and have those numbers pop out I need to, in a sense compare these numbers in a column to another column of numbers, but with all the numbers.
So, it should be one to twelve.
I'm going to build those numbers inside of a formula using ROW and then INDIRECT, in text one colon and double quotes ampersand, then I'm going to say MAX.
The MAX of whatever's in here.
Now, what this will do is this will create one to twelve, INDIRECT will created as a text reference and ROW, will convert the text reference to a reference and row will give me the number side hit, hit F9.
There's my numbers one to twelve, I need to match these against those. So, Control+Z.
I'm gonna copy this little piece right here. Now, I'm going to MATCH.
I'm going to use the MATCH function. I'm going to say hey, lookup value yeah that's an array one to twelve.
I'm going to say are any of those numbers over here comma 0, close parenthesis.
If I highlight this and hit F9, I get a bunch of N/A's and the N/A's are what I want 1, 2, N/A.
So, I'm going to Control+Z, and say ISNA, that gives me some trues and falses.
Now, I'm gonna put this inside the SMALL and I'm gonna say inside the SMALL, I'm going to say IF and that ISNA, tells me a true where the number is supposed to be if logical test comma and now, I'm gonna say just this part right here that's hard to see it's the green one.
This is the part that gives me 1 to 12 this hole ISNA, whatever gives me the true, where there should be a number and then I'm going to paste that, close parenthesis on the IF and then I'm gonna say that's the array comma and then I'm in cell C1.
So, I'm going to take see dollar sign 1 colon C1, I can already see I have a semicolon right and then there I close that off, Control+Shift+Enter, it doesn't look like it's working there's a bunch of zeros.
Bill: They are missing zeros and ones done projects area they forgot about projects.
Mike: You know, they forgot a lot they really, really on that one a lot.
Oh! Look at that I typed too many commas Control+Shift+Enter and then that's not it either.
Oh! Look at that, so we better put some dollar signs the hazards of shooting videos late at night and through all cross my fingers, Control+Shift+Enter and then we say if they're all right, Control+Shift+Enter and then.
Bill: Okay here's what I understand and you're gonna help me out here finally, you got a nice array formula and then you wrap an IFERROR around that which is not an array formula.
How does it know that the IFERROR is not an array and everything on the inside isn't array, how does it know that.
Mike: No because small is delivering a single value here, but there, there's certain parts it's an argument thing so.
Bill: Follows down to a single value.
Mike: Small single bit down to single value, but you know equals INDEX if you put an array right there, or in SUMPRODCUT, SUMPRODUCT those arguments understand arrays and don't need Control+Shift+Enter, but if you do equals IF, that right argument right there only accepts a single value.
If you put an array in there no matter what, it's going to require Control+Shift+Enter.
You know, Microsoft functions kind of our inconsistent sometimes but as far as I can tell it comes down to it arguments in a function.
Throw it back to MrExcel.
Bill: All right! Well, hey, I want to thank everyone for stopping by.
We'll see you next time for another dueling excel podcast from MrExcel and Excel Is Fun.
