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

[lrusso]

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

 

[Aladin Akyurek]

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

=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]

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