Business Functions Library for Excel

      1. Getting Started
      2. Using The Help File
      3. Top Dozen Functions
      4. Golden Rules
      5. Excel 2007
      1. Go To
      2. Function Selector
      3. CalculateFull
      4. Calculate WorkSheet
      5. Trace Facility
      6. Quick Paste Example
      7. Tutorials
      8. Function Finder
      9. Examples
      10. Help
      11. Excel"s Function Wizard
      12. Access Internet
      13. Usage of Functions (Audit)
      14. Uninstall
      1. Time Periods
      2. Inclusive and Exclusive Dates
      3. Using Daycount
      4. Examples of DayCount
      5. Annual Date Sequences
      6. ProjMode and Inclusive Dates
      7. Date Rolling Convention
      1. Rate Projections Functions Walkthrough
      2. Accruals and Cash
      3. Repeating Formulae
      4. Range Names and References
      5. Optional Parameters
      6. Using PmtsPerYear
      7. Modelling Seasonality
      8. Calculating Interest
      9. Using Business Functions in VBA (Visual Basic for Applications)
      10. Array Functions
      11. Volatility
      12. Annual Equivalent Rates
      13. Array Function
      14. Auto Multi Functions
      15. Variable Plurality
      16. GoalSeek
      1. Introduction To the Worked Examples
      2. Daycount
      3. General Projections
      4. Business Plans
      5. Cashbasis And Periods
      6. Using Timebases
      7. Using Dates
      1. How To Generate a time scale for a structured financing
      2. How To Project Rents off a Rental Forecast
      3. How To Run a model on different time bases
      4. How To Isolate The Cause of a Errors in Cells using Trace
      1. Introduction to the Utilities
      2. Audit
      3. Synchronized Range Insert/Delete
      4. Database Edit
      5. Insert Macro Button
      6. Link Analyser
      7. Range Describer
      8. PrintScript (beta)
      9. Create Local Range Name
      1. Number Formats
      2. Apply BF"s Color Palette
      3. Bullets
      4. Color Cells
      5. Conditional Formats
      1. Validation DropDowms
      2. Validation Standards
      1. Select Excel Function
      2. Array Function Tools
      3. Sort Sheets
      4. Range Value
      5. Named Range Manager
      6. Enforce WorkBook Settings
      7. Monte-Carlo
      8. TimeChart
      1. The ".ini" file
      1. Forum
      2. Online Help
      3. Templates
      1. Conversion of Input Strings to Values
      2. List of Holidays
      3. Acknowledgements and Trademarks
      4. Published Editions Changes in Behaviour
      5. Range Handling And Constraints
      6. Dates in Excel and Business Functions
      7. Old Composite DayCount Format
      8. DayCount in Excels"s Functions
      1. NPV of Annual To Periodic CashFlows - CorrectionM
      2. Interest - Simple, Annual, Continous and Discount Factors
      1. New Functions
      2. Obsolete Functions
      3. Discontinued Functions
      4. Deprecated Functions
    Array Function
    How to use this most powerful and efficient Excel feature in Business Functions
    What is an Array Function?

    An array function is literally a function that returns an array of values, rather than a single value. As such an array function needs to pertain to a range, not a single cell. If you put an array function in a single cell, you will just get the first value of the array returned. To apply a function to an array of cells, you press Control-Shift-Enter whilst the array range is highlighted (see Excel documentation for more details).

    Excel"s use of array functions can be confusing. Whilst there are some functions that actually require a range to put there answers into, there are others that can either be used on a single cell or array basis.

    How does Business Functions use the Array Function Concept?

    There are two classes of array function in BF.
    • Functions that can only be used as array functions such as PolyReg, which returns a range of coefficients. This category is very simple. The function returns a range so you must provide a range, by way of applying the formula as an array function (Ctrl-Shift-Enter).
    • Functions which can optionally be used as array functions, such as FStep or any of the projections functions. These functions, in normal use, return a value for a single time period as specified by Time and Base. Array functions enable you to apply the function to a range of time periods all in one go - and yet still using the same function. All you do is provide a range of Times note that Base is ignored, the timeperiod length being determined from the Times If you then apply this to an array, the first cell contains the result for the first time period, the second cell the second result, etc. For functions that do a lot of detailed calculations that refer across time periods, this approach can save a lot of calculation time. One function that yields dramatic improvements in this manner is LoanX.

    What are the array function tools?

    Most people find Excels Ctrl-Shift-Enter methodology rather cumbersome and minimalist. So we developed a couple of useful menu options.

    • Expand expands a formula, array or normal, from the leftmost or topmost cell in a selected range to the rest of that range, creating an array formula.
    • Collapse eliminates the array formula from the selected range, placing a single formula in the top left most cell.

    Remember also the primary method of entering array formulas that Excel provides is to go Ctrl-Shift-Enter in the formula pane, and the formula will be "arrayed" into the currently selected range.

Business Functions Ltd, London, UK Website Design: Webpure