Skip to content

AMAZING Power BI & Excel CALL 08 6210 8500

Pay my invoice
Contact Us

Amazing Power BI
Amazing Excel
Solver
Microsoft Fabric
Insightful Training
Resources
Blog
About Us

Dynamic Arrays + Lambda

Home/Dynamic Arrays + Lambda

Welcome to your free Advanced Excel test

1.

What feature in Excel allows formulas to spill results into multiple adjacent cells automatically?

Array Formulas

Dynamic Arrays

Data Validation

PivotTables

None

2.

Which operator is used to reference the spill range (resulting array) of a dynamic array formula in Excel?

(1) #

(2) $

(3) @

(4) :

None

3.

What happens if you try to edit or delete part of a spilled array in Excel?

The entire array is automatically recalculated.

Only the edited portion is recalculated.

Edits are not allowed in a spilled array.

The formula becomes static.

None

4.

What function is used to return the sorted unique values from a range in Excel?

SORTBY(UNIQUE(ItemList))

SORT(DISTINCT(ItemList))

SORT(UNIQUE(ItemList))

SORTBY(DISTINCT(ItemList))

None

5.

What does a spill error (#SPILL!) indicate when using dynamic arrays?

The formula returned too many results.

The formula returned too few results.

The formula caused a circular reference.

The formula's result could not fit into the adjacent cells because there's one or more entries in the way.

None

6.

Which statement correctly describes the purpose and outcome of the following dynamic array formula in Excel?

="=FILTER(FarmTable[Total (Kgs)], FarmTable[Total (Kgs)] < 100)"

It filters the values in the column Total (Kgs) from the table FarmTable where each value is less than 100, dynamically spilling the filtered results.

It returns the sum of all values in the column Total (Kgs) from the table FarmTable where each value is less than 100, and displays it in a single cell.

It calculates the average of all values in the column Total (Kgs) from the table FarmTable where each value is less than 100, and displays it in a single cell.

It sorts the values in the column Total (Kgs) from the table FarmTable in ascending order where each value is less than 100, and displays the sorted result in adjacent cells.

None

7.

How does a lambda function differ from traditional Excel functions like SUM or AVERAGE?

Lambda functions do not allow for recursion in formulas.

Lambda functions allow you to create custom functions that are not covered by built-in Excel functions.

Lambda functions are only used for text manipulation.

Lambda functions do not support conditional logic.

None

8.

How can you use a lambda function to find the average of values in the "Total (Kgs)" column which are less than 50?

=AVERAGE(IF(lambda(x, x < 50), FarmTable[Total (Kgs)] ))

=FILTER(lambda(x, x < 50), FarmTable[Total (Kgs)] )

=LAMBDA(x, AVERAGE(FILTER(x, x < 50)))( FarmTable[Total (Kgs)] )

=LAMBDA(x, AVERAGE(IF(x < 50, x)), FarmTable[Total (Kgs)]

None

9.

What is the correct syntax for defining a lambda function that squares each value in a range A1?

=LAMBDA(x, x^2)( A1:A7)

=LAMBDA(x * x, x)

=LAMBDA(x^2)(A1:A7)

=LAMBDA(x, x*2)(A1:A7)

None

10.

What does the #CALC! error indicate when encountered with a lambda function in Excel?

The formula returned too many results.

The formula returned too few results.

There was an error in the lambda function definition.

The formula's result could not fit into the adjacent cells.

None

Be sure to click the button below to check your answers! By checking your answers, you agree to receive communications from Access Analytic. You can unsubscribe at any time. Click here to review our Privacy Policy.

First Name

Email

Time's up

Related Posts:

Mastering Dynamic Measure Formatting in Power BI | Part 4:…
Mastering Dynamic Measure Formatting in Power BI |Part 3:…
Mastering Dynamic Measure Formatting in Power BI | Part 2:…
Mastering Dynamic Measure Formatting in Power BI | Part 1…
Dynamic Data Validation with Tables in Excel
Build Wordle using Dynamic Array Functions

Sam Milo2024-10-04T10:58:32+08:00

Share This Story, Choose Your Platform!

Facebook Twitter LinkedIn Email

Subscribe

* indicates required

Email *

First Name *

Last name *

Business name

Country

State

Newsletter Sign Up

First name(Required)

Email address(Required)

CAPTCHA

Δ

Categories

Arrays
Auditing
Budgeting/Forecasting
Business Intelligence
CFOs
Charts
ChatGPT
Data Handling
Excel
Excel 2007
Excel 2013
Excel 2016
Financial Modelling
Formatting
Functions
Keyboard
Microsoft Fabric
Mouse
Office 365
Pivot Tables
Power BI
Power Pivot
Power Query
Printing
Productivity
Teams
Uncategorized
VBA – Macros

Microsoft Power BI
Microsoft Excel
Solver Software
Training & Courses

Free Resources
Blog Articles
About Us
Pay My Invoice

Contact Us

Suite 143, Level 1, 580 Hay Street Perth, WA

Phone: (08) 6210 8500

Do you want to optimise your business efficiencies?

Download our 2 FREE Solver white papers on Budgeting Process and Monthly Reporting today.

First Name(Required)

Email(Required)

CAPTCHA

Δ

Copyright 2024 Access Analytic | All Rights Reserved | Privacy Policy | Engagement Terms & Conditions

Brought to you by: Huddle Up Creative

LinkedIn Twitter Facebook Rss YouTube Email

Jeff is the founding Director and Principal Business Analyst at Access Analytic Solutions. He has over 20 years’ experience in chartered accounting, business management and consulting.

Jeff holds a Masters in Applied Finance and is a Certified Information Systems Auditor. He is highly regarded and recognised around the world as an expert in the field of financial modelling and Power BI reporting. His achievements have been recognised by his peers and he is a Fellow of both the Financial Services Institute of Australasia and the Australian Institute of Management. Jeff has extensive experience in best practice management reporting, data analytics, business analysis, and financial modelling and is also an entertaining international presenter and trainer on these subjects.

Jeff is the author of a number of white papers on financial modelling on subjects such as Financial Modelling Best Practices and Financial Model Auditing. He provides training for companies throughout Australia, New Zealand, Asia, South Pacific, the Middle East, and Africa.

Yury Tokarev is a highly accomplished finance professional and international trainer with over 25 years of experience in financial modelling and advanced analytics. As a Director at Access Analytic, Yury specialises in leveraging data-driven insights to enhance strategic decision-making through sophisticated analytical tools, including Power BI and AI.

Yury’s extensive background includes developing complex financial models across various industries, automating Excel-based solutions for efficient data management, and designing Power BI reports that integrate a variety of metrics, including financial, people, and ESG indicators. His expertise uniquely positions him to address the evolving needs of senior financial professionals as they embrace digital finance and sustainability initiatives.

Prior to joining Access Analytic, Yury gained significant experience in investment banking, corporate finance, and business services and advisory. These roles provided him with a deep understanding of strategic financial planning, complex financial transactions, and advisory services, which he now leverages to deliver cutting-edge solutions in digital finance transformation and analytics.

As an engaging and interactive trainer, Yury has delivered numerous workshops and training sessions globally, helping professionals elevate their understanding of financial modelling, Power BI, and AI analytics. His teaching approach combines practical insights with real-world applications, empowering participants to effectively apply advanced analytics and AI in their strategic roles.

Wyn Hopkins
B.Sc. (Hons) Accounting & Financial Management, Microsoft MVP, Excel Expert (MS), Author

Wyn has been awarded MVP status by Microsoft every year since 2017 and is the author of the book “Power BI for the Excel Analyst”. He is known worldwide for sharing his knowledge via the Access Analytic YouTube channel with over 5 million views and 90,000+ subscribers.

He gained his Chartered Accountant qualification at PwC in 2000 and has extensive Business Analyst experience with HBOS and Barclays in the UK plus multinational clients in Perth.

Wyn is skilled in Excel and Power BI development, Power Query automation, management reporting and is an experienced Excel and Power Bl trainer.

Ready to kickstart your career?

To express your interest in our Internship Program, please fill out the form and attach your cover letter and resume, and we’ll get back to you shortly!

First Name(Required)

Last Name(Required)

Email address(Required)

Phone number

Why do you want to intern with us?(Required)

Resume(Required)

Drop files here or

Max. file size: 300 MB.

Cover letter

Drop files here or

Max. file size: 300 MB.

Consent(Required)

I confirm that I am a student currently enrolled at a university in Perth and residing in Perth.(Required)

Δ

First Name(Required)

Email(Required)

So we can contact you with updates - you can opt out any time.

Δ

First Name(Required)

Email(Required)

So we can contact you with updates - you can opt out any time.

Δ

First Name(Required)

Last Name(Required)

Email Address(Required)

Topic

Phone Number

Business Name

Preferred method of contact

Email

Phone

Message

CAPTCHA

Δ

First Name(Required)

Last Name(Required)

Email Address(Required)

Phone Number

Business Name

What is your enquiry in regards to?

Message

Consent

I agree to receive communications from Access Analytic. You can unsubscribe at any time. Click here to review our Privacy Policy.

CAPTCHA

Δ