Knowledge Base Articles By Category
- This function takes a series of letters and returns the average of them, i.e., the average of A, B, C is B.
Examples of where this function shines:
- The only application that I can see for this formula is for averaging grades. If you know another, please click here and send me an email to let me know!
Formula Weakness(es):
- It only works on a single contiguous range on a worksheet, not multiple ranges.
- Must remember to enter as an array formula.
- Only operates on the first character in each cell.
Versions Tested:
This formula has been tested with Excel 2003, but will work with Excel 97 or higher.
The Formula (array entered):
{=CHAR(ROUND(AVERAGE(IF(ISTEXT(A1:B1),CODE(UPPER(A1:B1)),"")),0))}
- In order to use this formula, copy it to your worksheet, without the curly braces, change the two references to the range (A1:B1) to your range, and confirm the formula with CTRL+SHFT+Enter to array-enter it.
How it works (from the inside out):
- The IF() function contains three parts:
- The ISTEXT() function ensures that only text characters are passed to the function. If the item is blank or numeric, it is ignored (replaced with a "").
- The CODE() function converts the submitted text character into its ANSI equivalent number.
- The UPPER() function is used to convert all submitted characters to upper case. Since upper- and lower-case characters are, in fact, different ("a" and "A" have different ANSI values), they cannot be compared to each other properly unless they are all of a consistent case.
- The AVERAGE() function computes the average of the results passed to it by the aforementioned IF function.
- The ROUND() function rounds the result of the AVERAGE function to no decimal places.
- The CHAR() function converts the rounded number into a letter.
See For Yourself!
- Try changing the letters in the Office WebApp below. You'll see that the formulas update to show you the new averages.
Recent Forum Posts
vBulletin Message