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
MamarazziAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
Try this ARRAY formula

=AVERAGE(IF((A2=Sheet2!$A:$A)*(Sheet2!C:C=""),Sheet1!$B$10-Sheet2!$B:$B))
0
MamarazziAuthor Commented:
I just get zeros. Did you use my example file? If so can you out your formula in the file?
0
Saqib Husain, SyedEngineerCommented:
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
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

MamarazziAuthor 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
MamarazziAuthor Commented:
Ctrl+Shift+Enter!
0
Saqib Husain, SyedEngineerCommented:
Yes, sorry.
0
barry houdiniCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saqib Husain, SyedEngineerCommented:
Middle Stump!!!
0
MamarazziAuthor 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
Saqib Husain, SyedEngineerCommented:
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
MamarazziAuthor Commented:
I see :-)

thank you both.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.