Combining AverageIF & Ignoring Values Less Than 0

[dnalsk]

Hi All,

I have a worksheet that contains the array formula:

=AVERAGE(IF((A:A>=J2)*(A:A<=J3),D:D))

This calculates the average number of days in column d between 2 dates (j3 & j2).

My question is there are a few negative numbers in column d that I want to ignore in the formula without physically deleting them from the worksheet.

Is there anyway to add this in to the above formula? i.e. include ">0" somewhere?

Thanks

 

[gaz_chops]

Maybe

=AVERAGE(IF((A:A>=J2)*(A:A<=J3)*(A:A>0),D:D))

 

[jim may]

Try..

=average(if((a:a>=j2)*(a:a<=j3)*(d:d>0),d:d))

 

[Jonmo1]

Might also try a non CSE entry

=AVERAGEIFS(D:D,D:D,">0",A:A,">="&J2,A:A,"<="&J3)

 

[gaz_chops]

Oops, Jim's is correct I put the wrong column!!

 

[dnalsk]

Perfect, both worked. Thank you very much!

 

[Jonmo1]

You're welcome.