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 this is the strangest email I've ever got and this is from Sarah, Sarah is on the Cattle farm in England. And Sarah is on the podcast before I answer one of her questions and as a thank you they’ve now named one of the new cattle after me. Is that the weirdest thank you you’ve ever had, somewhere right now in England there is a head of cattle that is name after me. So Sarah send in an insanely hard question and this comes directly from excel goowers gone loud, I'm going to try and explain this.
These are vehicles here and this is the mileage when the vehicle was fueled. And Sarah says when they enter this vehicle in mileage she would like to go grab the previous mileage so that involves finding the last entry for that vehicle and bringing that mileage down to here. Right now, they're doing that completely manually and I have a formula that will do this but I need to explain what’s going on even if it’s just for my own personal trained to understand how it works. First of all I want to talk about match, now match is kind of like the look up but not the view look of a match that we usually do with comma and false. This is the exact match, the exact match says “Hey, the date over is sorted and we want to find a match for 952” and 952 says that that is in the 15 row of the range right that works that great.
Now I want to show you cool trick here, what if we ask for a number that’s it way larger than any of the numbers such as there have been a bunch of nine that always is going to return the last cell in the range. Cool trick isn’t? Now I'm going to make this a little bit more interesting I'm going to replace some of this numbers with NA. So equal NA and strange enough the match ignores those NA and tells me the last real number in the range got it?
All right, now your saying “Well how is this going to solve our problem?” I'm going to come back to our spread sheet and I have built an interesting format it say “Were going to use the match of a really a large number 999999 and then on offline going to build a new ray. Check to see if everything form C$1 down to the row right above me C21 and you notice there is not dollar sign before the 21 is equal to C22. If it is putting the number one if its not put in the number NA and so let's conceptualized what were going to get here, were going to get and array of either ones or NA. There will be a one here, NA, NA, NA, NA, a one, NA, NA, NA, NA, and so on. And then I'm using that match trick look for a really large number within that array and the array is going to be either ones or NA its going to tell me the position of the last matching number.
So we have to use CTRL-shift there because it’s a array format that tells me that I'm in row 16 is the last C123 a, b, c. All right, so now that I'm going to come back here and I will use the index function and say the index of all my fuel values in C and looks like at column G. So G1, G$1 to G22 and which row number I went, I want the result of that match but put the closing parenthesis control shift enter and 28331 now we’ll copy this up and just see how it matches what Sarah had manually done and as I go through it, it looks like “Hey, look at that its working all right” just in an insanely hard formula.
Now this NA thing that this particular vehicle the 83HDL has not been fueled in these data sets so she’s just going to have to deal with that herself. So there you have it and insanely hard formula that’s three out of excel gurus gone wild the book of some the best amazing formulas that came out of the Mr. Excel message board. I want to thank Sarah for sending that question in you know last time I answered a question I had a cattle named after me, I really have no idea what the follow up will be but its very cool I want a picture I don’t know why? And here’s this—never mind it’s completely insane. All right well thank you for stopping by and see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services