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 Tools
    Ways of working with array functions
    Because a lot of BF"s projections functions can also be used as array functions, we "ve supplied a couple of tools to help in their usage.

    To recap on array functions:
    • Where a Business Function can also be an array function, the "arrayness" comes in the first argument, which usually a date. So a range of dates as the input argument results in a range of results returned from the function, one for each date or time period.
    • Excel uses the Control-Shift-Enter key combination as practically the only support you get for array functions. You enter an array function by selecting a range, typing the formula in the formula pane and pressing Control-Shift-Enter. It"s the same process for editing. Thereafter what you have to remember is that Excel treats the array region as a single entity that you cannot change part (e.g. a cell) of. Don"t think you can type curly brackets ({}) to make an array function either - it doesn"t work because array formulas are actually stored within Excel in an entirely different way to "normal" formulas.

    The two utilities we"ve provided are:

    1. Expand Array Function

    • When you"ve got a long argument list to complete, you might not want to go straight away to the array form of he function. In fact, we"d recommend that you first of all use the "normal" version of the function, then the "normal" version with a range of inputs (which returns the first result in the return range), and only then convert that function to an array function over the selected range.
    • For the step of converting a single function in a cell to a range, use the "Expand Array Function" utility. This will place an array formula in all the cells of the selected range, where the array formula is the formula in the top-left cell of the range in question. So it essentially expands a single cell"s "normal" function to a fully fledged array formula.
    • If you select a 2-dimensional arrays, the utility will ask you a question about how you want the formulae expanded. It will ask whether you want the formula expanded to the right, whether you wanted it expanded down, or whether you want in expanded both down and right. This is useful for "arraying a whole column of different formulae over a range of columns in your timebase (the same applies to a whole row over the rows in your timebase too).

    2. Collapse Array Function

    • This is the reverse of "Expand Array Function".
    • You select a range and the utility will concert the array formulae in that range to single cell "normal" formulae.
    • The resulting "normal" formulae will be created either in the leftmost column or the topmost row, depending on the context. The utility decides whether your "context" is horizontal (array formulas along rows), or vertical (in columns) by inspecting the first two array formulae in the first row and column and seeing if they are identical.

    Note that neither of these two utilities currently have an undo facility.

Business Functions phone: +44 (0)20 8144 9374 (London UK)
Website Design: Webpure