holidayenergylife.blogg.se

Failure to do so results in the totally ambiguous #VALUE! error.

OK, here are the rules: You have to hit Ctrl + Shift + Enter anytime you enter or edit these formulas. Type the formula, Hold down Ctrl and Shift while you hit enter, and you have you answer in one formula instead of 10.ĭo you see how powerful this is? Even if I had 10,000 rows of data, Excel will take this single formula, do the 10,000 multiplications and give me the result. It takes each cell in A2:A10, multiplies by the corresponding cell in B2:B10 and totals the result. Here, our CSE formula is =SUM(A2:A10*B2:B10). Sure, it is easy enough to add a column C and fill it with =A2*B2 and then total column C, but you don't have to! Our mainframe system stores Quantity and Unit Price, but not the extended price.

I copied C13 to C14:C15 and I now have averages for all of the regions. Oddly enough, Excel will let you copy and paste CSE formulas without any special keystrokes. Rather than specifying that we are looking for "East," indicate you want whatever value is in A13. In this example, we make the CSE formula more general. Remember: Hit Ctrl + Shift + Enter to enter the formula. Cool? You just created your own version of the nonexistent Excel function AverageIf. Plug this in and you will get 7452.667, the average of just the East values. The formula in cell A13 is an example of a CSE formula. If you don't write Ctrl Shift Enter down right now, you will forget it by the time something comes up again.Įxamine this example: Say you wanted to average just the values in column C where column A was in the East region. Power Excel users will have the opportunity to use these formulas about once a month. Grab a sticky note and write that down: Ctrl Shift Enter. Here is the secret: After you type a CSE formula, you have to hold down the Ctrl and the Shift keys, and then hit Enter. You are 99.1% of the way there," which you probably were. It doesn't say, "Wow - that is beautiful. Even if you get the formula right and hit the enter key, Excel gives you the totally non-user friendly "VALUE!" error. You cannot just exit the cell with a click of an arrow key. After you type in a CSE formula, you cannot just hit Enter. Second, they require a foreign, counter-intuitive handling in order to make them work. First, their real name scares everybody away. Here is why I think CSE formulas never caught on. Just about anything you can imagine can be done with one of these CSE formulas. Oh no, and they can do much more! What about if you want to do AverageIf? How about GrowthIf? AveDevIf? Even MultiplyByPiAndTakeTheSquareRootIf. Microsoft gave us SumIf and CountIf, but CSE formulas are not obsolete. Yes, my fellow Excel Guru's, there is something this powerful sitting right under our noses and we never use it.īefore there was SumIf, you could use a CSE formula to do the same thing as SumIf. CSE formulas allow you to literally replace 1000's of cells of formulas with a single formula. If you think SumIf and CountIf are cool, you will soon discover that Ctrl+Shift+Enter (CSE) formulas will run circles around SumIf and CountIf. When most people hear their real name, they think "That doesn't sound the least bit useful" and never bother to learn about them. If you know the magic three keys, you can get a single Excel array formula to replace thousands of other formulas.ĩ5% of Excel users do not know about CSE Formulas.

Use Ctrl + Shift + Enter (CSE formulas) to supercharge your formulas in Excel! Yes, it is true-there is a secret class of formulas in Excel. Use a CSE Formula (Array Formula) to Perform Super-Calculations on Data.Use a CSE Formula (Array Formula) to Perform Super-Calculations on Data