Average question

[E1 calling]

Hello,

I am looking for a formula which calculates the average of the values NOT in the parentheses, also values as - and #NA can be ignored.

So the formula should work like: =AVERAGE(3,1,1,1,27,5,6,21,48,4,1,38,2,6,4)=11.2

A1: 3
A2: 1
A3: 1
A4: 1
A5: 27 (-2)
A6: 5 (+9)
A7: -
A8: 6
A9: 21 (+4)
A10: -
A11: 48 (-2)
A12: 4
A13: -
A14: 1
A15: 38 (-8)
A16: 2
A17: 6
A18: #NA
A19: 4

Any takers for this formula? I have been trying to create formula with no result so far.

Many thanks in advance.

 

[grizle]

Assuming the common factor converting the oddballs to numbers is the presence of a space, in col B for each entry insert the formula

=IF(ISNUMBER(A2),A2,IF(ISERROR(LEFT(A2,FIND(" ",A2))),"",VALUE(LEFT(A2,FIND(" ",A2)))))

This will leave numbers unchanged, convert the start of text to a number and text without a space to blank. Now taking the average of this gives the correct answer since blanks are ignored.

 

[Yahya]

Hi try this

Excel Workbook
	A	B	C
1	3		11.2
2	1
3	1
4	1
5	27 (-2)
6	5 (+9)
7	-
8	6
9	21 (+4)
10	-
11	48 (-2)
12	4
13	-
14	1
15	38 (-8)
16	2
17	6
18	#NA
19	4
Sheet1

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[ankurshrestha]

Very much simple formula is::::
=SUM(E44:E62)/COUNT(E44:E62)

 

[Yahya]

Very much simple formula is::::
=SUM(E44:E62)/COUNT(E44:E62)

your formula same with Average formula and will not give the result
because it will ignore any cell contains () and will give Error if there is any error value

 

[E1 calling]

@Yahya: the formula is perfect. Many thanks for this, your help is much appreciated.

All others, thanks for taking time to reply.

 

[Yahya]

@Yahya: the formula is perfect. Many thanks for this, your help is much appreciated.

All others, thanks for taking time to reply.

you are welcome