Microsoft Office Development and Consultancy
|Excel > Formulas > Mega Formulas||< Previous | Next >|
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 ?
Recalculation is a lot faster
Size of the workbook is reduced
What are the Disadvantages ?
Significantly harder (if not impossible) to understand and modify.
illustrating the following
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.
|© Better Solutions Limited 06-Dec-2013||< Previous | Top | Next >|