Average difference between two column values without helper column...

[HURTMYPONY]

I am trying to get an overall average of the difference between two columns (D and E) with date values with two criteria...

I will be unable to build a helper column, so it has to be all-in-one, unfortunately.

1. The value in C must match a value Cell A1.
2. The value in E must not be blank.

This array works until it encounters a cell with blank value in E (hence my second criterion):

<code>
=AVERAGE(IF(C:C=$A$1,E:E-D:D))
</code>

I cannot figure out how to get it to consider a second criterion.

Can someone show me how to add a second criterion or offer a different solution?

Thank you so much!

 

[AhoyNC]

Try:
Enter as an array with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F	G
1	1/8/2018		1/8/2018	12	24
2			1/8/2018	20	27		7.667
3			1/8/2018	5
4			1/10/2018	45	66
5			1/8/2018	8	12
6			1/10/2018	30	37
7
Sheet

 

[sgroath]

how about this (non-array):

=(SUMIFS(E:E,C:C,A1,E:E,">0")-SUMIFS(D:D,C:C,A1,E:E,">0"))/COUNTIFS(C:C,A1,E:E,">0")

 

[HURTMYPONY]

Try:
Enter as an array with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F	G
1	1/8/2018		1/8/2018	12	24
2			1/8/2018	20	27		7.667
3			1/8/2018	5
4			1/10/2018	45	66
5			1/8/2018	8	12
6			1/10/2018	30	37
7
Sheet

It appears to be working perfectly. Thank you so much!

 

[AhoyNC]

You're welcome. One note, it is best not to reference whole columns in your formulas (can slow down calculations if you have a number of formulas in your worksheet). It is usually better to only reference cells with data or a few rows past if you are going to be adding to the range.