Check And Evaluate Formulas In Excel 2010
We have just finished a formula in Excel (which we are proud of) but we want to make sure that what we did is correct. What do we do?
We can use Excel’s Evaluate Formula function. It helps us go through the steps that we used when we were creating the formula. Very useful function especially for long formulas and ones that have precedents, because it makes it very easy to see how Excel has calculated this formula.
First of all we must select the cell that contains the formula and then select the Functions tab and then from the area of the ribbon named Formula Auditing we select the command Evaluate Formula as we can see in the image below.
Once selected the Evaluate Formula dialog box appears as we can see in the image below. At the top left corner of the window and under Reference, we see the cells reference which is IF!$I$4 (the actual name of the sheet and the actual cell).
In the Evaluation area of the Evaluate Formula window we see our formula. In this example the formula is =IF(Criteria!A2>=10000;“Bronze”; “Gold”).
We notice that a part of the formula is underlined (Criteria!A2), so if we click the Evaluate button, we will see the actual value that the specific cell contains, as we can see in the following image.
Now the part 6300>=10000 is underlined. If we click the Evaluate button, we will see the result of the first part of the formula. Seeing that it was an If function and the criteria 6300>=10000 is not True, the result that appears is False as we can see below.
By pressing once more the Evaluate button the actual result of the formula appears as we can see. The Evaluate button changes to Restart in order to start evaluating the formula again.
When we are evaluating a formula, we notice the Step In and Step Out buttons that appear on the Evaluate Formula dialog box. These buttons are enabled during the steps that have got to do with precedents as we can see in the image below.
Here we see the actual sheet and cells reference Criteria!$A$2 that we have stepped in, and at the right we see the actual formula that the specific formula contains. Once we have checked it we can press the Step Out button in order to continue Evaluating the formula.
Below you can check out the video which describes how to use the Evaluate Formula in Excel 2010.
Posted on June 4, 2012, in Excel 2010 English, Microsoft Office 2010 English and tagged Evaluate, Evaluate Formula, Excel 2010, Formula Auditing, Formulas, Microsoft Office 2010, Office Smart, Reference, Smart Office, Step In, Step Out. Bookmark the permalink. Comments Off on Check And Evaluate Formulas In Excel 2010.