Most everyone knows that an average is determined by adding all the values in a list, then dividing by the number of values listed; e.g., 4+5+3=12/3=4, which is the average. You can use the SUM function and add the division all in one formula, or you can just use the AVERAGE function. The syntax is: =AVERAGE(range).

1. Enter some numbers in column A. Enter the AVERAGE function at the bottom of the list: =AVERAGE(A4:A13) and note the answer (in our case) is 53. You can verify your answer with the SUM function; that is: =SUM(A4:A13/10) = 53.

2. Next enter some more numbers in column C but, this time, add some text to one cell, punctuation to another, and a space to another. Enter the same formula: =AVERAGE(A4:A15), and note the answer is 78. To verify, enter the SUM formula omitting the cells that contain non-numeric characters:

Cells that contain text, logical values, punctuation, or empty cells are disregarded; but cells with the zeros (as a number, but not as text) are included. A text zero would have an apostrophe in front of the zero, which you cannot see in the cell, but is visible in the Formula Bar.

IMPORTANT NOTE: If you’re importing huge databases from a mainframe or an outside, external source, sometimes the numbers export as text. How can you know if a number is really text? Generally, text is left-justified and numbers are right-justified but, because everyone formats their spreadsheets for aesthetics now, that method is unreliable. Another option is to scroll quickly through a long list of imported numbers and watch the Formula Bar. If you see apostrophes before any of the numbers, those entries are text. Last, look for the green triangle in the top left corner of the cell. Unless the previous owner of the spreadsheet instructed Excel to ignore this error, then the contents of the cell are text.

If the values are text, you must convert them to numbers immediately. To do this, move down to the first number in the list that’s actually text. Highlight the range of text that’s impersonating numbers. Right-click the yellow warning sign that’s left of the first text cell in the range. Click Convert to Number from the pop-up list, and it’s done.