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")

	a	b	c	d	e	f	g	H	I	J	K	L	M	N	O	P	Q	R
1		Name	John	Jacob	Jill			1	2	3	4	5	6	7
2	2	7/1	90%	87%	100%		John
3	3	7/2		60%	60%		Jacob
4	4	7/3	30%	77%	100%		Jill
5	5	7/4			73%
6	6	7/5	87%
7	7	7/6	76%	76%
8	1	7/7	93%	98%	100%
9	2	7/8	100%	100%	12%
10	3	7/9	0%	67%	73%
11	4	7/1		0%	0%

<tbody>
</tbody>

 

[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
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%

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</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")