Average(IF between two date ranges (when some are blank)?

lrusso

New Member
Hi again Everyone,

I have a datasheet (that is not created by me, and I cannot change it) with a column of proposal dates and a column of start dates. I am creating a report from this sheet.

I have a great Array that was given to me here that works very well to average the number of days between the dates, if the cell is not blank.

=AVERAGE(IF('Test Data'!\$C\$2:\$C\$65535-'Test Data'!\$S\$2:\$S\$65535>0,'Test Data'!\$C\$2:\$C\$65535-'Test Data'!\$S\$2:\$S\$65535))

My problem is that now I've found out some of the date cells in the datasheet may be blank. This throws my averages completely off. Is there a way to manipulate my array so it ignores blank cells in either column C or S? Meaning, that if there are blank dates in either of those columns, that it will skip that cell in its calculation?

I really appreciate any help. Let me know if you need more clarification

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

MrExcel MVP
Re: Average(IF between two date ranges (when some are blank

Hi again Everyone,

I have a datasheet (that is not created by me, and I cannot change it) with a column of proposal dates and a column of start dates. I am creating a report from this sheet.

I have a great Array that was given to me here that works very well to average the number of days between the dates, if the cell is not blank.

=AVERAGE(IF('Test Data'!\$C\$2:\$C\$65535-'Test Data'!\$S\$2:\$S\$65535>0,'Test Data'!\$C\$2:\$C\$65535-'Test Data'!\$S\$2:\$S\$65535))

My problem is that now I've found out some of the date cells in the datasheet may be blank. This throws my averages completely off. Is there a way to manipulate my array so it ignores blank cells in either column C or S? Meaning, that if there are blank dates in either of those columns, that it will skip that cell in its calculation?

I really appreciate any help. Let me know if you need more clarification

Code:
``````=AVERAGE(
IF(ISNUMBER('Test Data'!\$C\$2:\$C\$65535),
IF('Test Data'!\$S\$2:\$S\$65535),
IF('Test Data'!\$C\$2:\$C\$65535-'Test Data'!\$S\$2:\$S\$65535>0,
'Test Data'!\$C\$2:\$C\$65535-'Test Data'!\$S\$2:\$S\$65535))))``````

lrusso

New Member
I think this worked I'm still testing it out but thank you!

Replies
1
Views
98
Replies
3
Views
107
Replies
23
Views
224
Replies
5
Views
225
Replies
4
Views
33

1,191,189
Messages
5,985,198
Members
439,947
Latest member
fabiannic

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