Suggestions to write an efficient Excel formula

Excel formulas are great. We can calculate almost everything with their help. But their efficiency and performance will always be a big concern especially when they are being used to calculate some price in a live environment just as in our Woo Price Calculator.

Performance and efficiency of these formulas become a big concern when the calculations are very complicated and depend on lots of data and fields.

There are many suggestions and recommendations provided from experts regarding how to make your excel formula work faster or more efficiently.

We are helping you here with few of them:

• Work from left to right

Actually, this tip is something which is already adopted by most of the people but I feel important here to mention the reason behind that. By default, Excel calculates expressions at the top-left corner of the sheet first and then continue to the right and down. For this reason, you'll want to store independent values in the top-left portion of your sheet and enter expressions (dependent cells) to the right or below those values. In a small sheet, you won't notice much difference, but a sheet with thousands of rows and calculations will definitely perform better when you position dependent cells to the right and below the independent values.

• Always Clean things up

Always delete what you are not using. If you are concerned about using that unused functionality any other time in future, copy it and save it somewhere else. Any unused independent/dependent calculation takes time and if it happens on regular bases, one must feel the delay in the actual calculations.

• Avoid writing extra-big formulas

Writing a formula in one cell with lots of references and operations is surely going to kill the performance. It is always efficient and faster to divide your monster formula into multiple intermediate formulas.

• Provide all the required arguments for Excel functions

As we know that different excel functions require different arguments/parameters in them. Just like VLOOKUP() method of excel, which takes 4 attributes or any other function which accepts multiple attributes might also work if one or more attributes are missing but when this function is loaded into WPC (PHP), it doesn't work exactly the same without those missing parameters and will most probably give the different results.

So it is very important always to provide all the required and correct attributes.

• Use static references instead of useless formulas

Sometimes it is seen that some people use some function like SUM(S2) or SUM(S12:) which is very wrong.

This is very wrong and doesn't make any sense. Using a simple static value S2 fulfills the same purpose as of SUM(S2). The same way if the SUM of a specific range is required then must mention the range from start to end and do not let it be open-ended because of this way, the function is unsure about the range and try to include as many cells into the formula as possible and it takes a lot more time than required. 

• Static Values vs Cell References

One of the most common errors when writing Excel functions is to use static values inside a formula. For example, most people simply prefer entering the column index when creating a VLOOKUP formula. The same goes for SUMIF, AVERAGEIF, or similar formulas. You will get the correct result regardless of how you build these formulas. Formulas return the correct results, however, things might get ugly if you try copying that formula into other fields.