I'm having an issue trying to get this formula to work. I have a due date
which is when documents are supposed to be turned in and then their actual
dates - when the docs were actually turned in.

I need to know the difference between these two dates and if the difference
is greater than zero (meaning they turned it in prior to the due date or it
still hasn't been turned in - the cell is blank), the average of those
differences.

The current formula I'm using is:
=AVERAGE(IF((O12-O9)0,(O12-O9)),IF((O14-O9)0,(O14-O9)),IF((O16-O9)0,(O16-O9)),IF((O18-O9)0,(O18-O9)),IF((O20-O9)0,(O20-O9)))

It returns the average of all difference in dates, including the zeros. I
tried making it an array formula like the following one, so the zeros could
actually be null and then they wouldn't be averaged but that doesn't work
either:

{=AVERAGE(IF((N12-N9)0,(N12-N9),""),IF((N14-N9)0,(N14-N9),""),IF((N16-N9)0,(N16-N9),""),IF((N18-N9)0,(N18-N9),""),IF((N20-N9)0,(N20-N9),""))}

Could someone please help me out with this, I'd greatly appreciate it

=)

Because your breaking the numbers up, the Average function is treating the
FALSE values as 0. Can you simply combine the functions into:
{=AVERAGE(IF(O12:O20-O90,O12:O20-O9,FALSE))}

If you have text values or something in between that you were trying to
ignore, you might be able to just eliminate them within the IF function like
so:
{=AVERAGE(IF(AND(ISNUMBER(O12:O20),O12:O20-O90),O12:O20-O9,FALSE))}

This would ignore all text values on rows 13, 15, 17, 19.

Also, I presume you know these are array functions, but for future readers,
these formulas should be entered without the curly brackets, and confirmed
using Ctrl+Shift+Enter.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"juneturtle" wrote:

I'm having an issue trying to get this formula to work. I have a due date
which is when documents are supposed to be turned in and then their actual
dates - when the docs were actually turned in.

I need to know the difference between these two dates and if the difference
is greater than zero (meaning they turned it in prior to the due date or it
still hasn't been turned in - the cell is blank), the average of those
differences.

The current formula I'm using is:
=AVERAGE(IF((O12-O9)0,(O12-O9)),IF((O14-O9)0,(O14-O9)),IF((O16-O9)0,(O16-O9)),IF((O18-O9)0,(O18-O9)),IF((O20-O9)0,(O20-O9)))

It returns the average of all difference in dates, including the zeros. I
tried making it an array formula like the following one, so the zeros could
actually be null and then they wouldn't be averaged but that doesn't work
either:

{=AVERAGE(IF((N12-N9)0,(N12-N9),""),IF((N14-N9)0,(N14-N9),""),IF((N16-N9)0,(N16-N9),""),IF((N18-N9)0,(N18-N9),""),IF((N20-N9)0,(N20-N9),""))}

Could someone please help me out with this, I'd greatly appreciate it

=)