- slide 1 of 3
Strategy: The typical strategy is to add a new column with price times quantity and add that up. However, this is not necessary if you use a type of super formula that I call the CSE formula.
If you’ve been using Excel for a while, you will think that this looks like it will not work. In fact, if you enter the formula, you will get a #VALUE! error, confirming that it does not work, as shown in Fig. 588.
However, if you know the secret you can still make the formula work. Edit the formula by hitting F2. Instead of using Enter to finish the formula, hold down Ctrl+Shift and then hit Enter.
As shown in Fig. 589, miraculously, Excel does 563 multiplications and then adds them up to give you a result.
In the formula bar above, you will note that there are curly braces around the formula. You do not enter these braces. Excel adds them when you use Ctrl+Shift+Enter.
Additional Details: Most people will only have an occasion to use this formula once a month. I found that I could never remember the keystroke combination, so I renamed these formulas “CSE” to help me to remember Ctrl+Shift+Enter. If you need to search Microsoft Help on the subject, check under the name “Array Formulas”.
Summary: One single CSE formula can replace hundreds or even thousands of intermediate formulas.
Commands Discussed: Control+Shift+Enter
- slide 3 of 3
References and Additional Resources
If you're looking for more tips and tutorials, check out 91 Tips for Calculating With Microsoft Excel. This collection of easy-to-follow guides shows how to customize charts and graphs, different ways to make complex spreadsheets easier to update, and even how to play games like Craps in Excel.
Microsoft Excel Official Site, http://office.microsoft.com/en-us/excel/
Bill Jelen, Microsoft Excel 2010 In Depth, Available from Amazon.com.