Spreadsheet skills assessment
Try our on-line assessment
Home Register Contact About Us

  XL-Excellence® - Examples - Formula functions


Links

Some spreadsheet functions cause heavy calculation loads and often can be replaced by faster functions. The XL-Excellence® Add-In can help in detecting and reporting such functions.

Consider the following example.

The spreadsheet shown below deals with depreciation: Assets are purchased in each year. And they depreciate over a period defined in cell C2. Currently assets depreciate over three years.

Depreciation spreadsheet

Row 8 calculates, as at the end of each year, the amount of assets that have depreciated over that year.

Since asset life is currently three years then – for each year – the calculation must add the value of assets purchased over that year and the prior two years. If, however, C2 was 4 and assets depreciated over four years then the calculation must refer to the current and prior three years.

Shown below is an XL-Excellence® report that assesses two alternative ways of performing the depreciating assets calculation.

Heatmap showing two alternate ways of calculating depreciation

The heatmap above has been generated to report on formula functions – whether or not they are on a “do-not-use” register. The calculations on row 8 – shown in red – use a function (OFFSET) that the user nominated as being "disallowed".

Calculations on row 8 using disallowed functions

The alternative calculations on row 11 use “allowed” functions.

Calculations on row 11 using allowed functions

You can set which are “allowed” functions and “disallowed” by using the settings option ..

Setting options

Why is the OFFSET function best avoided if an alternative is available? Because it is a “volatile” function that causes a high computer load when any cell changes in any open workbook.

If you wish to experiment with the examples discussed above you can download the examples by choosing from the Links section.

Links


Sitemap | Terms of use | Privacy | Contact us
- 3.237.205.144 -