Use array formulas to perform unusual calculations that often involve tabulating data across parallel sets of data either on column or in rows. Complete the entry of these kinds of formulas by pressing Ctrl+Shift+Enter. Array formulas, when viewed in the Formula bar, are delineated by curly braces - { and {.
- [Voiceover] In Excel, there's a special kind of formula,…called an array formula.…And there's also special functions called array functions.…We're looking at a workbook called 11-Array…and the very first worksheet is called Arrays.…Many times, array formulas and functions deal with data…across multiple cells.…It's hard to define one of these exactly…until we see a few examples.…Now, in this list here…in columns B and C,…we've got unit sold, unit price,…and in column D, a simple formula…multiplying the two.…
Doing this…all the way down the column here,…then a total on the bottom.…Now, there could be times working with data like this…or larger amounts where we don't need the interim totals.…We just want the total price.…Now, there is a function that will let us handle this…it's not an array function.…I'm gonna alert you to it,…it's called sum product.…And in the example here,…we simple are going to be highlighting these cells…right here,…comma, and these.…And because of what the function does,…it's going to do the multiplication…
Author
Released
5/26/2016- Understanding how the hierarchy of operators affects formula results
- Knowing when to use absolute vs. relative references
- Using Formula Builder for unfamiliar functions
- Displaying a worksheet's formulas and highlighting formula cells
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- Creating and expanding nested IF functions; using AND, OR, and NOT with IF
- Looking up information with VLOOKUP, HLOOKUP, MATCH, and INDEX
- Analyzing data with the statistical functions: MEDIAN, MODE, etc.
- Using the power functions: COUNTIF, SUMIF, COUNTIFS, AVERAGEIFS, and more
- Calculating financial data such as payments
- Performing basic math
- Calculating dates and times
- Editing text with functions
- Using array formulas and functions
- Referencing data in other cells and files
- Extracting information from Excel worksheets
Skill Level Intermediate
Duration
Views
Related Courses
-
Excel for Mac 2016: Charts in Depth
with Dennis Taylor4h 22m Intermediate -
Excel for Mac 2016: Tips and Tricks
with Dennis Taylor5h 27m Intermediate -
Excel for Mac 2016: Pivot Tables in Depth
with Curt Frye2h 3m Intermediate
-
Introduction
-
Welcome46s
-
Exercise files25s
-
-
1. Formula and Function Tools
-
2. Formula and Function Tips and Shortcuts
-
3. IF and Related Functions
-
4. Lookup and Reference Functions
-
Use MATCH and INDEX together4m 40s
-
5. Statistical Functions
-
6. Power Functions
-
7. Selected Financial Functions
-
8. Math Functions
-
9. Date and Time Functions
-
10. Text Functions
-
11. Array Formulas and Functions
-
12. Reference Functions
-
13. Information Functions
-
Conclusion
-
Next steps25s
-
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Extend formula capabilities with arrays