Average Without Zeroes
 

My spread sheet contains already some COUNTIF formula which are working
okay.

As soon as i try to change which cells are included in the formula so
they do not go in sequence i get the #VALUE! coming up

the one that works goes as follows

=SUM(AA200:AA205)/COUNTIF(AA200:AA205,"<0")

the one that doesnt is

=SUM(AA207:AA211,AA217)/COUNTIF((AA207:AA211,AA217),"<0")

Any suggestions?

Thanks

Tom

Average Without Zeroes
 

Tom:

the one that doesnt is

=SUM(AA207:AA211,AA217)/COUNTIF((AA207:AA211,AA217),"<0")


What are you trying to do? Use a condition in the sum? Than try:

=SUMIF(AA207:AA211,AA217,"<0")/COUNTIF((AA207:AA211,AA217),"<0")

I am not sure if I use correct syntax (I have spanish version). But I
believe your error is provoked in "sum(AA207:AA211,AA217)", which does not
seem to be posible.

Hope some else can help out too.

Martin

Average Without Zeroes
 

=SUM(AA207:AA211,AA217)/SUM(COUNTIF(AA207:AA211,"<0"),COUNTIF(AA217,"<0" ))


--
Regards,
Tom Ogilvy


"tom" wrote:

My spread sheet contains already some COUNTIF formula which are working
okay.

As soon as i try to change which cells are included in the formula so
they do not go in sequence i get the #VALUE! coming up

the one that works goes as follows

=SUM(AA200:AA205)/COUNTIF(AA200:AA205,"<0")

the one that doesnt is

=SUM(AA207:AA211,AA217)/COUNTIF((AA207:AA211,AA217),"<0")

Any suggestions?

Thanks

Tom

Average Without Zeroes
 

the suggested solution will not work.

Sumif and Countif will not work with discontiguous ranges as suggested.

--
Regards,
Tom Ogilvy



"Martin Los" wrote:

Tom:

the one that doesnt is

=SUM(AA207:AA211,AA217)/COUNTIF((AA207:AA211,AA217),"<0")


What are you trying to do? Use a condition in the sum? Than try:

=SUMIF(AA207:AA211,AA217,"<0")/COUNTIF((AA207:AA211,AA217),"<0")

I am not sure if I use correct syntax (I have spanish version). But I
believe your error is provoked in "sum(AA207:AA211,AA217)", which does not
seem to be posible.

Hope some else can help out too.

Martin

Average Without Zeroes
 

Thanks anyway

Can you think of any way to work around this as i cannot use the
inbetween field without alot of work due to the number of times this
calculation would be used.

Thanks

Tom

Average Without Zeroes
 

Possibly with

=Average(if(isnumer(AA207:AA1000)*(AA207:AA1000<0 ),AA207:AA1000))

Entered with Ctrl+shift+enter since this is an Array formula

If you want to average all numeric values not zero in your range (cells to
be skipped would be blank or contain text).

--
Regards,
Tom Ogilvy


"tom" wrote:

Thanks anyway

Can you think of any way to work around this as i cannot use the
inbetween field without alot of work due to the number of times this
calculation would be used.

Thanks

Tom

Average Without Zeroes
 

Great help

Thanks Very Much

Tom