Average Ifs not working

[RoganLogan]

Hi

I was trying to use averageifs to average numbers I have in row 1 from A1:AV1 by a corresponding criteria number I have in row 2 from A2:AV2. So if the numbers in the cells in row 1 have a 1 below in the criteria row 2 they are averaged in row 3 and the same for all the other criteria numbers. The criteria numbers in row 2 go from 1 to 20. This is what i want to achieve below with the formula in row 3 giving me the average

[TABLE="width: 200"]
<tbody>[TR]
[TD]10[/TD]
[TD]20[/TD]
[TD]25[/TD]
[TD]20[/TD]
[TD]40[/TD]
[TD]50[/TD]
[TD]30[/TD]
[TD]40[/TD]
[TD]50[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]18.33[/TD]
[TD]18.33[/TD]
[TD]18.33[/TD]
[TD]36.66[/TD]
[TD]36.66[/TD]
[TD]36.66[/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD]40[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]


I tried using avergeifs but have since discovered that averageifs works on AND condition not OR. So I've tried using array formulas but still cant get it to work. Any help would be greatly appreciated.

Many thanks,

RL

 

[Eric W]

Try this in A3 and drag to the right:

=AVERAGEIFS($A$1:$AV$1,$A$2:$AV$2,A2)

 

[Aladin Akyurek]

Hard to follow.

What do we have in row 1? Data that you want to average?

What does row 2 contain?

What does row 3 contain?

 

[RoganLogan]

Yes, in row 1 is the data that I want to average by the criteria in row 2 with the result in row 3. Sorry if its hard to follow.

 

[Eric W]

Have you tried the formula from post 2?

 

[RoganLogan]

Hi Eric, Yeah the Averageifs formula doesn't work and just gives me a #DIV/0! if I try any more than one criteria. I think averageifs works on a AND condition and I think I need it to do an OR to achieve what I'm after. Thanks for looking though.

 

[Eric W]

My test sheet matches your output exactly:

	A	B	C	D	E	F	G	H	I	J
50
3

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]40[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

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

[TD="align: center"]3[/TD]
[TD="align: right"]18.33333333[/TD]
[TD="align: right"]18.3333[/TD]
[TD="align: right"]18.3333[/TD]
[TD="align: right"]36.6667[/TD]
[TD="align: right"]36.6667[/TD]
[TD="align: right"]36.6667[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]10[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]A3[/TH]
[TD="align: left"]=AVERAGEIFS($A$1:$AV$1,$A$2:$AV$2,A2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




How are your requirements different? If you were to copy the formula to K3, you'd get a #DIV/0 error because there are no matching values. AVERAGEIFS can have more than one condition, but they are AND conditions as you surmise. But there are other ways to handle OR conditions. But please explain how your requirements differ from your example.

 

[RoganLogan]

Hi Eric, Apologies. Yes it works perfectly. I just didn't enter it properly. Thanks so much. RL

 

[Eric W]

Glad you got it figured out.