Formula Guidance needed

[PrashanthGM12345]

Hi Gurus,

I am trying to do the following task with the help of excel formulas and struck very badly.

Quarter 1 = Jan, Feb, Mar
Quarter 2 = Apr, May, Jun
Quarter 3 = Jul, Aug, sep
Quarter 4 = Oct, Nov, Dec

I have 5 Tables Table A, B, C, D, E.
Table A, B, C , D has 4 Columns : Quarter, Audit Date, Opportunity ID and Audit Score
Table E has 3 Columns : Audit Months like Jan, Feb, March,.... Dec, Quarters like Quarter1, Quarter2, Quarter3, Quarter4 and Average Audit Score for Quarter.

I want a formula to do the following scenario.
It should scan the Quarter columns (A,F,K,P) of Table A,B,C,D and calculate the average ok audit score Columns (D,I,N,S) of Quarter 1 and copy the average audit score of Quarter 1 in the Cell W4.
similarly it should do for all the remaining Quarters in column A,F,K,P until Quarter 4 and update the average score in the corresponding cell in Table E.

Note: While calculating Average it should not do
((Average of Table A audit score + Average of Table B audit score + Average of Table C audit score + Average of Table D audit score) AND finally take the average again of all the 4 averages.) since AVERAGE OF AVERAGE is a wrong Approach.
So first for the month of jan, it should take the (Sum of Table A audit score for Quarter 1 + sum of table B Audit score for Quarter 1 + Sum of Table C audit score for Quarter 1 + Sum of Table D audit score for Quarter 1) and then take the average for Quarter 1.
Similarly it should do for all the other Quarters.

I have attached the excel sheet for your reference.

 

[bosco_yip]

Perhaps......

In W4, copied down :

=IFERROR(AVERAGEIF($A$3:$P$52,$V4,$D$3:$S$52),"")

Regards
Bosco

 

[PrashanthGM12345]

Thank you so much. Works perfectly