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. 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" 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. 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. 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. 6. 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. 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 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. 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 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. 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:Unleash the Power of Cloud-Based Budgeting &…Excel Office Scripts for Effortless Data MovementDynamic Data Validation with Tables in ExcelStreamlining Power Query Code Documentation with GPT-4: A…Power Query: Unpivot using Column Numbers rather than NamesUnleash ChatGPT’s Power in Office 365 Jackie Meddows-Taylor2023-11-01T17:28:08+08:00 Share This Story, Choose Your Platform! FacebookTwitterLinkedInEmail