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

bex

New member
Joined
Apr 14, 2014
Messages
2
Reaction score
0
Points
0
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
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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)
 

bex

New member
Joined
Apr 14, 2014
Messages
2
Reaction score
0
Points
0
Yes, 2013. Thank You so much. Your first formula works great, just what I needed. Thanks again.
 
Top