Hey! Welcome back to the Mr. Excel net cast, I’m Bill Jelen. Basically, we start out with massive amounts of data and say, “How are we going to analyze this?” Well let’s fire up and hit the table, and see if we could solve this problem.
Hey! Welcome back to the Mr. Excel net cast. I’m Bill Jelen. This question sent in by Nick from You tube. 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 is his bonus table.
He sell over 1000, he got $25.00 bonus, over 3000, get $125.00 bonus. And it will be tempting here to start out with the big, huge nested “IF” statement alright? But in Excel 2003, you can’t nest more than 7 and his table is too big so it’s not going to work. I’m going to use the other version, a VLOOKUP, equal view lookup. Go lookup that value, 468 in this table and we want that to be locked up of course, and then comma two for the second column. And normally where we put comma FALSE, we would put a TRUE but it turns out you don’t have to put TRUE. You can just close that and we copy it down.
Now the cool thing about this sorted version of VLOOKUP, the TRUE version, is that 828 do not have to be in the table. What it does is it finds the value less than the value if there is no match. Here the 2501 it goes back and grabs the 100 from 2500. Looks cool!
Now here’s what I thought bad about for Nick. I didn’t want to explain to Nick how to setup this table over here so I came to my formula, click F2 and went right to where that table is, selected those characters and press F9 which inserts the table directly into the formula. What a beautiful thing, that we had nice compact formula that I could send to Nick but how are you going to figure out what that does?
Now this is a cool trick lookup here it actually embeds the table you can get rid of the table then, and we are good to go because I need to copy that down. And our formula is at work everywhere, a good trick. Now when I was in Columbus I showed this trick, it’s kind of I want new tricks in my seminar and people said, “Wait, that’s fine until you need to edit the table. Then what are you going to do?” You are not to try and press to this whole thing.
Well the cool trick here is we are going to select all of those cells like that. Control C to copy and then here select the right size range to equal, control V, control shift, enter and the table comes right back to life, I didn’t select enough, comes right back to life I can then edit it and re embed it later. Cool, cool trick! Nick thanks for sending that question in, sorry I gave you the very bizarre formula there. I hope this makes sense now.
Thanks everyone for stopping by, we will see you next time for another net cast from Mr. Excel.
Thanks for stopping by, we will see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services