Making up a grade sheet for school

[Dean Solomon]

One of my professors gave me a project to make up a grade sheet in
Excel. My question is, is there a function that will average the
grades in a column or a row but will not average in the 4 lowest
grades in that column or row? In other words, if a column or row has
10 total grades (50, 63, 99, 78, 90, 100, 70, 85, 95, and 40) I need
to drop the 4 lowest and average the other 6. Any help would be
apprieciated. Thanks in advance. I am using MS Excel 2000.

 

[Peo Sjoblom]

One way

=AVERAGE(LARGE(A1:A10,ROW(INDIRECT("1:6"))))

entered with ctrl + shift & enter

 

[Dean Solomon]

Thank you for your reply. This formula worked very well except that I
forgot to put in an important piece of information. In this
spreadsheet there maybe a total of 20 grades that have to be entered
for the whole semester but the students have only finished half the
semester so there will be blank cells in the spreadsheet. Is there a
way, with this formula, to only average cells that have data in them
and ignore the empty cells until the professor adds more grades and
then re-averages the grades to get an updated average? I hope that I
said this right.

My orginal post is as follows:

One of my professors gave me a project to make up a grade sheet in
Excel. My question is, is there a function that will average the
grades in a column or a row but will not average in the 4 lowest
grades in that column or row? In other words, if a column or row has
10 total grades (50, 63, 99, 78, 90, 100, 70, 85, 95, and 40) I need
to drop the 4 lowest and average the other 6. Any help would be
apprieciated. Thanks in advance. I am using MS Excel 2000.

 

[Leo Heuser]

Here's one way:

=AVERAGE(LARGE(A1:A20,ROW(INDIRECT("1:"&COUNTA(A1:A20)-4))))

again entered with <Shift><Ctrl><Enter>

For 1 to 4 grades the formula will return the #REF! error
For 5 grades it will return the max-value of the range
For 6 grades it will return the average of the two largest grades.
For 7 grades it will return the average of the three largest grades
etc.

If you want to avoid the #REF!, you can use this variation instead:

=IF(COUNTA(A1:A20)>4,AVERAGE(LARGE(A1:A20,
ROW(INDIRECT("1:"&COUNTA(A1:A20)-4)))),"Not yet!")

--
Best Regards
Excel MVP
Leo Heuser

Followup to newsgroup only please.

 

[plangtry]

I have 5 grades. THe first 2 must be averaged and then account for 20%
of the total. The next two grades must be averaged and then account
for 30% of the total. The last grade must account for 50% of the
total. Any ideas would be appreciated. I'm sure its pretty simple,
just not very strong with excel.

Paul

 

[Dean Solomon]

Thank you so very much Peo and Leo for your help! My professor also
thanks you for the time that you are going to save her. Again, THANK
YOU and have a very nice day!!!!!!!!

 

[Leo Heuser]

You're welcome Dean and thanks
for the feedback. It's appreciated!

Whiz.......

Peo & Leo strike again

 

[Peo Sjoblom]

LOL!

You're welcome Dean and thanks
for the feedback. It's appreciated!

Whiz.......

Peo & Leo strike again

 

[Leo Heuser]

Paul

Could you give a full example with numbers please?
Calculated and all.
I'm not sure exactly what, you're after.

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.