Finding top 10 scores out of 15, adding top 10, finding average

[Vicki]

HELP

Hello everyone ..

I have developed a grading template for the faculty to use (I adapt the template to each faculty member's needs) and I have a situation I haven't run into before

The teacher is giving a total of 15 quizzes, but will only use the top 10 score (out of the 15) to count towards the student's final grade. Here's the verbal text for what I'm try to do

Find the top 10 scores out of 15 scores , sum the top 10 scores, and then find the average by dividing by 1

Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q1
90 70 100 100 70 95 75 80 95 100 80 100 85 75 8

I have looked through the list of worksheet functions find the "function" that will be able to determine the top 10 scores out of a range of 15, but I can't seem to find anything that fits. I know that I'm going to have severa nested formulas. Here's part of what I think the formula should look like

I know that I'll use the SUM and AVERAGE functions, but I am having trouble with the rest

Any ideas? Your help will be greatly appreciated

Vick
Portland

 

[Trevor Shuttleworth]

Vicki

assuming the data is in row 2:

=SUM(LARGE(A2:O2,{1,2,3,4,5,6,7,8,9,10}))/10 = 93

Regards

Trevor

HELP!

Hello everyone ...

I have developed a grading template for the faculty to use (I adapt the

template to each faculty member's needs) and I have a situation I haven't
run into before.

The teacher is giving a total of 15 quizzes, but will only use the top 10

score (out of the 15) to count towards the student's final grade. Here's
the verbal text for what I'm try to do:

Find the top 10 scores out of 15 scores , sum the top 10 scores, and then

find the average by dividing by 10

Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15
90 70 100 100 70 95 75 80 95 100 80 100 85 75 85

I have looked through the list of worksheet functions find the "function"

that will be able to determine the top 10 scores out of a range of 15, but I
can't seem to find anything that fits. I know that I'm going to have severa
nested formulas. Here's part of what I think the formula should look like:

 

[Ken Wright]

=AVERAGE(LARGE(A2:O2,ROW(INDIRECT("1:10"))))

array entered using CTRL+SHIFT+ENTER

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

HELP!

Hello everyone ...

I have developed a grading template for the faculty to use (I adapt the

template to each faculty member's needs) and I have a situation I haven't run
into before.

The teacher is giving a total of 15 quizzes, but will only use the top 10

score (out of the 15) to count towards the student's final grade. Here's the
verbal text for what I'm try to do:

Find the top 10 scores out of 15 scores , sum the top 10 scores, and then find the average by dividing by 10

Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15
90 70 100 100 70 95 75 80 95 100 80 100 85 75 85

I have looked through the list of worksheet functions find the "function" that

will be able to determine the top 10 scores out of a range of 15, but I can't
seem to find anything that fits. I know that I'm going to have severa nested
formulas. Here's part of what I think the formula should look like:

 

[Vicki]

Good day, Trevor ..

Thank you SO MUCH for your help! In my first attempts to create this formula, I knew that I had to rank the 15 scores to be able to get to where I needed to go, and I was trying to use the RANK function as part of the formula. How is the LARGE function different from the RANK function (i.e., how Excel uses the function to perform a calculation)

Again ... thank you for your assistance

Vick

----- Trevor Shuttleworth wrote: ----

Vick

assuming the data is in row 2

=SUM(LARGE(A2:O2,{1,2,3,4,5,6,7,8,9,10}))/10 = 9

Regard

Trevo

HELP

template to each faculty member's needs) and I have a situation I haven'
run into beforescore (out of the 15) to count towards the student's final grade. Here'
the verbal text for what I'm try to do

90 70 100 100 70 95 75 80 95 100 80 100 85 75 8

that will be able to determine the top 10 scores out of a range of 15, but
can't seem to find anything that fits. I know that I'm going to have sever
nested formulas. Here's part of what I think the formula should look like

 

[Vicki]

Good day, Ken ..

Thank you SO MUCH for your help! In my first attempts to create this formula, I knew that I had to rank the 15 scores to be able to get to where I needed to go, and I was trying to use the RANK function as part of the formula. I've never attempted this hard of a formula before - I guess I have more studying to do

Again ... thank you for your assistance

Vick


----- Ken Wright wrote: ----

=AVERAGE(LARGE(A2:O2,ROW(INDIRECT("1:10")))

array entered using CTRL+SHIFT+ENTE

--
Regard
Ken....................... Microsoft MVP - Exce
Sys Spec - Win XP Pro / XL 00/02/0

---------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-
---------------------------------------------------------------------------

HELP

template to each faculty member's needs) and I have a situation I haven't ru
into beforescore (out of the 15) to count towards the student's final grade. Here's th
verbal text for what I'm try to do

90 70 100 100 70 95 75 80 95 100 80 100 85 75 8

will be able to determine the top 10 scores out of a range of 15, but I can'
seem to find anything that fits. I know that I'm going to have severa neste
formulas. Here's part of what I think the formula should look like

 

[Aladin Akyurek]

Assuming that a grade of 0 is never given and A2:O2 houses the grades...

In Q2 enter:

=IF(COUNT(A2:O2),LARGE(A2:O2,MIN(10,COUNT(A2:O2))),0)

In P2 enter:

=IF(Q2,(SUMIF(A2:O2,">"&Q2)+(MIN(10,COUNT(A2:O2))-COUNTIF(A2:O2,">"&Q2))*Q2)
/MIN(10,COUNT(A2:O2)),"")

HELP!

Hello everyone ...

I have developed a grading template for the faculty to use (I adapt the

template to each faculty member's needs) and I have a situation I haven't
run into before.

The teacher is giving a total of 15 quizzes, but will only use the top 10

score (out of the 15) to count towards the student's final grade. Here's
the verbal text for what I'm try to do:

Find the top 10 scores out of 15 scores , sum the top 10 scores, and then

find the average by dividing by 10

Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15
90 70 100 100 70 95 75 80 95 100 80 100 85 75 85

I have looked through the list of worksheet functions find the "function"

that will be able to determine the top 10 scores out of a range of 15, but I
can't seem to find anything that fits. I know that I'm going to have severa
nested formulas. Here's part of what I think the formula should look like:

 

[Vicki]

Good day! I wanted to thank you for taking time to respond to my plea for help. It's been interesting to see how different formulas can achieve the same end result. I definitely need to take some Excel class that specifically revolve around creating more advanced formulas

Take care

Vick
----- Aladin Akyurek wrote: ----

Assuming that a grade of 0 is never given and A2:O2 houses the grades..

In Q2 enter

=IF(COUNT(A2:O2),LARGE(A2:O2,MIN(10,COUNT(A2:O2))),0

In P2 enter

=IF(Q2,(SUMIF(A2:O2,">"&Q2)+(MIN(10,COUNT(A2:O2))-COUNTIF(A2:O2,">"&Q2))*Q2
/MIN(10,COUNT(A2:O2)),""

HELP

template to each faculty member's needs) and I have a situation I haven'
run into beforescore (out of the 15) to count towards the student's final grade. Here'
the verbal text for what I'm try to do

90 70 100 100 70 95 75 80 95 100 80 100 85 75 8

that will be able to determine the top 10 scores out of a range of 15, but
can't seem to find anything that fits. I know that I'm going to have sever
nested formulas. Here's part of what I think the formula should look like

 

[Trevor Shuttleworth]

Vicki

Rank isn't a function I've made much (any ?) use of. From what I
understand, if you have a number, you can use this function to return its
Rank (Position) in a list of numbers. For example, 80 ranks 10th in your
list of numbers, 95 would be 5th and so on.

Large, on the other hand, returns the nth largest number. So,
=LARGE(A2:O2,RANK(95,A2:O2)) would return 95 ... how useful is that ?

Hope this helps

Regards

Trevor

Good day, Trevor ...

Thank you SO MUCH for your help! In my first attempts to create this

formula, I knew that I had to rank the 15 scores to be able to get to where
I needed to go, and I was trying to use the RANK function as part of the
formula. How is the LARGE function different from the RANK function (i.e.,
how Excel uses the function to perform a calculation)?