Hey, welcome back to the Mr. Excel Net Cast. I'm Bill Jelen. Amazing, we start out with massive amounts of data as to how are we going to analyze this. Well, let’s fire up the pivot table and see if we can solve this problem.
Hey alright, welcome back to the Mr. Excel Net Cast, I'm Bill Jelen. The spring seminar season is just about it, where we have one more big seminar out in dead where that’s the National IMA conference. So for the most part I'm not going to be getting excel questions every week as I travel around during the seminars. If you’re sending you're question to the pod cast before or you have a question that you like to you to send to in the pod cast. Please follow me and send it to me, this is a great time. I need some questions through the pod cast. Either drop an email with bill@mr.excel.com or if you want to be on the pod cast, I’ll leave your question as a voicemail. 866-581-0221 you know, introduce yourself, hey, this is Mary from Chicago and lodge in your question will be able to help you out.
Another example here of formula recount time, so we talked about this on Monday in 1,011 and we redesign a better formula. This time I want to talk about something called helper cells. Helper cells, now, I have 11,000 rose here, the 11,000 rose are each calculating revenue as a percentage of the total revenue for that product. So, I thought this formula out here it’s pretty cool to be honest. Take the revenue in this row divided by the sum. If look through all the rose in Column B, see if it is equal x, y, z, if it is out of the corresponding cell in Column B that excel sum function there but, think about the sum F function. This means in every single row its going to go to evaluate all the dozen 123 other cells. So, we just have a huge equal 11, 823 raise to second power and 139 million references that we have to calculate in order to calculate this.
I'm going to use the same macro that I use before. This macro right now is out doing 139 million various references and we’ll see how long it takes. It been running somewhere in the 15 to 20 seconds range. We were for that. Let me talk about the -- with these entire lines. So that means that one per x y z on row two, its going to come up again, again, again, calculate that the same number over and over. Alright, so check that out. 31 seconds to calculate that range. I'm going to go on to another worksheet where I change things a little bit. Over here, I put the really time intensive calculation. These three cells, three cells out here that calculate the total for x y z, the total for d e f, and the total for a b c. Put those out to the side.
Those are called helper cells so rather than having to calculate 11,000 times the sum F. It only calculates at once and then here in the actual formula, where doing E2 divided by vlookup. Now the vlookup is going to be fast. You notice that I'm using not the comma false version but the exact match. The lookup range is sort. There’s only three items in the lookup range so it’s going it run lightning fast.
Now, remember the very first time the previous example to calculate 31 seconds and now we’re going to calculate this one. Check that out, less than -- well, 0.01 almost 0.02 seconds when if 50th of a second. Dramatically faster by taking those three values in putting them out. In a helper cell, even though we have to do 11,000 vlookups. The vlookups are lightning fast because of the small table. So there's an example where we went from 31 seconds to 0. - lets call the 02 seconds, just a dramatic change in count speed by thinking about what it is in this calculation that can be move out to a secondary cell and then refer that to the secondary cells that with whole thing does not have to be calculated again, again, and again.
Great example of recount speed. If you like this one, make sure to watch episode 1,011 back on Monday and check out the white paper. I mention in that episode because it goes through many more examples like this. Well, I want to thank you for stopping by. Again, if you have any questions feel free to drop me a note and we’ll get you on a new future pod cast. We’ll see next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services