CS 106 Computing Fundamentals II Chapter 84 “ Array Formulae ”

1. Herbert G. Mayer, PSU CS status 6/14/2013 Initial content copied verbatim from CS 106 material developed by CS professors: Cynthia Brown & Robert Martin CS 106Computing Fundamentals IIChapter 84“Array Formulae”

2. Syllabus • Writing Excel User Functions • Mac vs. Windows • C • C • C • C • c

3. An array formula is… • An array formula is a formula that can perform multiple calculations on one or more elements of an array; it can return multiple cells instead of just one cell • The multiple cells are in a rectangular shape group which could be in one row or column or in multiple rows and columns. • We will call this group of cells an “array” in this context. It is not the same as a VBA array, but it is a similar concept.

4. Example: Transpose • We’ll illustrate the idea by creating an array formula that transposes an array • There is a copy option that does the same thing, but it creates a copy that is not linked to the original, so a change in the original does not create a change in the copy • This example is from “Excel, the Missing Manual” by Matthew McDonald

5. Example Data This data has 15 rows and 5 columns; the values repeat down each column and increase from row to row.

6. Create the Array Formula The formula is going in cells A18:E15. I typed =TRANPOSE( and selected the array of cells A1:E15. To save it as an array formula I now must type ) and then Control-Shift-Enter rather than just Enter. (Use Command-Return on a Mac)

7. The Result You can see that the rows and columns have been transposed.

8. I added a regular copy with transpose option These two transposed copies look the same, but they’re not

9. I changed the original “array”… The copy created with the array formula changed to match the new data; the simple transposed copy did not

10. Curly brackets signify an array formula Excel adds the curly brackets when you type Control-Shift-Enter (Command-Return on a Mac); they show the cell value was created using an array formula

11. Changing the Formula • If you want to change the formula, you need to select ALL the cells in the source array, change the formula, and then type Control-Shift-Enter (Command-Return on a Mac) to create the new formula • Besides TRANSPOSE, some other functions that require array formulas are FREQUENCY and TREND

12. Another Example • This is taken from the article at support.microsoft.com on when to use an array formula to do a SUM with multiple IF criteria • We discussed IF for worksheets early in the term; see Week 3 for details • This formula also uses + as a Boolean operator: it is the same as OR

13. The Data We have a company with 3 types of departments, A,B, and C Our spreadsheet has rows with department type and number of employees We want to sum the number of employees in departments of type A or B (You could also use SUMIFS, as we did in our example in Week 3)

14. First Example Here we entered the following array formula in Cell D1: (curly braces added by Excel) =SUM(IF((A2:A9="A")+(A2:A9="B"),B2:B9,0)) This says that if the entry in column A is “A” or “B”, add the corresponding number in column B to the sum, else add 0. The sum of numbers with departments A or B is 16

15. Second Example Note the curly braces inside this second formula! You type these explicitly; the outer ones come from using the special return combination. The formula gives the same result as the first one: =SUM(IF(A2:A9={“A”,”B”},B2:B9,0)) So in this case Excel matches any element in the inner array {“A”, “B”}

16. Bottom Line • There are cases where it makes sense to use these kinds of formulas • Often, though, you can write a VBA program to do the same job and the program will be easier to understand and modify, and less error-prone, than the formula • If you like these there is more information at http://office.microsoft.com/en-us/excel-help