AverageIFS with conditions not calculating right

[dbaker4020]

ok, need your assistance as I am not getting the correct average when I try to lump 3 together. The formula returns 5.3 rather than 8.667

Thanks in advance!

=AVERAGEIFS(D:D,B:B,{"Blue","Red","Yellow"})

ID Color Test 1 Test 2
33169 Black 1.9 1.6
33590 Black 5.6 8.3
45412236 Black 6 8.6
45412575 Blue 1.9 4.5
44511890 Blue 4 5.3
42712464 Blue 4.5 6.1
42712385 Red 2.4 2
42712564 Red 8.6 12.7
45412520 Red 13 13
44512269 White 1.7 0.9
40912221 White 1.9 1.7
40913435 White 4.3 1.8
40912829 Yellow 13 8.4
46311735 Yellow 12.2 13
46311745 Yellow 13 13

[JBeaucaire]

Try this array formula instead:

=AVERAGE(AVERAGEIFS(D:D,B:B,{"Blue","Red","Yellow"}))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.



Or this array variation:
=AVERAGE(AVERAGEIF(B:B,{"Blue","Red","Yellow"},D:D ))

[dbaker4020]

Still not getting a correct calculation on the average.

Here is the data:

Color Pretest
Red 1.6
Red 2.6
Red 1.7
Red 1.6
Yellow 2.9
Yellow 2.8
Yellow 3
Blue 1.8
Blue 1.3
Blue 1.2
Blue 2
Blue 0.8
Blue 1.2
Blue 1.6
Blue 1.5
Blue 1.9
Blue 2.3
Blue 1.1
Blue 2.2
Blue 2.1
Blue 2.6
Blue 1.7
Blue 2.1

The Formula =AVERAGE(AVERAGEIF(G:G,{"Blue","Red","Yellow"},L:L)) CTRL+SHIFT+ENTER

It is returning 2.1625

From my manual calculations, it should be returning 1.895652174 (sum 43.6 / 23)

[JBeaucaire]

Yeah, the math is not being done that way. Your desire to use an inline array {} is resulting in 3 separate formulas being run, then the results of the 3 formulas are being averaged.

The only way to get the total sum first, then divide that by the total count, is to do it manually yourself.

Array formula: =SUM(SUMIF($G:$G, {"Red","Blue","Yellow"}, $L:$L))/SUM(COUNTIF(G:G, {"Blue","Red","Yellow"}))

[Jonmo1]

You could do

=AVERAGE(IF(($G:$G="Red")+($G:$G="Blue")+($G:$G="Yellow"),$L:$L))

Confirmed with CTRL + SHIFT + ENTER