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
    Range Names and References
    Specifying function arguments and the case for named ranges
    When specifying the arguments to a function, there are broadly two types of argument:
    • Fixed (Absolute) Arguments. This is information you want to be the same, even if you copy the formula containing the function over to other cells. The key assumptions of a projection function, like the AnnualRates for example, will usually be fixed arguments.
    • Moving (Relative) Arguments. This is information that refers to cells that you want to move in the same way as the function moves. For example, if you copy the function from cell A1 to cell A2, you want a reference in the formula to change from E1 to E2. You usually make Time a moving Argument.
    If you input data by reference, i.e. referring to other cells, Excel"s default is to offer up a relative reference. If you want this reference to be fixed, and if you are adopting repeating formulae as you will most of the time, you should "double dollar" this reference, changing it to $E$1. Pressing function key F4 immediately and repeatedly after you have input the reference gets Excel to cycle through the various options for you.

    A better method to absolute referencing through double-dollaring is to give the source range a range name such as "Rents" or "InterestRates". Your formula will be much easier to read, and thereafter Excel will automatically offer up the rangename, rather than the reference, if you pick on that range when editing a cell"s formula. Note that a range name can refer to a single cell or a range of cells, on any single sheet.

    In BusinessFunctions most ranges are 1 dimensional because they are lists of things.

    Tip:When using Business Functions, try to use the Business Functions name for your variable, for example GrowthDates, GrowthRates. You won"t be able to do this all the time, but when you do it will make it easier when you paste functions from the Quick reference or Wizard, because the function template will already contain the names and are likely to work with the minimum of editing.

Business Functions Ltd, London, UK Website Design: Webpure