Combining AverageIF & Ignoring Values Less Than 0

dnalsk

New Member
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

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Maybe

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

Try..

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

Might also try a non CSE entry

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

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

Perfect, both worked. Thank you very much!

You're welcome.

Replies
5
Views
354
Replies
5
Views
2K
Replies
0
Views
309
Replies
3
Views
832
Replies
0
Views
212

1,203,212
Messages
6,054,184
Members
444,707
Latest member
cahayagalax

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back