Question

If you make a mistake in creating your formulas, you can get some significant errors in your worksheets. Discuss the kind of errors that are possible with the use of formulas. Give examples.

Excel updates relative references when formulas are copied. Could Excel ever be “too smart”? Might updating relative references when formulas are copied ever produce the wrong result? When?

Think about what would happen if the default in Excel was absolute cell references, rather than relative cell references. Would it be easier to create most worksheets? Why or why not?

Answer 1

The types of error that can happen with the use of formula are:

• Division by 0
• No value available
• Return of a null value
• Invalid cell reference
• Ambiguous name

Excel may give wrong result in examples where the formula needs to refer to a particular value. For example suppose we need to calculate on 5 principle amounts and 5 time periods but for a constant rate of interest. If the formula is keyed in a cell, and simple cell drag is done to copy the formula in subsequent cells, this may give error as the value of rate of interest needed to be locked with the use of F4 key or $. This is basically the usage of absolute cell reference.

If the default in Excel is absolute cell reference, working with large data sets will become difficult and tedious, as the formula drag function will not be effective. In such case, manual entry for each cell needs to be done.