excel - Average only the numbers part of cells -
i trying average cells, contain both numbers , characters. excel reads these text doesn't give average of numbers.
what can use make formula @ numbers part of cell? tried combination of sum , left hasn't worked. need array formula perhaps (something involving ={sum(left(a1:c1))});
for example
cell a1 b1 c1 grade 6a 8b 4c
the answer should (6+8+4)/3=6
if there 1 , 1 letter after number create second row strips off follows:
cell a2: =1*mid(a1,1,len(a1)-1)
important have 1* part of formula creates number. can average row.
alternatively use array formula:
=average(1*mid(a1:c1,1,len(a1:c1)-1))
remember hit ctrl+shift enter when done editing cell. excel put {} around function in editing window show it's array formula.
Comments
Post a Comment