By Excel offers more than four hundred different functions to give you increased functionality for crunching the numbers on your iPad or Mac. Here are ten of the more interesting and useful functions you can use with Excel. AVERAGE for averaging data Might as well start with an easy one. The AVERAGE function averages the values in a cell range. Here, AVERAGE is used to compute the average rainfall in a three‐month period in three different counties. Use AVERAGE as follows: AVERAGE(cell range) Excel ignores empty cells and logical values in the cell range; cells with 0 are computed. The COUNT (above) and COUNTIF (below) function at work.

The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. For example, you can enter the following formula to count the numbers in the range A1:A20: =COUNT(A1:A20).

COUNT(C5:C9) Use COUNT as follows: COUNT(cell range) Similar to COUNT is the COUNTIF function. It counts how many cells in a cell range have a specific value. To use COUNTIF, enter the cell range and a criterion in the argument, as follows.

If the criterion is a text value, enclose it in quotation marks. COUNTIF(cell range, criterion) At the bottom of the spreadsheet, the formula determines how many of the mountains in the data are in Nepal: =COUNTIF(D5:D9,'Nepal') CONCATENATE for combining values CONCATENATE, a text function, is useful for combining values from different cells into a single cell.

Use CONCATENATE as follows: CONCATENATE(text1,text2,text3..) To include blank spaces in the text you’re combining, enclose a blank space between quotation marks as an argument. Onenote cannot create new notebook. Moreover, you can include original text in the concatenation formula as long as you enclose it in quotation marks and enter it as a separate argument.

Use the CONCATENATE function to combine values from cells. =CONCATENATE(C3,' ',D3,'.' ,' ',B3) =CONCATENATE(C11,' ',D11,'.'

,' ',B11,' ','lives in',' ',E11,'.' ) PMT for calculating how much you can borrow Use the PMT (payment) function to explore how much you can borrow given different interest rates and different amounts. PMT determines how much you have to pay annually on different loans. After you determine how much you have to pay annually, you can divide this amount by 12 to see how much you have to pay monthly.

Use the PMT function as follows to determine how much you pay annually for a loan: PMT(interest rate, number of payments, amount of loan) Set up a worksheet with five columns to explore loan scenarios: • Interest rate (column A • No. Of payments (column B) • Amount of loan (column C) • Annual payment (column D) • Monthly payment (column E). IF(logical true-false test, value if true, value if false) Instructing Excel to enter a value if the logical true‐false test comes up false is optional; you must supply a value to enter if the test is true. Enclose the value in quotation marks if it is a text value such as the word Yes or No. The formula for determining whether a team made the playoffs is as follows: =IF(C3>6,'Yes','No') If the false “No” value was absent from the formula, teams that didn’t make the playoffs would not show a value in the Playoffs column; these teams’ Playoffs column would be empty. Exploring loan scenarios with the PMT function. LEFT, MID, and RIGHT for cleaning up data Sometimes when you import data from another software application, especially if it’s a database application, the data arrives with unneeded characters.

You can use the LEFT, MID, RIGHT, and TRIM functions to remove these characters: • LEFT returns the leftmost characters in a cell to the number of characters you specify. For example, in a cell with CA_State, this formula returns CA, the two leftmost characters in the text: =LEFT(A1,2) • MID returns the middle characters in a cell starting at a position you specify to the number of characters you specify. For example, in a cell with this formula uses MID to remove the extraneous seven characters at the beginning of the URL and get www.dummies.com: =MID(A1,7,50) • RIGHT returns the rightmost characters in a cell to the number of characters you specify. For example, in a cell containing the words Vitamin B1, the following formula returns B1, the two rightmost characters in the name of the vitamin: =RIGHT(A1,2) • TRIM, except for single spaces between words, removes all blank spaces from inside a cell. Use TRIM to remove leading and trailing spaces.

This formula removes unwanted spaces from the data in cell A1: =TRIM(A1) PROPER for capitalizing words The PROPER function makes the first letter of each word in a cell uppercase. As are LEFT and RIGHT, it is useful for cleaning up data you imported from elsewhere. Use PROPER as follows: PROPER(cell address) LARGE and SMALL for comparing values Use the LARGE and SMALL functions, as well as their cousins MIN, MAX, and RANK, to find out where a value stands in a list of values. For example, use LARGE to locate the ninth oldest man in a list, or MAX to find the oldest man. Use MIN to find the smallest city by population in a list, or SMALL to find the fourth smallest.