Grouping Range of Weeks to Specific Month and find the average of the values

[Vinod Krishna.C]

Dear all,

My Goal is to find the average of allocation of resources for specific weeks and group them into specific month for dynamic range using formula.

Ex:

	A	B	C	D	E
Emp No	05/19/2018	05/26/2018	06/02/2018	06/09/2018	06/16/2018
1	25	75	0	0	100
2	0	0	25	25	25

In the end, i.e. after the last columns, output against 1 should be:

May-18	=AVERAGE(A2:B2)
Jun-18	=AVERAGE(C2:E2)

In the end, i.e. after the last columns, output against 2 should be:

May-18	=AVERAGE(A3:B3)[/
Jun-18	=AVERAGE(C3:E3)[/

But, it should be based on formula using range of column. Please see columns AL through AT.

PFA for the sheet with error.

Request your help in fixing this code and improvising the same.

Thanks,
Vinod Krishna

[David A Coop]

Try this formula - I think it was introduced in 2007.

=IFERROR(AVERAGEIFS($B2:$AK2,$B$1:$AK$1,">="&AL$1,$B$1:$AK$1,"<="&EOMONTH(AL$1,0)),0)

You will need to change the headings in AL1:AT1 to dates. They are entered as text in your version.

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[Vinod Krishna.C]

Thanks for the formula David. It works.

However, I want to achieve this using VBA.

Perhaps, my requirement was confusing. I had updated my original post.

Could you please help me correct the VBA logic.

Thanks,
Vinod Krishna

[David A Coop]

Hi Vinod,

Before I discuss the VBA, I should state that I usually prefer a formula solution - especially where you are using VBA to enter a formula.

That said, I have written a new macro for you. (It is below your old one).

It is considerably shorter than yours - I hope it is doing what you want it to.

I suggest you work your way through it, and see if you can follow what I have done.

Basically, I work my way across the dates in row 1, and every time the month changes, I place the month heading on row 1, and a formula on row two.
When this is finished, the macro then copies the formula down.

This is a lot quicker than your approach, which was slow.

Have a look at the formula! Your method was incorrect - you have to use the R1C1 formula structure.

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[David A Coop]

Hi Vinod,

Thanks for letting me know that the code I provided worked (and for the rep).

Please mark the thread as SOLVED so that other members of the forum don't spend time looking into your query.

David

[Vinod Krishna.C]

Sure David. It is marked as Solved.

Regards,
Vinod Krishna