How to perform formula calculations in Excel

Versions of Excel earlier than Microsoft Excel 2002

In Excel 2002, the formula calculates the worksheet based worksheet and starts from the Sheet1 of the Excel workbook. After you complete the calculation on the worksheet Sheet1, Excel calculates the formula on Sheet2 until you calculate all the formulas on all worksheets on the workbook.

This process works fine as long as the reference and dependent cells are on the same worksheet. This can cause problems if you refer to cells that are different from the dependent cells, but are referenced across worksheets.

For example, if the formula in the worksheet Sheet1! A1 is = sheet3! A4 + 1, these two worksheets sheet1! A1 and sheet3! A4 must be calculated. The beginning of the worksheet Sheet1. When you try to compute A1, you may find that you cannot because sheet3! A4 does not have an outstanding calculation. Try to calculate the worksheet Sheet1 you can continue. When you finish using the worksheet Sheet1, Compute Sheet2. Then compute SHEET3, and finally update sheet3! A4.

At this point, you clicked on all the worksheets. However, the worksheet sheet1! A1 is still not evaluated, so go back and start recalculation of the worksheet Sheet1. Finally, the worksheet sheet1! can compute A1. The loop attempts to compute any number of times, depending on how many worksheets and how they are laid out across the worksheet dependencies.

Determined that this process is inefficient for calculating formulas in workbooks.

Excel 2002 and Microsoft Office Excel 2003

In Excel 2002 and Excel 2003, a large list of cells that are referenced by the calculation formula is preserved. That is, all the formulas in the workbook contain the formulas that are put into memory as a contiguous list. When you enter a new formula and calculate it exactly at the first time, place it in the correct position in the calculation chain.

Excel starts the calculated formula from the top of the list of tasks. If you have not calculated the referenced formula, jump down in the list of Excel formulas and calculate the dependent formula. Excel then returns a reference to the formula and completes the calculation.

When you finish the formula in the list, Excel moves to the next formula and continues until you calculate formulas for all formulas in the list.

With the calculation formula in this new procedure, Excel will only be able to calculate formulas in the workbook once.

Some other calculation changes should be noted as follows:

• Reentrant computations are not allowed at this time. You cannot make a call to the Range.Calculate method in Microsoft Visual Basic for Applications (VBA) If you are already in the calculation. When you include a user-defined function in cell A1, you can range.calculate the user-defined function. You will receive a run-time error.

• Name.add methods or Name.delete methods cannot be executed in user-defined functions.

• If you are using the SUMIF () function or the COUNTIF () function any of these conditions are more than 255 characters, you will be raised

#VALUE

An immediate error message.

• If you want to copy cells from the entire worksheet that contain more than 255 characters, immediately dirty the entire table.

• You cannot perform range.calculate when you have multiple selected worksheets. This also raises a run-time error.

• In some cases, Excel may be very slow to compare to Excel 2000 and earlier in particular by using CTRL + ALT + F9 or performing a full recalculation from an earlier version of Excel in Excel 2002 or in Excel 2003 When a full recalculation is performed, the calculation is done.

Microsoft Office Excel 2007 and Excel 2010

A set of functions in Excel 2007 to perform formula calculations. To optimize performance, the set of functions aggregates to as few queries as possible. When other Excel features are idle, queries for these aggregations are usually performed in the background.

In an aggregate query, a large list of cells that are referenced by the calculation formula is preserved. As a function in a contiguous list, all functions in the workbook are included in memory.

When you enter a new formula in the workbook, the new formula is evaluated for the first time. When you first calculate a new formula, Excel 2007 generates a set of functions that belong in the new formula, and each function in the function and the set of functions calculates the correct position in the chain.

Each cell is computed based on the processing of the calculation chain. If a cell with no dependencies is in another function, the cell immediately receives its value. If the cell has dependencies on another function, the cell receives the #getting_data ..., the placeholder error value, and then calculates the next cell that is passed to the chain.

After all the cells that are evaluated at once, Excel 2007 triggers the aggregate query or query to retrieve the data required. continues to display the placeholder error value after the aggregated query that is being processed when the cell is waiting for data #getting_data ....

After the processing of the aggregated query or query completes, recalculate the #getting_data that contains the placeholder ... The cell of the error value, and the value that the cell receives.