Dianne Auld's Excel Tips: Featuring Compensation and Benefits Formulas Third Edition by Power Query & Dynamic Arrays

Author:Power Query & Dynamic Arrays
Language: eng
Format: epub
Publisher: WorldatWork
Published: 2020-09-15T00:00:00+00:00

Screenshot 86.1

■When entering a Multi-Cell Array formula, you must select the range of cells that will hold the results before entering the formula.

■The arrays in the formula need to be the same shape and size.

When using Array formulas in Office 365, the rules are different from the legacy versions.

■You do not need to press Ctrl+Shift+Enter when entering any array formula. Simply press Enter. Excel will not surround the formula with braces { }. It will look like a normal formula.

■Parts of a Multi-Cell Array formula can be edited, and rows or columns can be inserted or deleted.

■When entering a Multi-Cell Array formula, click on the first cell where the formula is to go. The formula will automatically spill down or across, provided there is no data in the spill cell range.

■The arrays in the formula need to be the same shape and size.

In the Array Formula Tips 86 to 90, all instructions and screenshots are for pre-Office 365 versions. If you are using Office 365, follow the same instructions, but with two changes. A) Do not press Ctrl+Shift+Enter when entering or editing the formula. B) For multi-cell array formulas, only select the first cell where the formula is to go; it will automatically spill down or across. For more information on Office 365 dynamic arrays, see Tips 133 to 135.

With these rules in mind, let’s practice writing Multi-Cell and Single-Cell Array formulas for the comparative ratio example mentioned previously. Let’s work with the data set in Screenshot 86.1, which shows a group of employees’ salaries and pay scale midpoints for their grade

With regular formulas, the comparative ratio formula would be written in Cell G2 and then copied down the table. However, with Array formulas, the comparative ratio formulas for all employees can be written at one time. To do this, first select Cells G2 to G21. Remember: You must select the range of cells where the Array formula is to be placed before writing the Multi-Cell Array formula.

Next, in the formula bar, type the formula =E2:E21/F2:F21*100. Note: You can select Cells E2 to E21 and Cells F2 to F21 rather than typing them. Press Ctrl+Shift+Enter. Excel will then put braces { } around the formula, and the formula will be entered simultaneously into all the cells selected, calculating the comparative ratio for each employee. See Screenshot 86.2.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.