![]() |
Microsoft Office Development and Consultancy |
| Home | | | Excel | | | VBA | | | C# | | | Finance | | | Tools | | | Newsletter | | | Feedback | | | Contact |
| 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 ? |
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. |
| © Better Solutions Limited 10-May-2013 | < Previous | Top | Next > |