Average over a column if text in another column maches

Question

I want to compute the average over one column if the text in another column matches a certain text.

eg:

A      B      C
aa     6      =AVERAGEIF(B1:B6;EXACT(A1:A6;"aa"))
bb     15  
aa     8  
bb     17  
cc     1  
aa     5

But the value in C gets 1. Why? How can I do what I want?

Try this =AVERAGEIF(A:A,"aa",B:B) – Bigtree May 22 '14 at 15:54 — Bigtree, May 22 '14 at 15:54
I am getting `Err:501` (Invalid character) – thedarkside ofthemoon May 22 '14 at 15:57 — thedarkside ofthemoon, May 22 '14 at 15:57
In fact is LibreOffice Calc the reason why it dose so? – thedarkside ofthemoon May 22 '14 at 16:07 — thedarkside ofthemoon, May 22 '14 at 16:07
I do not know, how can I verify that? – thedarkside ofthemoon Jul 01 '14 at 12:41 — thedarkside ofthemoon, Jul 01 '14 at 12:41

score 1 · Answer 1 · answered May 22 '14 at 16:30

1

I would suggest using the AVERAGEIFS() function instead of AVERAGEIF(). See below:

=AVERAGEIFS(B1:B6,A1:A6,"aa")

This will yield as result of 6.3333.

Cheers.

answered May 22 '14 at 16:30

Derrik

I am still getting that error in both excel (Windows) and calc (Ubuntu) – thedarkside ofthemoon May 23 '14 at 07:04

1 Answers1