Average of difference between dates greater than zero

  • Thread starter Thread starter juneturtle
  • Start date Start date
J

juneturtle

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-O9>0,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-O9>0),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.
 
My apologies, change second formula to:
{=AVERAGE(IF(ISNUMBER(O12:O20)*(O12:O20-O9>0),O12:O20-O9,FALSE))}

Because its an array, need to multiple criteria, not evaluate independently.
 
Luke, thanks for your swift response. It seems like your formulas could work
but I wasn't using a range before because there are different dates in the
odd numbered cells. Do you have any other suggestions?
 
Try this:

=AVERAGE(IF({TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE}*(O12:O20-O9>0),O12:O20-O9,FALSE))

We'll just have to manually FORCE the formula to ignore those rows!!
 
=AVERAGE(IF({TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRUE}*(O12:O20-O9>0),O12:O20-O9,FALSE))

Are you sure that's doing what you think it's doing? The array of Booleans
should be a *vertical* array. As you have it, it's a horizontal array and is
not calculating the way you think.

A vertical array is delimited by semi-colons:

{TRUE;FALSE;TRUE}

Try it like this:

=AVERAGE(IF((MOD(ROW(O12:O20),2)=0)*(O12:O20-O9>0),O12:O20-O9))

If you want it to be more robust against row insertions:

=AVERAGE(IF((MOD(ROW(O12:O20)-ROW(O12),2)=0)*(O12:O20-O9>0),O12:O20-O9))

All formulas are array formulas.
 
Hello Biff,
...
If you want it to be more robust against row insertions:

=AVERAGE(IF((MOD(ROW(O12:O20)-ROW(O12),2)=0)*(O12:O20-O9>0),O12:O20-O9))
...
Click to expand...

Insert a row after row 12 or before 20 :-)

SCNR,
Bernd
 
Insert a row after row 12 or before 20 :-)

Yeah, I said "more robust" not bulletproof robust! <g>
 
Back
Top