AverageIFs

  • Thread starter Thread starter mhouseit
  • Start date Start date
M

mhouseit

I need to find a way to average scores based on a criteria of rank.

So if there are 6 ranks in column A ( 1 - 6 ) and scores in column B
( 2.00 - 4.00 ) and names in Column C ( Last Name )

If person XYZ is Rank 1 his average is an average of ALL rank 1 folks.
Easy enough ... until his rank changes 2 and the sheet gets re-sorted
and the formulas stop working right.

Somthing like ...

IF rank = 1 AVERAGE =ALL rank 1, ELSE IF rank 2 AVERAGE = ALL rank
2, ..... and so on...

this way if we change the rank of a person the average for ALL similar
ranks changes.

I hope that is clear enough :-)
 
Why not use the rank of the person

=AVERAGE(IF(A2:A200=rank_of_nominated_person,B2:200))

as an array formula
 
Why not use the rank of the person

=AVERAGE(IF(A2:A200=rank_of_nominated_person,B2:200))

as an array formula

--
__________________________________
HTH

Bob
Click to expand...

Because that gives the average for EVERYONE not just the rank 1
people. If you're rank 1 is your average is the average of ALL rank1
people , if you're rank 2 your average is the average of ALL rank2
people.
Thanks for the reply BTW,.
 
Hello,

I haven't found the time to enhance my UDF Mfreq for average
calculation yet.

But you can take sum your ranks with Sfreq and count them with Pfreq
and divide those later on.

Array-enter into a sufficient long area with 2 columns:
=Sfreq(RANK(A1:A12,A1:A12),B1:B12)

Then another - again with 2 columns:
=Pfreq(RANK(A1:A12,A1:A12))

And then divide...

You can find the functions mentioned at:
http://www.sulprobil.com/html/listfreq.html

Regards,
Bernd
 
How can it get EVERYONE when it explicitly tests for
rank_of_nominated_person

--
__________________________________
HTH

Bob

Why not use the rank of the person

=AVERAGE(IF(A2:A200=rank_of_nominated_person,B2:200))

as an array formula

--
__________________________________
HTH

Bob
Click to expand...

Because that gives the average for EVERYONE not just the rank 1
people. If you're rank 1 is your average is the average of ALL rank1
people , if you're rank 2 your average is the average of ALL rank2
people.
Thanks for the reply BTW,.
 
Back
Top