Chapter 7 continued ...
Perform the operation again to copy the formula (without the = sign) from Cell E1 to Cell F1, and paste over the reference E1. The result is a single formula: =SUM(SUM(A:A)+SUM(B:B)).
Analysis ToolPak, Analysis ToolPak-VBA
You should also install the Analysis ToolPak-VBA add-in. This add-in includes VBA functions that are useful in developing applications in the VBA programming language.
Install both add-ins before beginning to work in Excel.
Installing the add-ins
1. From the Tools menu, select Add-ins.
2. Select both add-ins, Analysis ToolPak and Analysis ToolPak-VBA.
3. Click OK.
Now check to see if the functions included in Analysis ToolPak have been installed. Press Shift+F3 (Paste Function) and select All. In Select Function, notice the functions whose names are printed in lower-case letters. You have installed these functions by installing the Analysis ToolPak.
Use an array formula to create a formula that performs complex calculations.
For example: there are names defined for three ranges:
Range A4:A12 Part_Number
Range B4:B12 Quantity
Range C4:C12 Price
In Cell D16 is the array formula ={(SUM(Quantity*Price)}.
The formula returns the result of the Quantity range times the Price range.
Cell D19 contains the formula ={SUM(IF(Part_Number=C19,Price*Quantity,0)}.
The formula returns the result of the Quantity times the Price for part number A663.
Use the following technique to enter an array formula:
1. In any cell, enter the formula =SUM(Price*Quantity). Define Names before beginning to enter the formula.
2. Press these three keys simultaneously: Ctrl+Shift+Enter.
3. The array formula is created when these three keys are pressed simultaneously. In order to identify the array formula, brackets ({}) are inserted around the formula.
4. If you forget to hold down Ctrl+Shift while pressing Enter, the formula will evaluate to the VALUE! ERROR. If this happens, then press F2 (Edit Cell), or use the mouse to select any character in the formula bar.
The technical side of array formulas
An array saves calculations in the temporary memory, which are used later in the calculation of the total. The ability to save results in the temporary memory allows you to perform complex calculations such as the one displayed.
Using array formulas to create links with a change of direction
Select a range of cells in the sheet, and press Ctrl+C. Select the last
cell in the sheet, right-click, and select Paste Special.
In the Paste Special dialog box, there is a Paste link option. This option allows you to paste link formulas into cells. The Paste Special dialog box also has a Transpose option, which pastes the data in the opposite direction (that is, horizontally to vertically, or vice versa).
Problem
You cannot select the Transpose option together with the Paste link option. In other words, you cannot create a link while changing the direction of the paste operation.
Solution
Use the TRANSPOSE function together with the array formula technique to create links that change direction.
First, measure the number of cells in the selected columns and rows. When selecting the range, notice the Name box.
Select range A2:B6, which has the name Range defined for it. The size of the range is 4Rx2C – that is, four columns by two rows.
Starting with Cell A8, select a range whose size is 2Rx4C – that is, the same size in the opposite direction.
1. Enter the formula =TRANSPOSE, and press Ctrl+A.
2. Press F3, paste the name RANGE, and press Ctrl+Shift+Enter.