Services

"I've never come across consultants with the level of knowledge and proficiency that Access Analytic staff have. The things they can make... Stephen Smith more »
The complex spreadsheet models we developed were used by the bank to test new areas of functionality in the banks' lending applications.... Susan D Evans more »
Testing and documentation of key business spreadsheets using Spreadsheet Professional "The services Access... Darren Gordon more »

"Impossible? Nothing's impossible. Impossible just takes a couple of extra calls." Michael J Fox (in 'The Concierge')
"I am always doing things I can't do, that's how I get to do them." Picasso
"Don't dwell on what went wrong. Instead, focus on what to do next. Spend your energies on moving forward toward finding the answer." Denis Waitley

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.

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
	A value of 500 will display
	A value of 1500 will display
	A value of -500 will display

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.

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.

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.

Subscribe Newsletter

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.

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

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.

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

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.