10 Functions for Crunching Numbers with Excel on Your iPad or Mac

By Peter Weverka

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.

Using AVERAGE to find average rainfall data.’

Using AVERAGE to find average rainfall data.’

COUNT and COUNTIF for tabulating data items

Use COUNT, a statistical function, to count how many cells have data in them. Numbers and dates, not text entries, are counted. The COUNT function is useful for tabulating how many data items are in a range. In this spreadsheet, for example, COUNT is used to compute the number of mountains listed in the data:

The COUNT (above) and COUNTIF (below) function at work.

The COUNT (above) and COUNTIF (below) function at work.
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. 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.

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)

    Exploring loan scenarios with the PMT function.

    Exploring loan scenarios with the PMT function.

IF for identifying data

The IF function examines data and returns a value based on criteria you enter. Use the IF function to locate data that meets a certain threshold. In the worksheet shown, for example, the IF function is used to identify teams that are eligible for the playoffs. To be eligible, a team must have won more than six games.

Use the IF function as follows:

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.

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 https://www.dummies.com, 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. The RANK function finds the rank of a value in a list of values.

Use these functions as follows:

  • MIN returns the smallest value in a list of values. For the argument, enter a cell range or cell array. In the worksheet shown, the following formula finds the fewest number of fish caught at any lake on any day:

    =MIN(C3:G7)
  • SMALL returns the nth smallest value in a list of values. This function takes two arguments, first the cell range or cell array, and next the position, expressed as a number, from the smallest of all values in the range or array. In the worksheet shown, this formula finds the second smallest number of fish caught in any lake:

    =SMALL(C3:G7,2)
  • MAX returns the largest value in a list of values. Enter a cell range or cell array as the argument. In the worksheet shown, this formula finds the most number of fish caught in any lake:

    =MAX(C3:G7)
  • LARGE returns the nth largest value in a list of values. This function takes two arguments, first the cell range or cell array, and next the position, expressed as a number, from the largest of all values in the range or array. In the worksheet shown, this formula finds the second largest number of fish caught in any lake:

    =LARGE(C3:G7,2)
  • RANK returns the rank of a value in a list of values. This function takes three arguments:

    • The cell with the value used for ranking

    • The cell range or cell array with the comparison values for determining rank

    • Whether to rank in order from top to bottom (enter 0 for descending) or bottom to top (enter 1 for ascending)

    In the worksheet shown, this formula ranks the total number of fish caught in Lake Temescal against the total number of fish caught in all five lakes:

    Using functions to compare values.

    Using functions to compare values.
    =RANK(H3,H3:H7,0)

NETWORKDAY and TODAY for measuring time in days

Excel offers a couple of date functions for scheduling, project planning, and measuring time periods in days.

NETWORKDAYS measures the number of workdays between two dates (the function excludes Saturdays and Sundays from its calculations). Use this function for scheduling purposes to determine the number of workdays needed to complete a project. Use NETWORKDAYS as follows:

NETWORKDAYS(start date, end date)

TODAY gives you today’s date, whatever it happens to be. Use this function to compute today’s date in a formula.

TODAY()

To measure the number of days between two dates, use the minus operator and subtract the latest date from the earlier one.

="6/1/2015"-"1/1/2015"

The dates are enclosed in quotation marks to make Excel recognize them as dates. Make sure that the cell where the formula is located is formatted to show numbers, not dates.

LEN for counting characters in cells

Use the LEN (length) function to obtain the number of characters in a cell. This function is useful for making sure that characters remain under a certain limit. The LEN function counts blank spaces as well as characters. Use the LEN function as follows:

LEN(cell address)