Average of difference between dates greater than zero
 

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

=)

Average of difference between dates greater than zero
 

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

=)

Average of difference between dates greater than zero
 

Try this:

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

We'll just have to manually FORCE the formula to ignore those rows!!
--
Best Regards,

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


"juneturtle" wrote:

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?

"Luke M" wrote:

My apologies, change second formula to:
{=AVERAGE(IF(ISNUMBER(O12:O20)*(O12:O20-O90),O12:O20-O9,FALSE))}

Because its an array, need to multiple criteria, not evaluate independently.
--
Best Regards,

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


"Luke M" wrote:

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

=)

Average of difference between dates greater than zero
 

Wow, it worked! Thank you so much...you were a great help. I'm sure I'll be
on here in the future for more mind-boggling excel questions!

"Luke M" wrote:

Try this:

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

We'll just have to manually FORCE the formula to ignore those rows!!
--
Best Regards,

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


"juneturtle" wrote:

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?

"Luke M" wrote:

My apologies, change second formula to:
{=AVERAGE(IF(ISNUMBER(O12:O20)*(O12:O20-O90),O12:O20-O9,FALSE))}

Because its an array, need to multiple criteria, not evaluate independently.
--
Best Regards,

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


"Luke M" wrote:

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

=)

Average of difference between dates greater than zero
 

=AVERAGE(IF({TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,TRU E,FALSE,TRUE}*(O12:O20-O90),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-O90),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-O90),O12:O20-O9))

All formulas are array formulas.


--
Biff
Microsoft Excel MVP


"Luke M" wrote in message
...
Try this:

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

We'll just have to manually FORCE the formula to ignore those rows!!
--
Best Regards,

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


"juneturtle" wrote:

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?

"Luke M" wrote:

My apologies, change second formula to:
{=AVERAGE(IF(ISNUMBER(O12:O20)*(O12:O20-O90),O12:O20-O9,FALSE))}

Because its an array, need to multiple criteria, not evaluate
independently.
--
Best Regards,

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


"Luke M" wrote:

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

=)

Average of difference between dates greater than zero
 

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-O90),O12:O20-O9))
...

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

SCNR,
Bernd

Average of difference between dates greater than zero
 

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

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

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
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-O90),O12:O20-O9))
...

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

SCNR,
Bernd