calculating average from text cells

Hello excel pros!
I have a problem. I have to calculate average availability from 4 drop down menu choices. Choices are text cells. Each choice basically is equal to constant number.
Is it possible to calculate average w/o using additional column? (See excel attached)

P.S. Right now only possible solution that i can think of is adding that number in front of text in each cell and in additional column using LEFT function get that number and calculate avarage from them. Seems a bit too ugly solution...

Try:

Formula:

Please Login or Register  to view this content.

count is not fixed (users are adding new rows)

Convert the range to a Table

Add header values to A3 and B3
Highlight A3:B8
On the INSERT tab, click Table. Check My Table Has Headers - OK

Now your formula will auto update when rows are added/deleted.

If you don't want a Table, create a Dynamic Named Range or use INDEX to locate the last cell.

Remove the example output and use:

Formula:

Please Login or Register  to view this content.

something is not working with that formula

Gonna need to narrow it down a bit. "Not working" is not very specific or helpful.

	A	B	C	D	E	F	G
1	Situation right now:
2	#NAME?	1.20		availability		1.2
3				0 easy		=SUMPRODUCT(--(LEFT($A$4:INDEX($A:$A,COUNTA($A:$A)+1),1)))/COUNTA($A$4:INDEX($A:$A,COUNTA($A:$A)+1))
4	0 easy	0		1 ok
5	1 ok	1		2 hard
6	2 hard	2		3 impossible
7	3 impossible	3
8	0 easy	0
9
10			Would like to:
11
12			1.2			availabilty
13						easy
14			easy			ok
15			ok			hard
16			hard			impossible
17			impossible
18			easy
19

Guessing it's probably because you have Calculation set to manual in that workbook.

Thanks! It's working! What about situation when in the beginning of each drop down choice is no number?

i have to put it in column A

You're welcome. Thanks for the rep. I did say

Remove the example output and use:

The formula was NOT in column A, so it's not an issue. Though, agreed, it would be an issue if it was in column A. But, spelling AVERAGE as AVARAGE is an issue

It's taking the leftmost single character which, in your example, is numeric from 0 to 3. How do you plan to average a non numeric character?

i have to put it in column A

Thought you said it was working? If it's not, use a Dynamic Named Range ... that would be similar to the formula used already.