Understanding Lambdas and Math Functions in Excel

Name: Understanding Lambdas and Math Functions in Excel
Rating: 4.6 (11 reviews)

Explore Excel’s math functions and LAMBDA to build flexible, advanced formulas with ease.

Highest Rated

Created byCreateMyExcel with Ben Scholl

Last updated 7/2025

English

English [Auto],

What you'll learn

Master Excel’s core math functions like SUM, ROUND, and INT for precise and flexible calculations.
Use MROUND, CEILING.MATH, and FLOOR.MATH to round values to the nearest multiple with control.
Apply EVEN, ODD, TRUNC, and MOD for numerical logic and advanced number manipulation.
Learn to prevent rounding errors and apply rounding to real-world billing and pricing scenarios.
Automate totals with SUMIF and SUMIFS to conditionally sum data with ease.
Use random number functions like RAND, RANDBETWEEN, and RANDARRAY for data modeling.
Create dynamic sequences and patterns using SEQUENCE, LET, and other formula-based tools.
Customize Excel’s behavior using LAMBDA functions and process arrays with MAP, REDUCE, BYROW, and BYCOL.
Determine leap years and apply calendar-based logic in formulas.
Use math functions creatively to generate values, control formatting, and simulate data structures.

Course content

1 section • 32 lectures • 1h 20m total length

Working with Math Functions2:32
Explore Excel math functions for business analysis, including rounding, sum functions, mod, rand, and practical case studies, plus advanced let and lambda features in Microsoft 365.
Excel's Math Functions1:29
Explore Excel's vast math function portfolio, focusing on the bolded functions essential for business analysis and how the formula ribbon houses these tools.
ROUND Function & Understanding Excel's Rounding Functions4:00
Understand the round function and Excel's rounding functions, including round up, round down, ceiling, and floor, and how to round to digits or negative positions.
MROUND Function2:15
Learn how the MROUND function rounds a number to the nearest multiple in Excel, with examples using multiples of 2 and 5, including negative values.
The ROUNDDOWN and ROUNDUP Functions2:33
Explore round down and round up in Excel, showing rounding toward zero and away from zero. See how the digits argument controls decimal places and rounding behavior.
The CEILING.MATH and FLOOR.MATH Functions3:01
Explore how Excel's ceiling.math and floor.math functions round numbers using a significance and mode, and compare positive versus negative behavior with practical examples.
Real Life Example; Calculating Easter Date3:02
Explore how floor math and the date function in Excel compute Easter for given years, using constants and the ecclesiastical full moon method.
The EVEN and ODD functions2:10
Use the even and odd functions in Excel to round numbers away from zero to the nearest even or odd value, with clear syntax and examples.
The INT and TRUNC Functions1:54
Explore the int and trunc functions in Excel to extract the integer portion of numbers. Observe how int rounds away from zero, while trunc removes decimals and controls decimal places.
Using Rounding to Prevent Calculation Errors & Pricing Examples4:06
Explore how rounding functions in Excel prevent calculation errors and apply them to pricing, ending prices at $0.95 or $0.50 with int and simple if logic.
Case Study; Rounding Billable Time2:29
Explore a case study that applies mround and ceiling math to round billable time to 15-minute intervals using the time and time value functions in Excel.
SUM Function & Summing Values1:00
Learn how the sum function in Excel adds values, using ranges like A2 to A13 and up to 255 arguments, to quickly compute column totals.
SUMIF Function2:47
Explore the sumif function, defining a range, criteria, and optional sum range, with examples like greater than zero, less than zero, equal to ten, and regional sums such as East.
SUMIFS Function1:55
Master the sumifs function in Excel to sum values by multiple criteria, such as region and product type, with clear syntax for sum range and criteria ranges.
Calculating Cumulative Totals2:16
Learn to compute cumulative totals in excel using a sum function with a fixed first reference and a relative second reference, enabling tracking of interest, principal, and amortization over time.
The MOD Function1:21
Analyze the mod function in Excel by understanding how it returns the remainder of a division, with examples like 13 mod 2 = 1 and 5 mod 3 = 2.
Summing Every nth Row2:23
Learn to sum every nth row in Excel using the mod function with sum and if, by checking the row number remainder.
Determining Whether a Year is a Leap Year3:37
Use the mod function in Excel to determine leap years by checking divisibility by four, 100, and 400; implement a 1 or 0 result for leap vs non-leap years.
Creating Ledger Shading2:46
Apply ledger shading in Excel by using conditional formatting with a mod of the row number to shade alternating rows, and choose suitable light or dark styles.
RAND Function & Generating Random Numbers2:10
Explore how the rand function in Excel generates numbers between 0 and 1, then scales them to a chosen range by multiplying and adding the low value.
Generating Random n-digit Numbers2:25
Generate random n-digit numbers in Excel by nesting rand inside int, using powers of ten to set the length, demonstrated with an eight-digit example.
Generating a Random Letter2:29
Generate random uppercase letters in excel by nesting the rand, int, and char functions, using character codes starting at 65 for a 26-letter range.
Sorting Values Randomly1:43
Use a helper column with the rand function to randomly sort values, disable automatic calculation while sorting, then delete the helper column to obtain a randomized list.
The RANDBETWEEN Function1:11
Randbetween function generates integer random numbers within a specified bottom and top range. Explore its syntax and compare it with the rand function to see the key differences.
The RANDARRAY Function1:18
Generate random numbers in Excel as an array with the rand array function, specifying five rows, four columns, integer output, and a 1 to 20 range.
Creating Increasing Random Numbers with The SEQUENCE function1:16
Explore creating increasing random numbers in Excel by combining the sequence function with rand, controlling rows, columns, start value, and step, and experimenting with random inputs.
LET Function & Programming with Excel's Formula Language7:35
Learn how the let function creates and reuses variables in Excel formulas to simplify complex calculations, demonstrated with computing the nth weekday occurrence.
Using LAMBDA to Create Custom Functions2:26
Use lambda to create custom Excel functions without relying on Visual Basic, demonstrated with a hypotenuse length function based on the Pythagorean theorem, defined with height and base inputs.
Using a LAMBDA to Process an Array, REDUCE4:22
Explore using a lambda with reduce to process an array of sales data in Excel. Learn to count items above 300,000 and compute 5% of those with an accumulator.
Using a LAMBDA to Process an Array, MAP3:27
Use map with a lambda to process a 2020–2024 sales array, creating a 2023 target at 6% over 2024 sales and a bonus decision by last year vs current year.
Using a LAMBDA to Process an Array, BYCOL1:36
Use a lambda with the by call to process an array and compute the max value for each column in a sales range in Excel.
Using a LAMBDA to Process an Array, BYROW1:21
Explore using a lambda with byrow to process an array in Excel, computing each row's maximum value across sales data from 2020 to 2024.

Requirements

Microsoft Excel (Office 2021 or Microsoft 365) installed.
Basic knowledge of Excel (e.g., entering data, simple calculations).
Access to a computer with a stable internet connection.
No prior advanced Excel skills required; suitable for beginners.

Description

This course dives deep into Excel’s powerful math functions, equipping students with the knowledge to perform a wide variety of numerical operations and data manipulations. Whether you're rounding numbers for cleaner financial reports or generating randomized values for simulations, this section introduces a comprehensive suite of tools designed to improve precision and efficiency.

Students will begin by exploring Excel’s core rounding functions—such as ROUND, ROUNDUP, ROUNDDOWN, MROUND, CEILING.MATH, and FLOOR.MATH—and learn how to apply them in real-world contexts like pricing, billing, and time tracking. Through hands-on exercises, learners will also master the INT and TRUNC functions for truncating values, and EVEN/ODD to work with number patterns.

The course progresses to advanced topics like conditional summing with SUMIF and SUMIFS, calculating cumulative totals, and applying logic using MOD. It also covers creating alternating row effects for ledgers and how to determine leap years using formulas.

Finally, students will unlock dynamic capabilities with Excel’s random functions, including RAND, RANDBETWEEN, and RANDARRAY. The chapter ends by introducing Excel’s formula programming tools—LET and LAMBDA—enabling the creation of custom functions and advanced data processing with BYROW, BYCOL, MAP, and REDUCE.

By the end, students will be confident in applying math functions to automate, simplify, and enhance their Excel workbooks.

Who this course is for:

For Excel users who want to master rounding with ROUND, MROUND, ROUNDDOWN, and ROUNDUP.
Ideal for professionals needing to use CEILING.MATH and FLOOR.MATH for pricing and billing.
Perfect for users who want to sum values with SUM, SUMIF, and SUMIFS.
Great for anyone managing totals, cumulative values, and nth-row summaries.
For those learning to generate and control random numbers with RAND, RANDBETWEEN, and RANDARRAY.
For users who want to explore INT, TRUNC, EVEN, and ODD for numeric cleanup.
Helpful to those needing to build logic for leap years and special dates.
For advanced users interested in creating custom functions with LAMBDA and LET.
A good fit for people building timesheets and billing calculators.
For learners applying math functions to dynamic reports and ledgers. Ask ChatGPT