How to set up a formula using the average and if functions

[penguinT]

I am trying to do a simple formula where I want to average a row of percentages without it counting the zeros. I thought that if I had it AVERAGEIF all numbers were greater than 0 it would work. For some reason, I cannot make it work for me. Some of the cells in the row are linked to other worksheets which fills the cell with text data. Does this have something to do with it not working? I get either a NAME error or VALUE error. I have tried turning off the zero in the options menu; that doesn't help either. Please help. Thank you in advance. penguinT

 

[Mark W.]

Book2
	A	B	C	D	E	F	G	H	I
1	4		3.571429
2	0
3	4
4	2
5	5
6	4
7	1
8	0
9	5
10	0
11
12
Sheet1

 

[Sir Vili]

Hello penguinT.

Lets us assume, that Your data is on area B2:B9. Use Formula:

=AVERAGE(IF(B2:B9<>0,B2:B9))

When You write this formula, do not push Enter, but instead CTRL + SHIFT + ENTER. Excel adds in beginning { and at end }. So You need ARRAY Formula.

Hopes this helps You.

BRGDS Sir Vili.

 

[Aladin Akyurek]

Let A1:A10 be the range of interest.

The following ordinary formula


=SUM(A1:A10)/MAX(1,COUNT(A1:A10)-COUNTIF(A1:A10,0))

will also give you the desired average.

 

[Mark W.]

On 2002-09-19 09:37, Sir Vili wrote:
...Use Formula:

=AVERAGE(IF(B2:B9<>0,B2:B9))

Since the IF worksheet functions treats 0 values as FALSE there's no need to compare each value to 0.