AverageIF Formula Error

[elax29]

Hi All,

Below is a sample of the information that I'm trying to evaluate:

Sheet 1 - Weekly Breakdown of Employees by Department
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]W/B[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]6Jun[/TD]
[TD]13Jun[/TD]
[TD]20Jun[/TD]
[TD]27Jun[/TD]
[TD]4Jul[/TD]
[TD]11Jul[/TD]
[TD]18Jul[/TD]
[TD]25Jul[/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]1Jun[/TD]
[TD]1Jun[/TD]
[TD]1Jun[/TD]
[TD]1Jun[/TD]
[TD]1Jul[/TD]
[TD]1Jul[/TD]
[TD]1Jul[/TD]
[TD]1Jul[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]HR[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD]Finance[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD]IT[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 - Average Monthly Breakdown of Employees by Department
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]should be[/TD]
[TD]should be[/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1Jun[/TD]
[TD]1Jul[/TD]
[TD]1Jun[/TD]
[TD]1Jul[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]HR[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]2.25[/TD]
[TD]3.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]Finance[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD]IT[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1.25[/TD]
[TD]1.75[/TD]
[/TR]
</tbody>[/TABLE]

I am trying to show the average employee count per department. I have tried using the following formula: AVERAGEIF('Sheet1'!$A$3:$A$5,$A2,INDEX('Sheet1'!$B$3:$I$5,0,MATCH(B$1,'Sheet1'!$B$2:$I$2,0)))
But it gives me the above incorrect averages (as shown above). What the averages actually should be is also shown above. It seems to just take the first week or the month value instead of averaging all of the weeks that belong in the month.

Any help would be greatly appreciated.

Thanks in advance!

 

[jtakw]

Hi,

If your data is indeed set up like your sample above, may be you're over complicating the situation, try this:

Excel 2010
	A	B	C	D	E	F	G	H	I
1		6-Jun	13-Jun	20-Jun	27-Jun	4-Jul	11-Jul	18-Jul	25-Jul
2		1-Jun	1-Jun	1-Jun	1-Jun	1-Jul	1-Jul	1-Jul	1-Jul
3	HR	1	2	3	3	3	3	4	4
4	Finance	2	2	4	4	4	4	4	4
5	IT	1	1	1	2	2	2	2	1
Sheet1

Excel 2010
	A	B	C
1		1-Jun	1-Jul
2	HR	2.25	3.5
3	Finance	3	4
4	IT	1.25	1.75
Sheet2

Cell Formulas
Range	Formula
B2	=AVERAGEIF(Sheet1!$B$2:$I$2,B$1,Sheet1!$B3:$I3)

Sheet 2 B2 formula copied down and across.

 

[elax29]

Thanks jtakw, I tried to simplify the data for the question that I posted on the forum.

My actual data has many different departments and weekly dates that extend into Dec 2018. As the dates pass, they are removed from the spreadsheet. Departments can be added at any time and moved into a different order also, hence me trying to make the formula dynamic and not lock in to a specific line...

I think what I need is some type of AverageIFs formula that matches the department name and the Month date from Sheet 2 (averages sheet) to Sheet 1 (weekly sheet) and then averages the specific cells that satisfy both criteria. The problem is that departments are vertically arranged and months/weeks are horizontally arranged and the ranges aren't the same size...

 

[jtakw]

Ok, if you wanted to stick with an AVERAGEIF(S) formula, it's going to end up being an array formula.

Here's my suggested solution using SUMPRODUCT which can handle arrays without being an array entered formula,
I've swapped "IT" and "FINANCE" on sheet 1 because you said the order of departments may not match from Sheet1 to Sheet2:

Excel 2010
	A	B	C	D	E	F	G	H	I
1		6-Jun	13-Jun	20-Jun	27-Jun	4-Jul	11-Jul	18-Jul	25-Jul
2		1-Jun	1-Jun	1-Jun	1-Jun	1-Jul	1-Jul	1-Jul	1-Jul
3	HR	1	2	3	3	3	3	4	4
4	IT	1	1	1	2	2	2	2	1
5	Finance	2	2	4	4	4	4	4	4
Sheet1

Excel 2010
	A	B	C
1		1-Jun	1-Jul
2	HR	2.25	3.5
3	Finance	3	4
4	IT	1.25	1.75
Sheet2

Cell Formulas
Range	Formula
B2	=SUMPRODUCT((Sheet1!$A$3:$A$5=$A2)*(Sheet1!$B$2:$I$2=B$1)*(Sheet1!$B$3:$I$5))/COUNTIF(Sheet1!$B$2:$I$2,B$1)

Sheet2 B2 formula copied down and across.