AVERAGE
 

I understand the basics of array formulas,and the following formula
=AVERAGE(IF(A2:A4<0, A2:A4,"")) which doesn't count zero's,but i want the
cells A2:A4 but also with A7 added as a seperate cell for the formula to use
in its calculations =AVERAGE(A2:A4,A7) something like this
=AVERAGE(IF(A2:A4,A7<0, A2:A4,A7,"")) although this doesn't work,does
anyone know how to write this the right way..........Help please...

AVERAGE
 

Done in a slightly different way.

=SUM(A2,A3,A4,A7)/COUNTIF(A2:A4,"0")+COUNTIF(A7,"0")

Mike

"sonicscooter" wrote:

I understand the basics of array formulas,and the following formula
=AVERAGE(IF(A2:A4<0, A2:A4,"")) which doesn't count zero's,but i want the
cells A2:A4 but also with A7 added as a seperate cell for the formula to use
in its calculations =AVERAGE(A2:A4,A7) something like this
=AVERAGE(IF(A2:A4,A7<0, A2:A4,A7,"")) although this doesn't work,does
anyone know how to write this the right way..........Help please...

AVERAGE
 

Sorry a set of brackets missing.

=SUM(A2,A3,A4,A7)/(COUNTIF(A2:A4,"0")+COUNTIF(A7,"0"))

Mike

"sonicscooter" wrote:

I understand the basics of array formulas,and the following formula
=AVERAGE(IF(A2:A4<0, A2:A4,"")) which doesn't count zero's,but i want the
cells A2:A4 but also with A7 added as a seperate cell for the formula to use
in its calculations =AVERAGE(A2:A4,A7) something like this
=AVERAGE(IF(A2:A4,A7<0, A2:A4,A7,"")) although this doesn't work,does
anyone know how to write this the right way..........Help please...

AVERAGE
 

Are A2:A4 always filled in with a number?

=SUM(A2:A4,A7)/(COUNTIF(A2:A4,"<"&0)+COUNT(A7))



sonicscooter wrote:

I understand the basics of array formulas,and the following formula
=AVERAGE(IF(A2:A4<0, A2:A4,"")) which doesn't count zero's,but i want the
cells A2:A4 but also with A7 added as a seperate cell for the formula to use
in its calculations =AVERAGE(A2:A4,A7) something like this
=AVERAGE(IF(A2:A4,A7<0, A2:A4,A7,"")) although this doesn't work,does
anyone know how to write this the right way..........Help please...

--

Dave Peterson

AVERAGE
 

Or if you could have text in those cells:

=SUM(A2:A4,A7)/(COUNTIF(A2:A4,"<"&0)+COUNTIF(A2:A4,""&0)
+(ISNUMBER(A7)*(A7<0)))

I missed the part that A7 should be excluded if =0 in the earlier post.



Dave Peterson wrote:

Are A2:A4 always filled in with a number?

=SUM(A2:A4,A7)/(COUNTIF(A2:A4,"<"&0)+COUNT(A7))

sonicscooter wrote:

I understand the basics of array formulas,and the following formula
=AVERAGE(IF(A2:A4<0, A2:A4,"")) which doesn't count zero's,but i want the
cells A2:A4 but also with A7 added as a seperate cell for the formula to use
in its calculations =AVERAGE(A2:A4,A7) something like this
=AVERAGE(IF(A2:A4,A7<0, A2:A4,A7,"")) although this doesn't work,does
anyone know how to write this the right way..........Help please...

--

Dave Peterson

--

Dave Peterson

AVERAGE
 

Hi,the list of cells is longer than that in my example,ie A1:A200 plus B2,the
reason for using an array formula was because some of the cells in either
A1:A200 or B2 may contain zero's.....i didn't want to count zero's as it
alters the outcome....Thanks Shane......England

"Dave Peterson" wrote:

Are A2:A4 always filled in with a number?

=SUM(A2:A4,A7)/(COUNTIF(A2:A4,"<"&0)+COUNT(A7))



sonicscooter wrote:

I understand the basics of array formulas,and the following formula
=AVERAGE(IF(A2:A4<0, A2:A4,"")) which doesn't count zero's,but i want the
cells A2:A4 but also with A7 added as a seperate cell for the formula to use
in its calculations =AVERAGE(A2:A4,A7) something like this
=AVERAGE(IF(A2:A4,A7<0, A2:A4,A7,"")) although this doesn't work,does
anyone know how to write this the right way..........Help please...

--

Dave Peterson

AVERAGE
 

It works fine,thanks very much....Shane.

"sonicscooter" wrote:

I understand the basics of array formulas,and the following formula
=AVERAGE(IF(A2:A4<0, A2:A4,"")) which doesn't count zero's,but i want the
cells A2:A4 but also with A7 added as a seperate cell for the formula to use
in its calculations =AVERAGE(A2:A4,A7) something like this
=AVERAGE(IF(A2:A4,A7<0, A2:A4,A7,"")) although this doesn't work,does
anyone know how to write this the right way..........Help please...