Using array-entered formulas for calculation average

  • Thread starter Thread starter paritoshmehta
  • Start date Start date
P

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
 
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
 
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!!
 
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
 
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.
 
Hey Aladin,

This one really works fine for me!!!!

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

Paritos
 
Back
Top