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]

 

[ChrisFoster]

What is it you're actually trying to achieve?

 

[Joe4]

1. The AVERAGEIFS function needs to have an odd number of arguments. First, the range you wish to average. Then pairs of ranges to check and their conditions. You have 6 arguments.
See: https://www.techonthenet.com/excel/formulas/averageifs.php

2. There should be a comma before the word "CHECK" in your formula.

 

[Sparda142]

What is it you're actually trying to achieve?

i'm trying to get the average percentage excluding 100% for each person by weekday.

 

[Twollaston]

So for John-1 you want to average all the numbers in column c if they have a 1 in column a?

 

[Sparda142]

So for John-1 you want to average all the numbers in column c if they have a 1 in column a?

correct as long as the number isn't 100%

 

[Twollaston]

correct as long as the number isn't 100%

what about the blanks?
For instance C3? Should that be 0%? because the formula will skip including it in the average

Also, would you be able to set it up like this? (it will make it one formula instead of 3 formulas)

Excel 2010
	G	H	I	J
12		John	Jacob	Jill
13	1	93%	93%	93%
14	2	90%
15	3	0%
16	4	15%
17	5	#DIV/0!
18	6	87%
19	7	76%
Sheet6

 

[Sparda142]

what about the blanks?
For instance C3? Should that be 0%? because the formula will skip including it in the average

Also, would you be able to set it up like this? (it will make it one formula instead of 3 formulas)


Excel 2010

	G	H	I	J
John	Jacob	Jill

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

[TD="align: center"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]93%[/TD]
[TD="align: right"]93%[/TD]
[TD="align: right"]93%[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]90%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]#DIV/0![/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]87%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]76%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

I want the formula to skip blanks in the average. Can you be more specific with "like this?" did you mean you wanted to fill in blanks with 100% or 0%?

 

[Twollaston]

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
1		Name	John	Jacob	Jill			John	Jacob	Jill
2	2	1-Jul	90%	87%	100%		1	93%	98%	CHECK
3	3	2-Jul		60%	60%		2	90%	87%	12%
4	4	3-Jul	30%	77%	100%		3	0%	64%	67%
5	5	4-Jul			73%		4	30%	39%	0%
6	6	5-Jul	87%				5	CHECK	CHECK	73%
7	7	6-Jul	76%	76%			6	87%	CHECK	CHECK
8	1	7-Jul	93%	98%	100%		7	76%	76%	CHECK
9	2	8-Jul	100%	100%	12%
10	3	9-Jul	0%	67%	73%
11	4	1-Jul		0%	0%
Sheet6

Cell Formulas
Range	Formula
H2	=IFERROR(AVERAGEIFS(C$2:C$11,$A$2:$A$11,$G2,C$2:C$11,"<>100%"),"CHECK")
I2	=IFERROR(AVERAGEIFS(D$2:D$11,$A$2:$A$11,$G2,D$2:D$11,"<>100%"),"CHECK")
J2	=IFERROR(AVERAGEIFS(E$2:E$11,$A$2:$A$11,$G2,E$2:E$11,"<>100%"),"CHECK")

 

[Twollaston]

If you need to keep it the same you can use these 3 formulas and just drag them to the right

Excel 2010
	G	H
2	John	93%
3	Jacob	98%
4	Jill	Check
Sheet6

Cell Formulas
Range	Formula
H2	=IFERROR(AVERAGEIFS($C$2:$C$11,$A$2:$A$11,H$1,$C$2:$C$11,"<>100%"),"Check")
H3	=IFERROR(AVERAGEIFS($D$2:$D$11,$A$2:$A$11,H$1,$D$2:$D$11,"<>100%"),"Check")
H4	=IFERROR(AVERAGEIFS($E$2:$E$11,$A$2:$A$11,H$1,$E$2:$E$11,"<>100%"),"Check")