average number of days between list of closeddate and opendate excluding blank cells

RosieGP welcome to the forum.

Try this with the following layout and this formula in D1 as below.

Formula:

Please Login or Register  to view this content.

	A	B	C	D	E
1	opendate	closedate*		7	1-Dec-16
2	1-Apr-16	3-Apr-16	2
3	10-Apr-16	11-Jun-16	31
4	02-May-16*
5	12-Dec-16	24-Dec-16	12
6	20-Dec-16	22-Dec-16	2
7	11-Dec-16	18-Jan-17
8	21-Dec-16*

Thanks for quick reply Dave. column C or E1 is not in the standard workbook2. I just added the days to display what the output should be.
The calculation needs to be done only on opendate and closedate:
sorry for any confusion.
I'm trying make the following work

Please Login or Register  to view this content.

I also tried the following:

Please Login or Register  to view this content.

Our edits crossed in the ether-mail.

I'm confused. I thought you wanted averages of values in column C that were returned by closedate-opendate conditional upon a monthly range.

opendate(Dec-16) and closedate(Dec-16) is a bit vague. We need to also know what the underlying days are in order to do the calendar math.

I'm missing something. Please tell me.

Better yet please upload a sample Excel workbook that illustrates what you are starting with and what you would like the results to be. Also indicate the logic you want us to apply in order to reach the solution. Think "BEFORE - AFTER". 10 to 20 rows is usually sufficient.

Please no pics or screenshots (saves having to retype your data). You often get more and faster response if you do.

If you are not familiar with how to do this:


To attach a file to your post,

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”
• be sure to desensitize the data

The file name will appear at the bottom of your reply.

I've reworked your upload with some suggested modification to expedite use and minimize the need for future edits in two cases.

There is a helper column in column D. It can be hidden if you wish. It streamlines calculations and simplifies formulas by first subtracting all openDates from all closeDates. The formula in D2 filled down is

Formula:

Please Login or Register  to view this content.

There is a data validation list of FOM dates 2016-2017 in column I. It is applied to a drop down in E1 for convenience.

There are three formulas all equally valid and all returning the same results.

In E2 this formula resembles what you were trying to do in the attempts Post #3. It must be array entered. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

The next two do not have to be array entered and take advantage of the IFs family of functions to reference whole columns without noticeable load. This makes "future proofing" the formulas almost unnecessary.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

You're welcome. Thanks for the feedback and rep.