Averages that take into account half days

[DukeRollo]

Hello.

I am looking for a formula that can average numbers while taking into account a variable number of instances.

Imagine you are working in an offices and you want to know your workers' average. John Doe works for you and makes widgets. John makes 30 widgets a day.

So most weeks, you'd have an average of 30 widgets over 5 days ( (30+30+30+30+30)/5). But If Joe works a half day on Friday you don't want to penalize him if he only makes 15 widgets.

You don't want ( (30+30+30+30+15)/5=27).

You want ( (30+30+30+30+15)/4.5=30).

I know I can use an AVERAGE formula to get a regular average. Is there a formula or setup you recommend that would allow me to take into account those half days?

Thank you!

[Glenn Kennedy]

You've told us nOTHING about your data layout... so any answer will almost certainly be wrong.

Here's my take...

=SUMPRODUCT(($A$2:$A$5=J2)*($B$2:$B$5="n")*$C$2:$G$5)/SUMPRODUCT(($A$2:$A$5=J2)*($B$2:$B$5="Hr")*$C$2:$G$5)

[DukeRollo]

Awesome. Thanks!

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.