Getting the average of n maximum grades in an unsorted list of grades

  • Thread starter Thread starter clickonce
  • Start date Start date
C

clickonce

Dear All,

I need to know the function that would calculate the average of best
grades in a list of grades - let say 9 grades - knowing that the grade
are not sorted.

Thanks
 
With your figures in range A1 to A9

{=AVERAGE(IF(A1:A9>=LARGE(A1:A9,4),A1:A9))}
Array formula to be entered using Ctrl|Shift|Enter

--
XL2002
Regards

William

(e-mail address removed)

| Dear All,
|
| I need to know the function that would calculate the average of best 4
| grades in a list of grades - let say 9 grades - knowing that the grades
| are not sorted.
|
| Thanks.
|
|
| ---
| Message posted
|
 
clickonce > said:
Dear All,

I need to know the function that would calculate the average of best 4
grades in a list of grades - let say 9 grades - knowing that the grades
are not sorted.

Thanks.
Click to expand...

If your range is A1:A9
=AVERAGE(LARGE(A1:A9,ROW(1:4)))
it's an array formula (to be entered with CTRL+SHIFT+ENTER)

Regards
Barbara
 
If your range is A1:A9
=AVERAGE(LARGE(A1:A9,ROW(1:4)))
it's an array formula (to be entered with CTRL+SHIFT+ENTER)
Click to expand...

One suggestion, Barbara.

Using your formula, if you copy/drag it down, the ROW values will change.

A more stable form would be =AVERAGE(LARGE(A1:A9,{1,2,3,4}))

or, =AVERAGE(LARGE(A1:A9,ROW(INDIRECT("1:4"))))


--ron
 
Ron Rosenfeld said:
One suggestion, Barbara.

Using your formula, if you copy/drag it down, the ROW values will change.

A more stable form would be =AVERAGE(LARGE(A1:A9,{1,2,3,4}))

or, =AVERAGE(LARGE(A1:A9,ROW(INDIRECT("1:4"))))
Click to expand...

Thanks Ron.
You're right.
Usually I use
=ROW($1:$4) when I have to copy it down.

But I didn't use this form in my answer...

Regards
Barbara
 
Thanks Ron.
You're right.
Usually I use
=ROW($1:$4) when I have to copy it down.

But I didn't use this form in my answer..
Click to expand...

Barbara,

With the =ROW($1:$4) form, that will change if you insert a row in the range of
$1:$4. Again, the ROW(INDIRECT("1:4")) doesn't change even with a row
insertion.


--ron
 
Back
Top