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

Advanced Excel

Home/Advanced Excel

Welcome to your free Advanced Excel test

1.

Each green cell contains exactly the same formula (ie =CellAbove ). How come each one displays a different result?

This is what the CellAbove function does.

CellAbove is a relative range reference.

CellAbove is an external indirect function.

This is only possible in Excel 95 and below. Newer versions do not support this functionality.

None

2.

The following formula should show "Fair", "Good" or "Excellent" depending on the value of A1 but there is an error in the logic. What value produces an incorrect result?

A value of 0 will display "Good"

A value of 500 will display "Excellent"

A value of 1500 will display "Fair"

A value of -500 will display "Good"

None

3.

The range name "EmployeeLookupTbl" has been correctly defined to refer to the shaded area. What needs to be changed so the cells B5 and B6 show Pierre Francois' information rather than Graeme Smith's.

The table should be sorted by employee ID so the values are in numerical order.

The VLOOKUP function should include FALSE as the 4th argument e.g. =VLOOKUP(B4,EmployeeLookupTbl,2,FALSE) in B5

Either of the above will fix the problem.

The employee ID column of numbers should be formatted in yellow.

None

4.

I have a spreadsheet with many rows and columns of data as shown below. Yet when I print the entire sheet, only the highlighted range is printed. Why?

There is probably a problem with my printer driver.

I may have incorrect page margins.

A1:E8 has been set as the print area for this sheet.

Because I've selected this area, that is the only part that will print.

None

5.

What's wrong with the function in B7 below?

The function itself is correct but it will be confusing later on because unless you look into function, it looks like the function uses the numbers in the cells above it ... but it doesn't.

Everything is fine - this is a trick question.

The function gives an incorrect result because it defaults to interest charged in advance.

The 100,000 should also be multiplied by 12.

None

6.

Which Excel formula correctly retrieves the age of "Emily" from the data set?

(1) =INDEX(A2:B5, MATCH(B7, A2:A5, 0), 2)

(2) =INDEX(A2:B5, MATCH(B7, A2:A5, 0), 3)

(3) =INDEX(A2:B5, MATCH(B7, B2:B5, 0), 2)

(4) =INDEX(A2:B5, MATCH(B7, A2:A5, 2), 0)

None

7.

Cell B10 is being edited. B8 contains the formula =SUBTOTAL(9,B4:B7). What number will appear in B10 once the user presses Enter?

0 because this will result in a circular reference.

2000

8000

6000

None

8.

Why doesn't the SUM formula in E10 show an answer of $30,000?

The database has been filtered so there must be hidden rows containing other income figures.

The correct formula for this situation is SUMPRODUCT rather than SUM.

Conditional formatting has been applied to rows 3, 4, and 6 so that these rows are excluded from the SUM calculations.

Incorrect data validation has been used.

None

9.

Why could the formulas in C3 and C4 cause #VALUE! errors?

B3 is an empty cell.

B3 contains one or more space characters.

The formulas have used brackets incorrectly.

None of the above

None

10.

Why does the SUM function in B7 show zero?

Manual calculation is turned on and the cell has not been re-calculated.

Someone has keyed the number 0 into B7.

The function contains a circular reference.

The function is still being edited so hasn't calculated a result yet.

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:

How ChatGPT’s Premium Features are Revolutionising…
Leveraging ChatGPT for Transformative Finance Functions: A…
Unleash the Power of Cloud-Based Budgeting &…
Streamlining Power Query Code Documentation with GPT-4: A…
Navigating the New Normal: Why CFOs are embracing Data…
Excel Office Scripts for Effortless Data Movement

Jackie Meddows-Taylor2024-10-04T10:58:41+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

Δ