The second tab of this workbook is the Array Formulas tab. I will do Ctrl+Page Down to move to the Array Formulas tab. This dataset is slightly different. It has region, product, date, quantity, unit price and unit cost. I remember when I learned about array formulas. Back at my day job, I had a vice president of marketing who sat two doors down from me and that is a dangerous combination. You have a vice president using Excel and a person from marketing using Excel and he came down to me and he say, “Hey, look! I have 5000 rows of this data. I have quantity and unit price, I need one formula that will multiply all those and sum them up.
Well, you know, being versed in Excel, I was ready to go over to column G, enter a new formula in G2 that will multiply quantity times unit price. Copy that all the way down and add the sum and he stopped me. He said, “No, no, no, no, no. You are adding 5000 formulas. I want one single formula.” You know, you have to understand at this point, I am already MrExcel. I have been running MrExcel.com for a while. I look at the vice president and said, “You know, there is no really way to do that.”
Well, for the other direction two cubes down kind of like a golfer popping his head off over the cue ball, fellow says, “Hey, there is a way to do that.” And he proceeded to write this formula. Take a look at this formula. I am going to choose cell E48 and hit F2 to edit. He wrote a formula that said we are going to take the sum of the rectangular range D2 to D46 and multiply that range by the rectangular range E2 to E46. Now, if you have been using Excel for anytime at all, you know that that formula is completely invalid. I looked to this fellow and said, “You are crazy. There is no way that it will work.” and I reached across the keyboard and I hit the Enter key and sure enough the value error. It is not a valid formula.
Aha! But here is the secret. Excel has a whole separate kind of formula called array formulas. These are secret super formulas that actually can do the 45 multiplications and total them up. If, as in this case, we have 5000 rows of data, it could do the 5000 multiplications and add them up. Now that we are in Excel 2007 it could the 1.1 million calculations and add them up, but you have to know the secret. Let me go back up and select this original formula. I am going to hit F2 to put it back in edit mode. So we are taking the sum of this rectangular range times this rectangular range, the secret in order to have Excel understand that this is super formula is to hold down three keys. You have to hold the Ctrl key, the Shift key and then finally, hit the Enter key.
Ctrl, Shift, Enter tells Excel that we have created a super formula; an array formula. And you will notice that in the formula bar, Excel has put curly braces around the formula to tell us that it is an array formula. These are incredibly powerful formulas and most people have no clue that they are there. Now, the one gotcha that I have to warn you about is that this formula is very memory intensive. It is great here, where I have one or two or ten or twelve of them. I once tried to build a spreadsheet where I had 5000 of these formulas each one taking 50,000 rows of data. If I had not turned the computer off, it will still be calculating to this day.
So, they are great in moderation. The thing you have to be careful of is you do not want to have hundreds of thousands of this. They will take forever to calculate. Find some other way to solve the problem. Excel calls these array formulas. Array formulas bring back horrible memories of Algebra or some Calculus things that I just do not want to think of so I renamed these formulas as CSE formulas. It is a CSE formula. Ctrl, Shift, Enter formula. The name of the formula, CSE, helps me remember that I have to hold down Ctrl and Shift and Enter; very powerful kind of formula.
Well, let us go down and take a look at the formula in cell B51. This is a special kind of array or CSE formula that will do the same thing as the SUMIF. Now, here we have a special situation where we do not
Transcription by:
Scribe4you Transcription Services