Microsoft Office Development and Consultancy

Excel

What is a Mega Formula ?

Quite often a formula requires a number of intermediate formulas in order to produce the correct result.

After you have got all your formulas working it is possible to eliminate the intermediate formulas and create one big "mega formula".

Formulas can only contain a maximum of 1024 characters.

If your "Mega formula" is longer than this then you should consider creating a user defined function.

What are the Advantages ?

Fewer Cells

Recalculation is a lot faster

Size of the workbook is reduced

What are the Disadvantages ?

Significantly harder (if not impossible) to understand and modify.

Example

illustrating the following

concatenating

finding the first space

finding the next space

removing excess spaces

getting first, middle and last words

Rather than edit all these manually you could opt for a formula based solution.

When you are satisfied that the mega formula is returning the same result you can delete all the intermediate formulas.

It is often to keep a copy of the intermediate formulas in case you need to make an ammendment to the formula at a later date.