Array Formulas

Resolved · Low Priority · Version 2007

Thu 28 Nov 2013, 12:17 Delegate Charlotte said...

Charlotte has attended:
Access Introduction course
Excel VBA Introduction course

Array Formulas

I am trying to get my head around array formulas and why they are necessary - does anyone have a simple explanation for them they wouldn't mind sharing?

Thanks

Fri 29 Nov 2013, 11:26 Trainer Doug said...

RE: Array Formulas

Hi Charlette

Here's an example where an array formula is used.

Imagine you have a list of sales figures with 3 columns - Salesperson, units sold and Price.

Salesperson Sold Price
Barnhill 5 2200
Belcher 4 1800
Ingle 6 2300
Jordan 3 2000
Pike 9 2150
Sancher 6 2250

The company owner wants to calculate total sales in a single cell.
Instead of entering a formula for Units * Price , copying it down and then totalling the results, you can use an array formula:

=SUM(B2:B7*C2:C7)

Then press Ctrl+Shift+Enter to make it an array formula.
This adds curly brackets around the formula.

{=SUM(B2:B7*C2:C7)}

This is an example of a singe-cell array formula. It multiplies a range by another range one row at a time. By having just one cell it can save space and avoid errors caused by multiple formulas.

I hope that helps. More about arrays is included in the Excel Advanced for Power Users course.

Regards
Doug Dunn
Best STL

Fri 6 Dec 2013: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Create your own custom list on Excel 2010!

If you know how to use the auto-fill option on Excel then why not create your own customs lists?

The auto fill feature saves you time by allowing you to enter one of the list entries into a cell and then use your mouse to automatically drag the rest of the list into the cells below, above or to either side of the initial cell. When using your mouse to perform this task you will see a thin black cross appear at the bottom right hand side of the cell. Click, hold and drag to make the list appear.

Default lists include weekdays and months. To create your own list in Excel 2010 do the following;

>File
>Options
>Advanced
>Scroll right to the bottom of the page and you will see a buttom "edit custom lists", click this button
>enter your list in the list entries
>click add

Now try it out. Good luck.
>

View all Excel hints and tips