Averaging results of SUMIF

[greyscale]

Hi,

I have an Excel 2010 document that gives the average occupancy of each agent over a given number of weeks. I am using the following calculation

=AVERAGE(SUMIF(A$2:A$200,L3,B$2:B$200),SUMIF(D$2:D$200,L3,E$2:E$200),SUMIF(H$2:H$200,L3,I$2:I$200),SUMIF(L$2:L$200,L3,M$2:M$200))

This seems to work well if each agent is in every week. When the agent does not appear in one of the weeks, it still divides by 4 instead of the actual number of weeks they were here (e.g. 3). Agent 5 in the attached has an average 4 week occupancy of 60 = (90+72+76+0)/4 when it should be 79 = (90+72+76)/3. Is anyone able to advise of a better calculation I could use?

thanks in advance

[Ron Coderre]

Using your posted workbook...
This ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)
Returns the average Occupancy for the DA Name

Please Login or Register  to view this content.

Copy G3 and paste into G4:G15
Copy G2:G15
...Paste into K3:K15
...Paste into O3:O14
...Paste into W3:W14

Is that something you can work with?

[greyscale]

Thank you, Ron. I have to admit that I don't understand it but I think it will work for us. Can I ask 3 questions?

1 - From column W onwards, I only need to measure the previous 4 weeks. would I therefore change the $A reference to D as in the following?
{=IF(T3<>"",SUM(IF(D$3:T$15=T3,$B$3:U$15))/COUNTIF(D$3:T$15,T3),"")}. Then when I paste in each new column it will only look back at previous 4 weeks?

2 - If I copy the ARRAY {} in ahead of time into blank columns, will it update correctly when data is pasted in the next week or will I need to CTRL+SHIFT+ENTER again?

3 - Is it too much trouble to ask how the D<>"" works in the calc?

Thanks

[Ron Coderre]

1 - From column W onwards, I only need to measure the previous 4 weeks. would I therefore change the $A reference to D as in the following?
{=IF(T3<>"",SUM(IF(D$3:T$15=T3,$B$3:U$15))/COUNTIF(D$3:T$15,T3),"")}. Then when I paste in each new column it will only look back at previous 4 weeks?
• Yes, that's probably the least complicated way to approach the 4-week-limit

2 - If I copy the ARRAY {} in ahead of time into blank columns, will it update correctly when data is pasted in the next week or will I need to CTRL+SHIFT+ENTER again?
• Yes...as long as Excel has put the braces around the formula.

3 - Is it too much trouble to ask how the D<>"" works in the calc
• the <>"" is a quick way to check if a cell is not blank.

[greyscale]

Thanks Ron. I had a bit of trouble converting it to my live document which is a little different to the sample but all seems to be working now. thanks for your help