When conducting simple math or building highly complicated spreadsheets that require formulas up to the task, leveraging the right formula can heighten the accuracy and efficiency of your work, and can improve the speed with which you compile and analyze data. Understanding which formulas to use and knowing how to create a formula when you need to are essential.

. . . . . . . . . . . . . . . . . . . . . . . . . . 691 A function for a more complex commission structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 692 Text Manipulation Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 693 Reversing a string . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

shows the Paste Name dialog box. Figure 3-8: Use the Paste Name dialog box to create a list of names. Caution The list of names does not include hidden names or worksheet‐level names that appear in sheets other than the active sheet. The list of names pasted to your worksheet occupies two columns. The first column contains the names, and the second column contains the corresponding range addresses. The range addresses in the second column consist of text strings that look like formulas.

negative. Note that this function doesn’t calculate full years. For example, if cell A1 contains 12/31/2014, and cell B1 contains 01/01/2015, the formula returns a difference of one year even though the dates differ by only one day. You can also use the YEARFRAC function to calculate the number of years between two dates. This function returns the number of years, including partial years. For example: =YEARFRAC(A1,B1,1) 142 Part II: Leveraging Excel Functions Because the YEARFRAC function is

{=SUM((WEEKDAY(DATE(YEAR(A1),MONTH(A1),ROW(INDIRECT("1:"&DAY (DATE(YEAR(A1),MONTH(A1)+1,0))))))=B1)*1)} If cell A1 contains the date January 6, 2015 and cell B1 contains the value 3 (for Tuesday), the formula returns 4, which reveals that January 2015 contains four Tuesdays. The preceding array formula calculates the year and month by using the YEAR and MONTH functions. You can simplify the formula a bit if you store the year and month in separate cells. The following formula (also an array

cumulative time (using column C). The cells in column G are formatted using the following number format (which permits time displays that exceed 24 hours): [hh]:mm:ss On the Web You can access the workbook shown in Figure 6-12, jogging log.xlsx, at this book’s website. 7 Counting and Summing Techniques In This Chapter ● Counting and summing cells ● Counting and summing records in databases and pivot tables ● Basic counting formulas ● Advanced counting formulas ● Formulas for