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

HURTMYPONY

Board Regular
Joined
Oct 27, 2003
Messages
166
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!
 
Last edited:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Try:
Enter as an array with CTRL-SHIFT-ENTER.
Excel Workbook
ABCDEFG
11/8/20181/8/20181224
21/8/201820277.667
31/8/20185
41/10/20184566
51/8/2018812
61/10/20183037
7
Sheet
 
Upvote 0
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")
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top