# Calculate average of days interval

I need to get the average of a sum of interval of days. See example!

The formula I need help with is in Sheet1 col C. The others are fine. I think :-)

The only way I could get the average was by adding col D in Sheet 2. But I would like to avoid that.
excel-avg.xlsx
###### Who is Participating?

Commented:
You don't really need an array formula for this......

Just use an AVERAGEIFS function like you already have but with the average of the dates, rather than the number of days - then deduct that result from B10 and you'll get the average days as required, i.e. using this formula

=B\$10-AVERAGEIFS(Sheet2!\$B:\$B,Sheet2!\$A:\$A,A2,Sheet2!\$C:\$C,"<>1")

regards, barry
0

EngineerCommented:
Try this ARRAY formula

=AVERAGE(IF((A2=Sheet2!\$A:\$A)*(Sheet2!C:C=""),Sheet1!\$B\$10-Sheet2!\$B:\$B))
0

Author Commented:
I just get zeros. Did you use my example file? If so can you out your formula in the file?
0

EngineerCommented:
Did you enter the formula as an ARRAY formula or as a normal formula?

If you did it as a normal formula then
Select the first formula cell (with the formula entered)
press F2
press Ctrl-Alt-Enter
Now copy this formula down

here is the formula applied.
excel-avg-2.xlsx
0

Author Commented:
Aha! :-)

Array formulas are new to me. But now it works fine apart from that the command  Ctrl-Alt-Enter doesn't do anything. I can see though that the only difference are the curly brackets... or?
0

Author Commented:
Ctrl+Shift+Enter!
0

EngineerCommented:
Yes, sorry.
0

EngineerCommented:
Middle Stump!!!
0

Author Commented:
@Barry: Yes that works also and will be my choice since someone else is maintaining this formula and arrays might be too complicated for them.

@ssaqibh: What do you mean? :-)
0

EngineerCommented:
I mean that Barry's formula was a beauty and clean-bowled mine on the "Middle wicket" ;)

http://sarahcanterbury.files.wordpress.com/2011/05/darren-stevens-bowled-v-northants-home-cc-2011-day-4-1.jpg
0

Author Commented:
I see :-)

thank you both.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.