Access Analytic: financial modelling, management reporting and training to help companies make decisions with confidence
<IMG SRC="/images/bw_images1.gif" WIDTH=312 HEIGHT=62 usemap="#bw_images1" BORDER=0>
decisions with confidence
financial modelling, management reporting and Excel auditing services
in-house and open learning training courses
Free Excel products, templates and add-ins to download
knowledge, research and whitepapers
contact us
about us
 

Services

> Services Home
> Financial Modelling
> Management Reporting
> Spreadsheet Auditing
> Training Courses
> Excel Mentoring
> How to Engage us
> Newsletter
> Free Excel Test
> Links

"The greater danger for most of us is not that our aim is too high and we miss it, but that it is too low and we reach it."

-- Michelangelo

  
 

Newsletter: Sample

  

Test your Excel Knowledge - FREE!

Test Your Excel Knowledge

... Then Brag to your Friends on Facebook!

Exams Available:
- Advanced Excel
- Excel 2007

How well do you know Excel and Financial Modelling?

Take one of our free online exams and find out!  There are 10 challenging questions in each test.  For your own benefit, try to complete the exam with Excel (and its online help) closed.

Even if you don't know the answers, have a guess anyway ... we'll tell you all the answers anyway, and you might learn something!

At the end, you'll also get a beautiful certificate you can print!

Post your score on Facebook so all your friends know how great you are!  Challenge them to see if they can beat you!

Looking for more in-depth training?  Visit our other training resources.

Now ... bring on the questions!  (but watch out ... they're not as easy as they look)!

Advanced Excel Online Exam:

Questions

Q1: 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.

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


 Note: the formula in C3 has been copied to C4:C6
 
B3 is an empty cell.
B3 contains one or more space characters.
The formulas have used brackets incorrectly.
None of the above

Q3: 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.

Q4: 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

Q5: What does the word "Calculate" indicate when displayed in the status bar?


 
Calculation is set to manual and something in the spreadsheet has changed.
The calculation option has been set to Automatic Except Tables.
The active cell needs to be re-calculated.
A calculation on the current sheet is incorrect.

Q6: 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.

Q7: 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.

Q8: The range name "EmployeeLookupTbl" has been correctly defined to refer to the shaded area. What needs to be fixed in B5 and B6 so the functions 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.

Q9: 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?

=IF(A1<500,"Fair",IF(AND(A1>500,A1<1500),"Good","Excellent"))
 
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 "Excellent"

Q10: 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.

 Privacy Policy | Terms & Conditions
top


Copyright © Access Analytic Solutions Pty Ltd 2002-2010 ACN 091 625 697. All rights reserved.
Access Analytic® and its logo are registered trademarks of Access Analytic Solutions Pty Ltd
Ground Floor, Unit 110/131 Adelaide Tce, East Perth WA 6004, Australia. Phone +61 8 6210 8500

Excel® is a registered trademark of Microsoft Corporation

Javascript must be enabled to use this site as all e-mail addresses have been encrypted
to protect against spam-bots. You can use this free tool too!