Nick asks how to do a pretty lengthy bonus calculation. It might be too complex for the Excel 2003 nested IF limit, so I decided to use the range version of VLOOKUP. But...as a twist, I embed the table right in the formula. Episode 1135 shows you how.
This is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey alright, welcome back to the MrExcel netcast, I'm Bill Jelen.
This question sent by Nick from YouTube, I feel really bad, Nick sent this in just before I was leaving for Columbus Indiana, and I said “Nick, I'm going to give you the formula, I don't have time to explain it, I promise I'll do a podcast though, to catch up with this.” Nick has a bunch of sales, and he's going to calculate the bonus, here's his bonus table.
He sell over 1000, you get a $25 bonus, over 3000, get $125 bonus, and it would be tempting here to start out with the big huge nested IF statement, right?
But in Excel 2003 you can't nest more than 7, and this table’s too big, so it's not going to work.
I'm going to use the other version, a =VLOOKUP, go look up that value, 468, in this table, and we want that to be locked out, of course, and then ,2 for the second column.
And normally, where we put ,FALSE we would put a TRUE, but it turns out you don't have to put TRUE, you can just close that, and will copy it down.
Now the cool thing about this sorted version of VLOOKUP, the TRUE version, is that 828 doesn't have to be in the table, right?
What it does is, it finds the value less than the value if there is no match.
So here the 2501, it goes back and grabs the 100 from 2500, alright, looks cool.
Now, here's what I felt bad about for Nick.
I didn't want to explain to Nick how to set up this table over here, so I came to my formula, clicked F2, and went right to where that table is, select those characters, and press F9, alright, which inserts the table directly into the formula.
What a beautiful thing, that way I had a nice compact formula I could send a Nick, but you know, how are you ever going to figure out what the heck that does?
Now this is a cool trick, look up here, it actually embeds the table, you can get rid of the table then, and we're good to go.
I guess I need to copy that down, don’t I?
And our formulas work everywhere, alright, so a good trick.
Now, when I was in Columbus, I showed this trick, it's kind of one of the new tricks in my seminar, and people said “Well wait, that's fine, until you need to edit the table, then what are you going to do?
You’re going to have to try and parse through this whole thing?” Well, you know the cool trick here is, we're going to select all of those cells like that, right, Ctrl+C to copy, and then here select the right-sized range.
Do =, Ctrl+V, Ctrl+Shift+Enter, and the table comes right back to life, oh, I didn't select enough, comes right back to life, I can then edit it and re-embed it later.
So cool, cool trick.
Nick, thanks for sending that question in, sorry I gave you the very bizarre formula there, hope this makes sense now.
Thanks to everyone else for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey alright, welcome back to the MrExcel netcast, I'm Bill Jelen.
This question sent by Nick from YouTube, I feel really bad, Nick sent this in just before I was leaving for Columbus Indiana, and I said “Nick, I'm going to give you the formula, I don't have time to explain it, I promise I'll do a podcast though, to catch up with this.” Nick has a bunch of sales, and he's going to calculate the bonus, here's his bonus table.
He sell over 1000, you get a $25 bonus, over 3000, get $125 bonus, and it would be tempting here to start out with the big huge nested IF statement, right?
But in Excel 2003 you can't nest more than 7, and this table’s too big, so it's not going to work.
I'm going to use the other version, a =VLOOKUP, go look up that value, 468, in this table, and we want that to be locked out, of course, and then ,2 for the second column.
And normally, where we put ,FALSE we would put a TRUE, but it turns out you don't have to put TRUE, you can just close that, and will copy it down.
Now the cool thing about this sorted version of VLOOKUP, the TRUE version, is that 828 doesn't have to be in the table, right?
What it does is, it finds the value less than the value if there is no match.
So here the 2501, it goes back and grabs the 100 from 2500, alright, looks cool.
Now, here's what I felt bad about for Nick.
I didn't want to explain to Nick how to set up this table over here, so I came to my formula, clicked F2, and went right to where that table is, select those characters, and press F9, alright, which inserts the table directly into the formula.
What a beautiful thing, that way I had a nice compact formula I could send a Nick, but you know, how are you ever going to figure out what the heck that does?
Now this is a cool trick, look up here, it actually embeds the table, you can get rid of the table then, and we're good to go.
I guess I need to copy that down, don’t I?
And our formulas work everywhere, alright, so a good trick.
Now, when I was in Columbus, I showed this trick, it's kind of one of the new tricks in my seminar, and people said “Well wait, that's fine, until you need to edit the table, then what are you going to do?
You’re going to have to try and parse through this whole thing?” Well, you know the cool trick here is, we're going to select all of those cells like that, right, Ctrl+C to copy, and then here select the right-sized range.
Do =, Ctrl+V, Ctrl+Shift+Enter, and the table comes right back to life, oh, I didn't select enough, comes right back to life, I can then edit it and re-embed it later.
So cool, cool trick.
Nick, thanks for sending that question in, sorry I gave you the very bizarre formula there, hope this makes sense now.
Thanks to everyone else for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
