Optimising & Summarising Large Formulas

0
votes

I'm working on a spreadsheet which will forecast the changes to certain costs in our building business based on estimated inputs.

For example, we may speculate that the price for a carpenter to complete a fitout will increase by $8 per m2 in Brisbane in August. We would write this data as:

  • Area = Brisbane
  • Month = August
  • Cost Centre = Carpenter Fitout = 150
    • We split each of the costs for building into different cost centres, represented numerically.
  • Increase = $8
  • Unit = m2

Based on this data, we can speculate how much each cost will increase in the coming months, and this is what I'm trying to do automatically.

The following are representations of the tables that I'm using in the spreadsheets.

Raw Data

An example of how the data looks raw from the import worksheet.

Area   | Month     | Centre | Value | Unit
-------|-----------|--------|-------|------
Bris   | August    | 150    | 10    | %
Sydney | September | 350    | 15    | m2

Import Table

How the data will be imported into the data analysing worksheet. The area, month and cost centre are combined for the VLOOKUPs later.

Label              | Value | Unit
-------------------|-------|------
BrisAugust150      | 10    | %
SydneySeptember350 | 15    | m2

Calculation Table

All of the units that can be used in the import, and which calculation they correspond to. m2, m2t, m3, and EACH all use the same calculation (calc 4).

Unit | Calc | Description
-----|------|------------
FLAT | 1    | = Increase_Value
%    | 2    | = Month_Value * Increase_Value / 100
000  | 3    | = Standard_Value * Increase_Value / 1000
m2   | 4    | = Standard_Value * Increase_Value
m2t  | 4    |
m3   | 4    | 
EACH | 4    |

Centre Values

Examples of standard quantities/dimensions that correspond to each of the cost centres.

Centre | Value
-------|-------
50     | 6
100    | 12
150    | 17
200    |
250    | ...
300    |
350    |
400    | etc

Monthly Data Dumps (For each Area)

Raw data is pasted into here from the live database at the beginning of each month to represent the costs associated with them.

        July    August    September    October
Centre
50      7       16        ...         etc
100     68
150             
200
250     ...
300
350
400     etc

Example Outputs

A summarised version of how the output will look, where each of the cost centres are against each of the months, and if there is something from the import that corresponds to both of these the appropriate calculation will be done.

Brisbane:

        July    August    September    October
Centre
50
100
150             10%
200
250
300
350
400

Sydney:

        July    August    September    October
Centre
50
100
150
200
250
300
350                       15m2
400

Formula So Far

A psuedo-code version of the formula that will be featured in each cell so far. I thought it would be easier to decipher with labels instead of cell references, IFNA formulas taken out, etc.

=CHOOSE(
    VLOOKUP(                        // Determine whether to use calc 1, 2, 3, or 4.
        VLOOKUP(                    // Unit of calculation (i.e. m2, EACH, etc).
            Area&Month&Centre,
            Import_Table_Value,
            3,
            FALSE
        ),
        Calculation_Table,
        2,
        FALSE
    ),
    VLOOKUP(                        // Calc 1: Flat increase will only look up the increase value.
        Area&Month&Centre,
        Import_Value_Table,
        2,
        FALSE
    ),
    (                               // Calc 2: % increase.
        VLOOKUP(                    // Lookup the value from the monthly data dump corresponding to the appropriate month & cost centre.
            Centre,                 // Cost centre (for each row).
            Monthly_Data_Dump,
            Appropriate_Month_Column,
            FALSE
        ) * VLOOKUP(                // Lookup the increase value.
            Area&Month&Centre,
            Import_Value_Table,
            2,
            FALSE
        ) / 100
    ),
    (                               // Calc 3: 000' increase
        VLOOKUP(                    // Lookup the appropriate value from the cost centre values table.
            Centre,
            Centre_Values,
            2,
            FALSE
        ) * VLOOKUP(                // Lookup the increase value.
            Area&Month&Centre,
            Import_Value_Table,
            2,
            FALSE
        ) / 1000
    ),
    (                               // Calc 4: Linear increase.
        VLOOKUP(                    // Lookup the appropriate value from the cost centre values table.
            Centre,
            Centre_Values,
            2,
            FALSE
        ) * VLOOKUP(                // Lookup the increase value.
            Area&Month&Centre,
            Import_Value_Table,
            2,
            FALSE
        )
    )
)

Basically, the formula will lookup a number from 1-4 and "choose" which formula will be used to determine a cell's value (if at all).

The spreadsheet has over approximately 300,000 cells to update across all the different areas, and running the formula as is takes an hour or more. I'm trying to reduce all the bloat and improve the time taken for the sheet to compute.

I've been dabbling with using INDEX MATCH instead of the VLOOKUPS, as well as trying some of the general optimisation tips that can be found online but the results only take off 5-10 minutes.

I'm after a more solid solution and am looking for advice on how to do that.

1
exceloptimizationexcel-formula

1 Answers

0
votes

Looking at this from a data perspective you have 4 sets of information which can be represented as 

    RAW   |  CALC  |   DUMP   |   CENTRE
----------|--------|----------|-----------
   Area*  |  Unit* |   Area*  |   Centre*
   Month* |  Calc  |   Month* |   CentreValue
   Centre*|        |   Centre*|
   Value  |        |   Dump   |
   Unit   |        |          |

RAW is your Raw Data Table, CALC is your Calculation Table, DUMP is equivalent to your Monthly Data Dumps and CENTRE is your Centre Values table.

We can conceive of these as the tables of a database with the labels in each column above representing the columns of the corresponding table. Columns with an asterisk represent the primary key(s) of the table. So, for example, table RAW has 5 columns and is keyed on the combination of columns Area, Month and Centre.

In a real database, these 4 tables could be joined to form a "view" which looks like

VIEW
--------
Area*
Centre*
Month*
Value
Dump
CentreValue
Calc

An additional column, say Result can be added to this view and (assuming I have understand your pseudo-formula correctly) assigned as

Value                   if Calc = 1
Value*Dump              if Calc = 2
Value*CentreValue/1000  if Calc = 3
Value*CentreValue       if Calc = 4

At the risk of not knowing all the subtleties of your data, in your position I would be giving consideration to implementing the above using a database approach.

3 of your inputs (RAW, CALC and CENTRE) already appear to be in the required table format whilst the fourth (DUMP) is sourced from a database so you may be able to get in the required format from its source (if not you'll just have to bash it into shape - not difficult).

The use of SQL for joining the tables into the required view replaces that complex nested set of VLOOKUP's and is likely to be considerably more efficient and faster. MS Access would be a good solution, but if not available to you you could try using MS Query. The latter is accessed via the Data Tab of the ribbon (From Other Sources/From Microsoft Query) and can access tables which are set up as named ranges in an Excel workbook. With MS Query you will need to put the input tables in a different workbook from the results view.

Both Access and Query employ a visual method for joining tables together and there will be plenty of tutorial material available on the web. Excel can import a "view" from Access (where views are known as Queries) and if using Query, closing the query pop-up window results in a prompt about whereabouts in the workbook the data should be placed.

Once you have your results in a database table format in Excel a pivot table will quickly get it to your required output format.