Learn advanced Excel techniques for automating rainfall reporting with array formulas in this 37-minute tutorial. Master date and time formatting, create unique date lists using FREQUENCY and AGGREGATE functions, and calculate rain totals with SUMIFS and IF functions. Explore efficient formula construction and test the reporting system with new data. Download provided Excel files to follow along and practice these powerful data analysis methods.
Overview
Syllabus
) Introduction and description of reporting requirements.
) Date and Time Number Formatting and Time Formula for Time Equivalent for Date Start Time..
) Count Unique Date Array Formula. See the functions FREQUENCY, SUM and INT. See two versions of this formula. One version converts positive numbers with the Logical Test Greater than Zero and a Double Negative. The other uses the SIGN Function..
) Array Formula to Extract a List of Unique Dates that assumes that we may not have a complete list of sequential dates, and there is a variable Start Time. See the functions AGGREGATE, ROW, ROWS, IF, SIGN, FREQUENCY, SUM and INT..
) Formula to add rain totals using SUMIFS & IF Functions and criteria based on the start date-time..
) Array Formula to Extract a List of Unique Dates that assumes we HAVE a complete list of sequential dates. See the functions, AGGREGATE, IF, ROWS and INT..
) Re-organize Order of Logical Tests in IF Function to create a more efficient formula..
) Add new data and test formulas and reporting system..
) Summary.
Taught by
ExcelIsFun
Related Courses
-
Comprehensive Excel Dynamic Array Formula Lesson - The Power of Array Formulas
-
Average of Customer Total Sales by Product - Formulas, DAX or Array Formula?
-
Ctrl - Shift - Enter - Excel Array Formulas - Review Formula Basics
-
Excel Magic Trick - FREQUENCY Array Function
-
Aging Accounts Receivable Reports on Multiple Sheets With Array Formula
-
Excel Statistical Analysis - Comprehensive Worksheet Formula & Model Building
