No Links were listed yet. Go ahead and share!
Hey, welcome back to the Mr. Excel net cast I'm Bill Jelen. Basically you start out with massive amounts of data, see how were going to analyze this well let's fire up exhibit table and see if we can solve this problem.
Hey, welcome back to the Mr. Excel net cast I'm Bill Jelen. Well were going to back to episode 1073 this is where Sarah from the cattle farm in England asked about how to find the last match. Remember she has some vehicles and this is the millage and for this record. And then over here she wanted a formula that would find that millage for the last time that vehicle was fuel so last occurred here the millage was 11728 we've copied that down and B11 last match was here 12689 and that gets copied down. All right, well I had a formula and Daniel from Quebec sends in this formula using a lookup instead of V lookup and he’s doing—looking in for number and then one divided by A1 to A21 equal to A22 and the results vector is B1 to B22 and I had to tell you I looked to that and said there is no way that’s going to work but anytime that some tells me the formula and it actually does what do I do, I come back to the firmest and to have and say, let's turn on evaluate formula and see how this bad boy is actually working.
So we evaluate formula first thing we want it wants to do is figure out what A22 is what else can E14, so I get that and then it goes through all of this values, vehicle E14, F 15 and compares it as well as its going to do. So the next step, we’re going to see a whole bunch of true and false. All right, false, true, false, false all right, still don’t se where this is going. And then I noticed that were about to do one divided by this whole bigger ray. One divided this whole bigger ray well true is one, so one divided by one is going to be the number one. But false is actually stored as a zero, so one divided by zero is going to generate a whole bunch of errors and sure enough when I do evaluate you see that we get a whole bunch of div by zeros but occasionally a one. And then what the program is doing is that hey go find it two within this ray.
Well of course there are now two’s but just like in episode 1073 where I used a match function what's going to happened is, its going to keep looking for the two, its never going to find a two so its going to give me the last one in this ray, so we’ll evaluate that and sure enough by finding that one the result selector is going to give us the corresponding row and it works out beautifully. I love this, this is one of those functions that no one who ever looks this part she’s just going to be able to figure out what the heck you're doing its like magic, it just simply works. So Daniel from Quebec I want to thank you for sending in that great formula. Daniel if you don’t have an excel master pin drop me a note and we’ll ship one up to Canada for you and for everyone else thanks for stopping and I’ll see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services