Cebano Excel Utilities


KWIK Utilities EXCEL Add-In

Column Calculator

What it does

When building and managing large column-type worksheets in Excel, the following problems often occur:
  • Excel gets bogged down during recalculation.
  • Editing formulas becomes messy due to having to scroll around to find column cell references.
  • Testing and auditing complex formulas becomes time consuming and risky due to having to "decode" cell references to meaningful names.
  • Because Excel stores the formula in each row, there is a risk that the formulas are not the same throughout the column. This can happen for example when updating a formula and not copying it all the way down.
KWIK Column Calculator provides a logical framework and tools to build and manage column-type spreadsheets using a more efficient way to deal with formula references, thereby saving you time and reducing risk of errors.

Benefits

  • Formulas are edited algebraically using column headings (stored in the first row), e.g. 'Date of birth', thereby greatly enhancing the editing and auditing process.
  • A 'Parameters' Worksheet is designated where overall parameters are named and can be used in formulas, e.g. 'Date of calculation'.
  • A 'Lookup Tables' Worksheet is designated where you can name and set tables used in VLOOKUP functions in formulas.
  • An enhanced VLOOKUP function is provided which allows for the table and the return column in the table to be referred to by name.
  • Formulas are edited in a form which lists all the available Columns, Parameters and Tables by name. These can be selected and inserted into the formula.
  • A Formula Test facility is provided allowing you to manually enter values for the source Columns to check your formula.
  • Once columns are calculated, only the values are stored so that EXCEL does not get bogged down in recalculations.
  • Since the Columns do not contain formulas, file sizes are reduced.
Example:
 Excel Formula: =MAX((BV1234-BR1234)*BW1234,(BY1234-BR1234)*BZ1234,0) becomes
 Column Formula: =MAX((New Height - Old Height) * Old Rate, (Max Height - Old Height) * New Rate,0)

Instructions

It is highly recommended to work through the Demo Workbook. Working through the example will help to understand the framework.

Create a new Workbook or open an existing one and Click 'KWIK->New Column Worksheet'. This will add worksheets '_Parameters', '_Lookup Tables' and 'Column Calculator'.

_Parameters Worksheet:
  1. This is where one can store overall parameters or constants for use in formulas, e.g. 'Effective Date'.
  2. Parameters are set up row by row
  3. Column A contains the name of the Parameter, e.g. 'Effective Date'.
  4. Column B contains the Parameter value, e.g. '1/1/2016'.
  5. Parameters are available as variables when editing Column Formulas, using the name of the variable.
  6. You can have as many Parameters as required.
  7. Note that all Parameters must be stored in the '_Parameters' Worksheet which is available to all 'Column Calculator' Worksheets in the Workbook.
_Lookup Tables Worksheet:
  1. Create and manage tables that for use in Column Formulas, i.e. as a source for VLOOKUP() formulas.
  2. Tables are set up one below the other as indicated with a blank row in between tables.
  3. Column A contains the Name of the table.
  4. Column B contains the lookup field, i.e. used to decide which row to return.
  5. Column C onwards contain the potential return values from the table.
  6. The row above the first data for the table is used to optionally store the name of the column.
    (When constructing formulas, these column names can be used to specify the output column instead of the numeric approach used in VLOOKUP() - This makes it easier to understand the formula, e.g. instead of column 2, the formula would refer to e.g. "Category". If additional columns are inserted later, or the order changes, there is no need to update the formula.)
  7. Don't leave more than one blank row between tables.
  8. A Workbook has one '_Lookup Tables' Worksheet which is available to all 'Column Calculator' Worksheets in the Workbook.
  9. Note that one can use an existing Column Calculator worksheet as a lookup table. The first column is the lookup column.
Column Calculator Worksheet:
  1. This Worksheet is where the data and formulas are stored, edited and calculated.
  2. The Blue row contains the Column name. You can use names that are logical for you, e.g. 'Date of birth'.
  3. The Yellow row contains the formula (in text format) for each respective column.
  4. If a column contains input data and does not have a formula, e.g. 'Personnel number', leave the yellow cell blank to inform the engine not to calculate the column.
  5. Column Calculations are performed from left to right, hence a Column Formula can reference Columns to its left in its formula.
  6. To edit a Column formula, highlight any cell in the column and click 'KWIK->Edit Column Formula'.
    The form displays 'Columns', 'Parameters' and 'Tables' that are available.
    If one selects a listed entry, the application will highlight the corresponding area in the Workbook
    To added an entry to the formula text area, double-click the name in the list.
    Clicking on a name listed below 'VLOOKUP Table' brings up a sub-form which allows for a 'lookup field name' and 'output field name' of the respective table to be selected.
    Press 'OK' to insert the appropriately formatted VLOOKUP() Function into the editing area.
    To manually test the formula being edited, click the 'TEST FORMULA' button, enter test values for the Columns referenced by the formula and click 'CALCULATE'.
    Press 'OK' when finished editing which will update the column formula in the Yellow cell.
  7. In the edit formula area, highlighting a variable in the formula will also highlight the corresponding area.
  8. One can manually edit the formula cell, but note that it is stored as TEXT in the cell, i.e. it is not an EXCEL Formula. Do not put "=" in front of the formula for the Column Formula.
  9. To calculate all the Column Worksheets in the active Workbook, click 'KWIK->Calc Workbook'.
  10. To calculate all the columns in the active Column Worksheet, click 'KWIK->Calc Active Worksheet'.
  11. To calculate Selected columns in the active Column Worksheet, click 'KWIK->All Selected Column(s)'.
  12. To calculate from the Selected Column onwards, click 'KWIK->Calc Selected Onwards'.
  13. Hold the SHIFT button in whilst calculating, to leave the decoded Excel formula in the cells. This may assist in debugging formulas.
  14. A Workbook can have many 'Column Calculator' Worksheets. Each worksheet operates independently and can be renamed. To add a new Column Calculator Worksheet, Click 'UTILITIES->New Column Worksheet'.
Other notes:
  • You can insert other normal Worksheets as required.
  • The functionality works with .xlsx and .xlsm files.
  • If you send the file to someone who does not have KWIK Utilities installed, they will be able to read the spreadsheet values, but they need their own copy of the Utilities to edit and calculate the formulas.

See the Demo Workbook for an example.