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
 
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)
 
Yes, 2013. Thank You so much. Your first formula works great, just what I needed. Thanks again.
 
Back
Top