EXCEL VBA PRIMER |
CE En 170Brigham Young University Norm Jones |

INTRO - | [Table of Contents] |

EXCEL - | [Formulas] [VLOOKUP] [Validation] [Names] [Operators] [IF Function] [Goal Seek] [Charts] [Annotation] [Array Formulas] |

VBA - | [VB Editor] [Macros] [Controls] [Ranges] [MsgBox] [Variables] [Objects] [If Then] [Debug] [Loops] [Arrays] [Strings] [User Forms] [Functions] [Excel Funs] [Subs] [Events] [Standards] |

ADVANCED - | [Graphics] [E-mail] [Files] [Apps] [Google Sheets] |

In Excel terminology, an array is a group of cells in a row (horizonal array), column (vertical array), or multiple rows and columns (2D array). In this chapter we discuss a special type of Excel formula called an "array formula" that operates on arrays. Array formulas can be a powerful tool for condensing a complex set of calculations in a simple, concise formula.

To illustrate the power of array formulas, we will use the following workbook as an example:

The workbook is designed to perform calculations associated with the sale of a set of products. Each column represents a sale. The product ID is in column A, the number units sold in the sale is in column B, and the wholesale and retail prices are in columns C and D. We will calculate the total sale amount in terms of wholesale and retail in columns E and F and then perform some summary computations in the section at the bottom.

When dealing with array formulas, it is sometimes useful to use named ranges. This is not a requirement but it makes the formulas much easier to understand. For the purpose of this example, we will name the following set of ranges:

Range | Column | Name |
---|---|---|

B6:B17 | Units | units |

C6:C17 | Price|Wholesale | pw |

D6:D17 | Price|Retail | pr |

E6:E17 | Totals|Wholesale | tw |

F6:F17 | Totals|Retail | tr |

These names will be used in each of the formula examples shown below.

To begin, we will use an array formula to calcute the wholesale total in column E. Normally, we will do this by entering the following formula cell E6:

=B6*C6

and then copying the formula to the rest of the cells in column E. To do this with an array formula, we first select the the entire Total|Wholesale column (E6:E17) and then type the following in the formula bar:

=units*pw

and then rather than simply hitting the Enter key, we hit **Ctrl-Shift-Enter**. We must always use this key sequence when entering an array formula. Otherwise we get either an error message or the wrong answer, depending on what is selected. After entering the array formula, the entire column is populated as follows:

Note the resulting format of the formula:

={units*pw}

The curly braces indicate that is is an array formula. If we wish to edit the formula, we can click on the formula bar and make changes, but we must always hit **Ctrl-Shift-Enter** when we are done.

An array formula is similar to peforming vector algebra. The formula essentially multiplies the units column by the wholesale price column. The result of multiplying these two vertical arrays is a vertical array of same dimension (12 items) where each item it equal to the number of units times the price for that particular entry (row in this case). The equation applies to the entire column in the **Totals|Wholesale** part of the table. One advantage of using an array formula in a case like this is that the resulting formula is simple and intuitive.

Next, we will do the same thing for the **Total|Retail** column. We will select the column and enter the following formula in the formula bar:

=units*pr

and finish with **Ctrl-Shift-Enter**. At this point, the table is complete:

Now we will focus on the bottom section where we will perform some summary calculations based on our sales totals using functions. There are many Excel functions such as Sum() or Average() that take an array as input and return a single number as output. We will use these formulas in combination with array algebra to create some interesting results.

First of all, we calculate the wholesale total by calculating the sum of the number of units times the wholesale price. We could do that by calculating the sum of the Totats|Wholesale column, but with an array formula, we can peform the calculation without using this column. This is one of the advantages of array formulas is that we do not need any intermediate columns in order to some calculations involving multi-cell ranges. We will enter the following array formula in cell **D20**:

=SUM(units*pw)

and we get value of $4,865.00. Similarly, we can use the formula:

=SUM(units*pr)

to calculate the retail total (). Once again, after typing the formulas, we finish with **Ctrl-Shift-Enter** and the formulas are displayed with curly braces.

{=SUM(units*pw)}

{=SUM(units*pr)}

Next we will calculate the total profit as the sum of the retail totals minus the sum of the wholesale totals in cell **D22** using the following formula.

=SUM(tr-tw)

Note that we use the wholesale totals and retail totals columns in this calculation. But we could have performed the calculations directly from the units column and the prices columns as follows:

=SUM(units*pr-units*pw)

In cell **D23**, we will calculate the maximum markup (difference between retail and wholesale prices) as follows:

=MAX(pr-pw)

And the average markup as:

=AVERAGE(pr-pw)

The maximum profit (difference between retail and wholesale totals) can be computed as:

=MAX(tr-tw)

Finally, in cell **D26** we wish to compute the total high-price profit. The high-price profit is defined as the profit on items were the retail price is greater than $50. In order to calculate this correctly we need to compute a sum of only those items where the prices greater than $50. In order to do this we need to combine both the IF funciton and the SUM function as follows:

=SUM(IF(pr>50,tr-tw,0))

This example is a nice illustration of the power of array formulas. Doing this type of calculation within a normal formula would've been much more difficult than it was in this case.

At this point, our sales summary is complete:

One caveat associated with using array formulas in Excel is that you have to be very careful when using logical functions such as OR() and AND(). For some reason, they don't behave the way you would normally expect, and can lead to logical errors in your formula results. However there is a way you can reformulate your array formulas to work around this limitation. This issue and the associated workarounds are explained in the following article:

http://dailydoseofexcel.com/archives/2004/12/04/logical-operations-in-array-formulas/

The workarounds rely on the fact that the value of **True** is equal to the numerical value of **1** and a value of **False** is equal to the numerical value of **0**.

The workbook used in the examples shown above can be downloaded here:

You may wish to complete following exercises to gain practice with and reinforce the topics covered in this chapter:

Description |
Difficulty |
Start |
Solution |

Financial Arrays - Conduct a simple financial analysis on a list of items using arrays. This worksheet will help explain/show the benefits of using arrays. |
Easy | financial_arrays.xlsx | financial_arrays_key.xlsx |

New Car Expense Calculator - Calculate a range of expenses for buying a new car. |
Medium | new_car_expense_calculator.xlsx | new_car_expense_calculator_key.xlsx |

Force Analysis - Use arrays to find different statistics of force from test data. |
Hard | force_analysis.xlsx | force_analysis_key.xlsx |