I would like to average an array of cells, but only include those cells whose
cell value is greater than zero.
I have an array like (D3;G3;J3), and want to include only those
AVERAGE(D3;G3;J3). I have tried to use
SUM(D3;G3;J3)/COUNTIF((D3;G3;J3);"0" )
but the COUNTIF doesn't allow that arrayform (D3;G3;J3). If I for example
put COUNTIF((D3:D7);"0") , that works - but that's not the cells where my
data is!
How should I write the array so that it works?
Yours
Karin

Hi,

Try this

=AVERAGE(IF(ISNUMBER(MATCH(COLUMN(A3:J3),{4,7,10}, 0))*(A3:J30),A3:J3))

'This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"Karin" wrote:

I would like to average an array of cells, but only include those cells whose
cell value is greater than zero.
I have an array like (D3;G3;J3), and want to include only those
AVERAGE(D3;G3;J3). I have tried to use
SUM(D3;G3;J3)/COUNTIF((D3;G3;J3);"0" )
but the COUNTIF doesn't allow that arrayform (D3;G3;J3). If I for example
put COUNTIF((D3:D7);"0") , that works - but that's not the cells where my
data is!
How should I write the array so that it works?
Yours
Karin

This will average non-zero cells: =SUM(D3,G3,J3)/SUM((D30)+(G30)+(J30))
Note that negative values will give an incorrect answer
This will average only positive values and ignore negative and zero values
=SUM(D3*(D30),G3*(G30),J3*(J30))/SUM((D30)+(G30)+(J30))
Convert commas (,) to semicolons (;) for you local version of Excel
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Karin" wrote in message
...
I would like to average an array of cells, but only include those cells
whose
cell value is greater than zero.
I have an array like (D3;G3;J3), and want to include only those
AVERAGE(D3;G3;J3). I have tried to use
SUM(D3;G3;J3)/COUNTIF((D3;G3;J3);"0" )
but the COUNTIF doesn't allow that arrayform (D3;G3;J3). If I for example
put COUNTIF((D3:D7);"0") , that works - but that's not the cells where my
data is!
How should I write the array so that it works?
Yours
Karin

I noticed that your cells are spaced equally, every third column. Thus
you can use the following *array* formula

=AVERAGE(IF((D3:Z30)*(MOD(COLUMN(D3:Z3),3)=1),D3: Z3)

Commit with Shift+Ctrl+Enter

HTH
Kostis Vezerides

On Nov 18, 2:47*pm, Karin wrote:
I would like to average an array of cells, but only include those cells whose
cell value is greater than zero.
I have an array like (D3;G3;J3), and want to include only those
AVERAGE(D3;G3;J3). I have tried to use
SUM(D3;G3;J3)/COUNTIF((D3;G3;J3);"0" )
but the COUNTIF doesn't allow that arrayform (D3;G3;J3). If I for example
put COUNTIF((D3:D7);"0") , that works - but that's not the cells where my
data is!
How should I write the array so that it works?
Yours
Karin

Thanks to all of you, I think all of your suggestions worked fine! Problem
solved!
Karin