      HOW TO Windows XP Windows Vista Windows 7 Windows Azure Windows Server Windows Phone Windows 7

Microsoft Excel 2010 : Calculating the Mode (part 4) - Getting the Mode of Categories with a Formula - Using Formula Evaluation

2/6/2014 3:18:21 AM
Using Formula Evaluation

If you’re using Excel 2002 or a more recent version, you have access to a formula evaluation tool. Begin by selecting a cell that contains a formula. Then start formula evaluation. In Excel 2007 and 2010, you’ll find it on the Ribbon’s Formulas tab, in the Formula Auditing group; in Excel 2002 and 2003, choose Tools, Formula Auditing, Evaluate Formula. If you were to begin by selecting a cell with the array formula that this section has discussed, you would see the window shown in Figure 5.

Figure 5. Formula evaluation starts with the formula as it’s entered in the active cell. Now, if you click Evaluate, Excel begins evaluating the formula from the inside out and the display changes to what you see in Figure 6.

Figure 6. The formula expands to show the contents of A2:A21 and C1. Click Evaluate again and you’ll see the results of the test of A2:A21 with C1, as shown in Figure 7.

Figure 7. The array of cell contents becomes an array of TRUE and FALSE, depending on the contents of the cells. Click Evaluate again and the window shows the results of the IF() function, which in this case replaces TRUE with 1 and FALSE with 0 (see Figure 8).

Figure 8. Each 1 represents a cell that equals the value in cell C1. A final click of Evaluate shows you the final result, when the SUM() function totals the 1’s and 0’s to return a count of the number of instances of Ford in A2:A21, as shown in Figure 9.

Figure 9. There are eight instances of Ford in A2:A21. You could use the SUMIF() or COUNTIF() function if you prefer. I like the SUM(IF()) structure because I find that it gives me more flexibility in complicated situations such as summing the results of multiplying two or more conditional arrays.

Using the Recalculate Key

Another method for looking inside a formula is available in all Windows versions of Excel, and makes use of the F9 key. The F9 key forces a calculation and can be used to recalculate a worksheet’s formulas when automatic recalculation has been turned off.

If that were all you could do with the F9 key, its scope would be pretty limited. But you can also use it to calculate a portion of a formula. Suppose you have this array formula in a worksheet cell and its arguments as given in Figure 2:

=SUM(IF(A2:A21=C1,1,0))

If the cell that contains the formula is active, you’ll see the formula in the Formula box. Drag across the A2:A21=C1 portion with your mouse pointer to highlight it. Then, while it’s still highlighted, press F9 to get the result shown in Figure 10.

Figure 10. Notice that the array of TRUE and FALSE values is identical to the one shown in Figure 7. Note

Excel formulas separate rows by semicolons and columns by commas. The array in Figure 10 is based on values that are found in different rows, so the TRUE and FALSE items are separated by semicolons. If the original values were in different columns, the TRUE and FALSE items would be separated by commas.

If you’re using Excel 2002 or later, use formula evaluation to step through a formula from the inside out. Alternatively, using any Windows version, use the F9 key to get a quick look at how Excel evaluates a single fragment from the formula.

 Other -----------------

REVIEW
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)  VIDEO TUTORIAL
- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)  Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS  Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating  Top 10   Windows XP Windows Vista Windows 7 Windows Azure Windows Server Windows Phone 2015 Camaro