• Formula To Average Letters

    Formula Purpose:
    • 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.

     

    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

     

    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

    Comments 1 Comment
    1. wcruz's Avatar
      wcruz -
      I can't get this to work over multiple sheets. Any ideas?
  • MVP Logo
  • Recent Forum Posts

    AliGW

    Ok, go easy on me - First post

    You need to Edit then Go Advanced....

    AliGW Today, 04:48 PM Go to last post
    Knowsenoughtobedangerous

    Ok, go easy on me - First post

    I'm trying to edit the title but not having much luck. I hovered over the title hoping a pencil would pop up. Boy do I feel silly. Can you tell me...

    Knowsenoughtobedangerous Today, 04:27 PM Go to last post
    p45cal

    Create a repeating set of IDs

    [Still practising on these old threads]
    I learnt about Table.FromRows today:

    Code:
    let
        RootDte= Date.From(Excel.CurrentWorkbook(){[Name="Root"]}[Content][Root
    ...

    p45cal Today, 10:42 AM Go to last post
    AliGW

    Ok, go easy on me - First post

    Welcome to the forum.

    One of the things that you need to do for us is provide useful thread titles so that members have an idea about...

    AliGW Today, 07:19 AM Go to last post
    farooq_bhatti

    Couting Unique value from the Filtered Table

    However I tried to use it with table refernce but can't get into it why it is not working perfectly with multiple filter

    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(tbl_Projects[COMPANY],ROWS(tbl_Projects[COMPANY])-ROW(tbl_Projects[COMPANY]),0,1)),MATCH(tbl_Projects[COMPANY],tbl_Projects[COMPANY],0)),ROWS(tbl_Projects[COMPANY])-ROW(tbl_Projects[COMPANY])+1),1))...

    farooq_bhatti Today, 07:15 AM Go to last post