How to Use LEN, AVERAGE, and IF Functions in Excel – Tutorial

If you prefer video, check out the youtube tutorial Download the file to follow along: Download

Step 1: Calculate the Length of Each Capital Name Using LEN

To figure out which capital city names are longer than the average, the first step is to calculate the length of each city name.

1. Click into the cell next to the city name: G2
2. In cell G2 type the following formula to calculate the length of the city name:

   =LEN(F2)
   

   This will calculate the length of the text string in cell F2 (the city name).
3. Hit Enter to apply the formula.
4. Drag the fill handle (small square in the bottom-right corner of the selected cell) down the column to calculate the length of all city names.

The LEN function gives you the length (number of characters) in each capital name.

Step 2: Calculate the Average Length Using AVERAGE

Next, we’ll calculate the average length of the capital city names.

1. Select a blank cell where you want the average to appear - I’m randomly picking cell J2
2. Type the following formula:

   =AVERAGE(G:G)
   

   In this example, G:G refers to the range where the lengths of the city names are located.
3. Hit Enter to calculate the average length.

The AVERAGE function returns the average number of characters in the city names.

Step 3: Use IF to Determine Which Names Are Longer or Shorter Than Average

Now that we know the length of each city name and the average, we’ll use an IF statement to classify whether each capital name is longer or shorter than the average.

1. Click on the first cell where you want to apply the IF formula (e.g., H2).
2. Type the following formula:

   =IF(G2>$J$2, "Long", "Short")
   

   In this formula:
   • G2 is the length of the city name.
   • $J$2 is the cell containing the average length (the dollar signs lock the reference to this cell so it doesn’t move when copied).
   • If the length is greater than the average, it will return Long, otherwise, it will return Short.
3. Hit Enter.
4. Drag the fill handle down the column to apply the formula to the other rows.

Now, each row will return either "Long" or "Short" based on the comparison with the average.

Step 4: Fixing Relative References in the IF Statement

If you followed allong you should be fine, but if you find that the formula isn’t working as expected, it's likely because of relative references. As you copy the formula down, Excel adjusts the cell references unless you lock them. Here’s how you can avoid this problem:

1. Ensure that you use absolute referencing for the cell containing the average value by placing dollar signs ($) before the column and row references, like this:

   $N$2
   

   This locks the reference so that every comparison will be made against the correct average value.

2. After making this adjustment, check if the IF statement now works correctly across all rows.

Summary of Formulas Used

• LEN function: Calculates the number of characters in a string.

  =LEN(F2)
  

• AVERAGE function: Calculates the average of a range of numbers.

  =AVERAGE(G:G)
  

• IF function: Performs a logical test and returns one value if true and another if false.

  =IF(G2>$J$2, "Long", "Short")
  

Conclusion

By using a combination of LEN, AVERAGE, and IF functions, you can easily analyze text data and compare values in Excel. These are powerful tools that allow you to perform logical tests and make comparisons based on custom conditions. Check out the video tutorial linked below for a step-by-step demonstration of how to apply these formulas in Excel.