Knowledge Base Articles By Category
View Profile
View Forum Posts
View Blog Entries
Visit Homepage
View Articles
- This function takes a series of letters and returns the average of them, i.e., the average of A, B, C is B.
- 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!
- 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.
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.
- 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.
- Try changing the letters in the Office WebApp below. You'll see that the formulas update to show you the new averages.
Donations
Recent Forum Posts
