Using array-entered formulas for calculation average

[paritoshmehta]

Hi,

I am trying to calculate the average depending on some criteria, i.e
in a data table, I want to calculate the average score for a particula
name

The data is:

NAME SCORE
Name1 2.5
Name1 2.5
Name1 3
Name2 2.5
Name2 2.5
Name2 3

//I have defined names as name and score

The formulas (array-entered) that I am using are :

1. { =AVERAGE(IF(name = "Name1",score,0)) }
This is calculating the average for the scores for name1, i.e. 2.5, 2.
and 3 but is treating the rest 3 values as 0; therefore the result i
is showing is 1.33333333, which is the average of 2.5, 2.5, 3, 0, 0
0.


2. When this didnt work, I tried this:
{ =IF(name = "Name1",AVERAGE(score),0) }

but this is calculating the average for all the values and showing th
result as 2.6666667!!


Thanks for any help possible

 

[Kieran]

paritoshmehta,

There are at least two methods for this.
No array formula

=sumproduct(name="name1", Score) / countif(name, "Name1)

Alos look at =sumif as that offers another method

or array method

=sum((name="name1") * Score) / sum(name="name1") - array entered o
course.

in this example name = "name1" will evaluate to 0 or 1 (false or true)
the rest is the usual method of averaging etc

 

[paritoshmehta]

Kieran,

Thanks a ton for your reply!!!!!

The formulas arent working fine for me, I tried the
"=sum((name="name1") * Score) / countif(name, "Name1) " and it worke
pretty fine for me!!!

I just had a small concern, If you see at the formula above you wil
notice that in place of sum, i have used countif; is it fine, do yo
think it will create a problem??? Till now its working preety fine fo
me but i thought it would be better if i ask you??

Till now I was concatenating the cells and using a countif to searc
and although I was getting the result but the file size went way to
high!

Thanks again!!

 

[Kieran]

Paritosh Mehta,

Any method that gets the answer is OK.

If you are looking for speed/less system resources though you will fin
that array formulas will lose in the end.
Consider the =SUMPRODUCT(), =SUMIF() & =COUNTIF() where speed o
resources are tight.
That said, in some circumstances only array formulas will fit th
purpose so it gets back to a judgement call again.

Anyone else in the forum like to comment

 

[Aladin Akyurek]

Given:

{"NAME","SCORE";"Name1",2.5;"Name1",2.5;"Name1",3;"Name2",2.5;"Name2",2.5;"N
ame2",3}

in A1:B7, the conditional average for Name1 is 2.66666666666667, computed
with:

=AVERAGE(IF(A2:A7="Name1",B2:B7))

which must be confirmed with control+shift+enter instead of just with enter.

If don't want to include empty cells associated with a name, use:

=AVERAGE(IF((A2:A7="Name1")*ISNUMBER(B2:B7),B2:B7))

Again, confirmed with control+shift+enter.

 

[paritoshmehta]

Hey Aladin,

This one really works fine for me!!!!

Thanks a ton again for al your help!!!!

Paritos