tailieunhanh - formulas and functions with microsoft excel 2003 phần 8

Trong ô B2 nhập trong năm qua số lượng thời gian mà mua sẽ được khấu hao. 3. Nhập giá trị cứu hộ trong tế bào B3. 4. Tính khấu hao trong năm thứ năm trong ô B5 với công thức sau Sử dụng chức năng sln để tính toán đường thẳng | Use the DATEDIF function to determine all friends younger than 30 You have the birth dates of your friends listed in a worksheet and want to shade those who are currently younger than 30 years old. Use the TODAY function to determine the actual date and the DATEDIF function to calculate the exact age then combine those functions with conditional formatting. To determine all friends younger than 30 1. In a worksheet enter data in cells A2 B10 as shown in Figure 10-25. 2. Select cells A2 B10. 3. On the Format menu click Conditional Formatting. 4. Select Formula Is and type the following formula DATEDIF B2 TOdAy Y 30. 5. Click Format. 6. From the Patterns tab select a color and click OK. 7. Click OK. Figure 10-25 270 Chapter 10 Use the MONTH and TODAY functions to find birthdays in the current month Use the same list from the previous tip to determine whose birthday falls in the current month. Use the TODAY function to determine the actual date and the MONTH function to compare the month of everyone s birthday with the current month then combine those functions with conditional formatting. To determine all friends whose birthday is in the current month 1. In cell D1 enter the formula TODAY . 2. Select cells A2 B10. 3. On the Format menu click Conditional Formatting. 4. Select Formula Is and type the following formula MONTH TODAY MONTH B2 . 5. Click Format. 6. From the Patterns tab select a color and click OK. 7. Click OK. D1 A TODAYQ A B c D E 1 name birthday today 3 29 2006 2 james 12 3 1956 3 michael 3 3 1980 4 Sarah 11 18 1966 5 sue 2 23 1970 5 Steve 4 21 1979 7 diana 3 7 1981 s era 11 23 1992 9 alex 1 22 2002 10 waiter 4 25 1945 11 12 Figure 10-26 Conditional Formatting with Formulas 271 Use conditional formatting to border summed rows Enhance worksheets with this tip for placing a border on special cells. The worksheet contains daily sales for different teams. Their sales are summed up after a certain period to get a current status. To enhance the visibility of .