This course consists of two parts. In Part 1, we learn to create and use data vortices for analysis and summarization, and learn to create and use decision matrices to simplify complex formulas.
In Part 2, we learn how LibreOffice Calc array functions work and how to create and use them to enhance your calculations; learn how to create and use user-defined functions and arrays in code script to gain additional functionality and perform complex operations; and also learn how to return an entire array to the spreadsheet in one statement.
Topics Covered: Part 1:
- Data Vortices
- Decision Matrices
- User-defined Functions • Arrays in spreadsheets
- Arrays in code script
Who Should Attend: CPAs, accountants, and other financial professionals wanting to learn more advanced techniques for spreadsheet data manipulation.
Software and version used in course presentation: In this course we use LibreOffice 7.x for our discussion and demonstrations. The code script language used will be LibreOffice Basic. (although users of various desktop versions currently supported by LibreOffice should benefit).
Prerequisites
Intermediate knowledge of spreadsheets such as Microsoft Excel, LibreOffice Calc, or Google Sheets. Basic knowledge of computer programming is helpful.Advance Preparation: Access to a computer with speakers or headset. Alternatively, access to a computer and a telephone (for audio reception).
Learning Objective
- Discover and describe the purpose of data vortices and decision matrices, and use them to manipulate spreadsheet data.
- Explore and describe the purpose of user-defined functions and arrays, and use them to manipulate spreadsheet data.
Last updated/reviewed: March 12, 2024
(0) Reviews
(1 rating)Lesson Questions and Answers0 Questions
INTRODUCTION AND OVERVIEW
- Course Overview 1:22
PART 1
- Data Vortices 18:52
- Decision Matrices 3:46
- Decision Matrices-Demonstration 13:20
- Leveraging Data Vortices and Decision Matrices Part 1 13:10
- Leveraging Data Vortices and Decision Matrices Part 2 4:32
- Leveraging Data Vortices and Decision Matrices Part 3 12:14
PART 2
- User Defined Function 1:42
- User Defined Function - Demonstration Part 1 14:14
- User Defined Function Demonstration - Part 2 18:00
- User Defined Function Demonstration - Part 3 22:16
- UDFs Advantages and Disadvantages 8:32
- Triggering UDFs 21:36
- Arrays 6:32
- Arrays Demonstration - Part 1 13:10
- Arrays Demonstration - Part 2 10:30
- Amortization Example 17:54
- Final Review 1:28
CONTINUOUS PLAY
SUPPORTING MATERIAL
- Slides: Advanced Spreadsheet Techniques in LibreOffice Calc PDF
- Advanced Spreadsheet Techniques in LibreOffice Calc Glossary PDF
REVIEW AND TEST
- REVIEW QUESTIONS quiz
- FINAL EXAM exam
Ask the instructor a question about this lesson