To calculate the Monthly Averages by formulas

[thilag]

Dear Friends

Please refer the attached excel file (File name : inout avearge.xlsx).

My requirement is i want to put the
1) Particular Month Average (Hours)
2) Upto that Month Average (Hours)

by formulas which is highlighted in Yello Color. Currently iam manually entering,Is it possbile to do this by using formulas.

thanks in advance

thilag

[benishiryo]

hi thilag. first thing off, make column J real dates instead of texts. type Nov 2013 in J4. format cells to:
mmmm yy

you uploaded an xlsx file, but your profile says MS off 2003. so which is it? if it's Excel 2007 & above, then this formula works faster:
=AVERAGEIFS(F:F,B:B,">="&J4,B:B,"<="&EOMONTH(J4,0))

in L4:
=AVERAGEIFS(F:F,B:B,"<="&EOMONTH(J4,0))

if you're still using Excel 2003 & below, then:

Please Login or Register  to view this content.

=SUMPRODUCT(($B$4:$B$20<=EOMONTH(J4,0))*$F$4:$F$20)/SUMPRODUCT(--($B$4:$B$20<=EOMONTH(J4,0)))

Edit: and if you need to round them, simply wrap any of the formulas with ROUND:
=ROUND(formula,2)

[thilag]

Hello benishiryo

Thanks to your reply. It help me to do the calcualtion automatiaclly. Also my Excel Version is 2010 in Office, in Home i believe 2003anyhow it will be greater than 2007. So i will update the profile to Excel 2007.

thanks

thilag