Averageif(

[Pizzio]

I'm trying to a conditional average.

Average column A
IF column B =include
AND IF column C = 042017
AND IF column D = blue

I've tried a few variances of the formula but nothing has worked. Any ideas?

 

[sheetspread]

Excel 2010
	A	B	C	D
1	1	2	3	4
2	65	include	42017	blue
3	72	a	3	green
4	28	q	5	red
5	27	w	42017	yellow
6	70	e	8	blue
7	65	include	42017	blue
8	53	include	0	orange
9	34	a	8	yellow
10	86	h	6	blue
11	22	g	3	green
12	59	include	42017	blue
13	44	d	42017	red
14
15
16	63
Sheet13

Cell Formulas
Range	Formula
A16	=AVERAGEIFS($A$2:$A$13,$B$2:$B$13,"include",$C$2:$C$13,42017,$D$2:$D$13,"blue")

 

[tdh777]

Hi Pizzio,

Why don't you share some of "the few variances of the formula" you have tried so we can help you more ?

A little grid of a some sample data would help too.

Cheers,
Warren K.

 

[Johnny Thunder]

Something like this - =IF(AND(B4="Include",C4=42017,D4="Blue"),AVERAGE(A4))

I'm trying to a conditional average.

Average column A
IF column B =include
AND IF column C = 042017
AND IF column D = blue

I've tried a few variances of the formula but nothing has worked. Any ideas?

 

[tdh777]

Hi Johnny,

As you will have seen, sheetspread has already given you a specific example of how to achieve what you want.

Your syntax was a bit skewey :

1. You can't AVERAGE a single cell (you just get the same value back)
2. You were using IF instead of AVERAGEIF

Whenever you need help with an inbuilt Excel Function, just use the "insert Function" button on the Formulas Tab, and then pick your function. Excel will then break up entering the formula parameters in a helpful way with explanation of what each one is. It also provides a link to "help on this function" once you've selected it.

Cheers.

 

[Pizzio]

Thank you. It is exactly what I needed.