Results 1 to 4 of 4

Thread: Find the Average of a sum of numbers in a single cell

  1. #1

    Find the Average of a sum of numbers in a single cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I've searched for the answer to this, but I may not have the terminology right to get the answer.
    In Cell A1 I have a simple addition formula such as =8+3+5+2 which gives me the result of 18.
    What I want (say in Cell B1) now is to find the average of those numbers based on the number of arguments or inputs. In this case it would be four. I know the fourmula =A1/4 would give me the answer but,
    I want excel to recognize the number of inputs to perform the average, I do not want to have to specifically change the formula in cell B1 every time based on the number of inputs.

    Thanks

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    What version of Excel?


  3. #3
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    If Excel 2013, use FORMULATEXT

    e.g

    =A1/(LEN(FORMULATEXT(A1))-LEN(SUBSTITUTE(FORMULATEXT(A1),"+",""))+1)

    otherwise add a User Defined Function:

    Hit Alt+F11, then Insert|Module and paste:

    Code:
    Function FormulaString(cell As Range) As String
        FormulaString = cell.Formula
    End Function
    Then apply formula in sheet:

    =A1/(LEN(FormulaString(A1))-LEN(SUBSTITUTE(FormulaString(A1),"+",""))+1)


  4. #4
    Yes, 2013. Thank You so much. Your first formula works great, just what I needed. Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •