weekdaty Average IFs formula

[Sparda142]

Hello,

I'm having trouble with the formula below, Im getting "Too few arguments" . Where am i going wrong with the formula below?


CELL H2 FORMULA =IFERROR(AVERAGEIFS(C2:C11,"<>100%", C1:E1,G2,A2:A11,H1)"CHECK")


[TABLE="class: grid, width: 125"]
<tbody>[TR]
[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]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]John[/TD]
[TD]Jacob[/TD]
[TD]Jill [/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]7/1[/TD]
[TD]90%[/TD]
[TD]87%[/TD]
[TD]100%[/TD]
[TD][/TD]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]7/2[/TD]
[TD][/TD]
[TD]60%[/TD]
[TD]60%[/TD]
[TD][/TD]
[TD]Jacob[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]7/3[/TD]
[TD]30%[/TD]
[TD]77%[/TD]
[TD]100%[/TD]
[TD][/TD]
[TD]Jill[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[TD]7/4[/TD]
[TD][/TD]
[TD][/TD]
[TD]73%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6[/TD]
[TD]7/5[/TD]
[TD]87%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]7[/TD]
[TD]7/6[/TD]
[TD]76%[/TD]
[TD]76%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1[/TD]
[TD]7/7[/TD]
[TD]93%[/TD]
[TD]98%[/TD]
[TD]100%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]2[/TD]
[TD]7/8[/TD]
[TD]100%[/TD]
[TD]100%[/TD]
[TD]12%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3[/TD]
[TD]7/9[/TD]
[TD]0%
[/TD]
[TD]67%[/TD]
[TD]73%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]4[/TD]
[TD]7/1[/TD]
[TD][/TD]
[TD]0%[/TD]
[TD]0%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Sparda142]

Meaning can you set up your secondary table like this(Vertically instead of horizontal-transposed)? because then you can just use 1 formula and always drag it down and over, rather than having 3 formulas

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K
Name	John	Jacob	Jill	John	Jacob	Jill
CHECK
CHECK	CHECK
CHECK	CHECK
CHECK

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1-Jul[/TD]
[TD="align: right"]90%[/TD]
[TD="align: right"]87%[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]93%[/TD]
[TD="align: right"]98%[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2-Jul[/TD]
[TD="align: right"][/TD]
[TD="align: right"]60%[/TD]
[TD="align: right"]60%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]90%[/TD]
[TD="align: right"]87%[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3-Jul[/TD]
[TD="align: right"]30%[/TD]
[TD="align: right"]77%[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]64%[/TD]
[TD="align: right"]67%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4-Jul[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]73%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]30%[/TD]
[TD="align: right"]39%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5-Jul[/TD]
[TD="align: right"]87%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]73%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6-Jul[/TD]
[TD="align: right"]76%[/TD]
[TD="align: right"]76%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]87%[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7-Jul[/TD]
[TD="align: right"]93%[/TD]
[TD="align: right"]98%[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]76%[/TD]
[TD="align: right"]76%[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8-Jul[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9-Jul[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]67%[/TD]
[TD="align: right"]73%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1-Jul[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=IFERROR(AVERAGEIFS(C$2:C$11,$A$2:$A$11,$G2,C$2:C$11,"<>100%"),"CHECK")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=IFERROR(AVERAGEIFS(D$2:D$11,$A$2:$A$11,$G2,D$2:D$11,"<>100%"),"CHECK")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]=IFERROR(AVERAGEIFS(E$2:E$11,$A$2:$A$11,$G2,E$2:E$11,"<>100%"),"CHECK")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I placed the data vertically and use your formulas, It worked! thanks for all the help!

 

[Twollaston]

No problem