Averageifs comparing to other Multiple Averageifs formulas already completed

[styx_87]

Hi there,

I will try to make my question as simple as possible.

I have a spreadsheet with lots of Averageifs formulas that are already working properly. Now All those Averageifs Formulas reflect multiple criteria that need to be met for each.

My problem:
I want to Average all the records that are not included in the other 4 Averageifs calculations. Is that possible? Or how could i do it?

Sorry if it gets confusing.

-Styx_87

 

[StephenCrump]

Welcome to the Forum!

The product of an AverageIfs and the equivalent Countifs will tell you the total scores included.

So provided your criteria don't overlap, you can do it like this:

A12: =COUNT(A2:A11)
B12: =SUM(B2:B11)
E4: =AVERAGEIFS($B$2:$B$11,$A$2:$A$11,2,$B$2:$B$11,">=7")
F4: =COUNTIFS($A$2:$A$11,2,$B$2:$B$11,">=7")
E8: =(B12-SUMPRODUCT(E4:E5,F4:F5))/(A12-F6)

I'd only use this if I was sure there was no overlap, otherwise you'll get an incorrect answer with no indication that anything is wrong.

Book1
	A	B	C	D	E	F
1	ID	Score
2	1	1
3	2	2		Criteria	AverageIf	Countif
4	3	3		ID=2, >=7	8.500	2
5	1	4		ID = 1, <=6	3.667	3
6	3	5				5
7	1	6
8	1	7		Average	5.400
9	2	8
10	2	9
11	1	10
12	10	55
Sheet1

 

[styx_87]

Welcome to the Forum!

The product of an AverageIfs and the equivalent Countifs will tell you the total scores included.

So provided your criteria don't overlap, you can do it like this:

A12: =COUNT(A2:A11)
B12: =SUM(B2:B11)
E4: =AVERAGEIFS($B$2:$B$11,$A$2:$A$11,2,$B$2:$B$11,">=7")
F4: =COUNTIFS($A$2:$A$11,2,$B$2:$B$11,">=7")
E8: =(B12-SUMPRODUCT(E4:E5,F4:F5))/(A12-F6)

I'd only use this if I was sure there was no overlap, otherwise you'll get an incorrect answer with no indication that anything is wrong.

Book1
	A	B	C	D	E	F
1	ID	Score
2	1	1
3	2	2		Criteria	AverageIf	Countif
4	3	3		ID=2, >=7	8.500	2
5	1	4		ID = 1, <=6	3.667	3
6	3	5				5
7	1	6
8	1	7		Average	5.400
9	2	8
10	2	9
11	1	10
12	10	55
Sheet1

Thank you for your quick reply! What I am trying to do is:

So far I have created the AverageIFS for the following Criteria:
CAA>=78 and <80 and SAT >1150
CAA>=80 and SAT>=1150
CAA>=83 and SAT 950-1140
CAA>=85 and SAT 920-940

What I am trying to find is if there is a formula I can find the average for all the other records that do not meet the above unique criteria.

Thanks,

Styx_87

 

[StephenCrump]

OK, so your criteria don't overlap, i.e. it's impossible for any result to be included in two or more of these averages.

Therefore my method will work. Can you adapt it to your layout?

You'll need to start by creating AVERAGIFS() for each of your four criteria.

 

[StephenCrump]

You'll need to start by creating AVERAGIFS() for each of your four criteria.

Oops, sorry. I meant COUNTIFS()

 

[styx_87]

Thanks for all your help. I was able to find the remaining Averages that were missing and I got the correct counts for the remaining unique records.

-Styx_87