Can someone please help me? I am doing a grade book and have 4 test scores
say in A1, A2, A3, and A4. I want to get the average score but dropping the
lowest test score first and giving equal weight to the remaining 3 test
scores. Can someone help me with this, please?

Thanks!

One way:

=AVERAGE(LARGE(A1:A4,{1,2,3}))

In article ,
Caribbrz wrote:

Can someone please help me? I am doing a grade book and have 4 test scores
say in A1, A2, A3, and A4. I want to get the average score but dropping the
lowest test score first and giving equal weight to the remaining 3 test
scores. Can someone help me with this, please?

Thanks!

Thanks! This does work, however, not the formula I am looking for. I am
looking to use the SUM, MIN, and COUNT functions to get my answer.

"JE McGimpsey" wrote:

One way:

=AVERAGE(LARGE(A1:A4,{1,2,3}))

In article ,
Caribbrz wrote:

Can someone please help me? I am doing a grade book and have 4 test scores
say in A1, A2, A3, and A4. I want to get the average score but dropping the
lowest test score first and giving equal weight to the remaining 3 test
scores. Can someone help me with this, please?

Thanks!

You could try:
=(SUM(A1:A4)-MIN(A1:A4))/(COUNT(A1:A4)-1)

but it assumes there are no blank cells in A1:A4, otherwise it will average
the top 2 scores instead of the top 3 scores.

I think that could be fixed using:
=(SUM(A1:A4)-MIN(--(A1:A4)))/(COUNT(--(A1:A4))-1)

array entered using Ctrl+Shift+Enter


"Caribbrz" wrote:

Thanks! This does work, however, not the formula I am looking for. I am
looking to use the SUM, MIN, and COUNT functions to get my answer.

"JE McGimpsey" wrote:

One way:

=AVERAGE(LARGE(A1:A4,{1,2,3}))

In article ,
Caribbrz wrote:

Can someone please help me? I am doing a grade book and have 4 test scores
say in A1, A2, A3, and A4. I want to get the average score but dropping the
lowest test score first and giving equal weight to the remaining 3 test
scores. Can someone help me with this, please?

Thanks!

Thanks! That is exactly what I was looking for!
Thanks so much!

"JMB" wrote:

You could try:
=(SUM(A1:A4)-MIN(A1:A4))/(COUNT(A1:A4)-1)

but it assumes there are no blank cells in A1:A4, otherwise it will average
the top 2 scores instead of the top 3 scores.

I think that could be fixed using:
=(SUM(A1:A4)-MIN(--(A1:A4)))/(COUNT(--(A1:A4))-1)

array entered using Ctrl+Shift+Enter


"Caribbrz" wrote:

Thanks! This does work, however, not the formula I am looking for. I am
looking to use the SUM, MIN, and COUNT functions to get my answer.

"JE McGimpsey" wrote:

One way:

=AVERAGE(LARGE(A1:A4,{1,2,3}))

In article ,
Caribbrz wrote:

Can someone please help me? I am doing a grade book and have 4 test scores
say in A1, A2, A3, and A4. I want to get the average score but dropping the
lowest test score first and giving equal weight to the remaining 3 test
scores. Can someone help me with this, please?

Thanks!

Hmmm sounds like homework!

Look at these formulas
=SUM(A1:A4)-MIN(A1:A4)
and
=COUNT(A1:A4)-1

Now see if you can put them together to come up
with an average.

HTH
Martin

You are absolutly right. I am learning Excel and having a tough time with
the "Functions" when multiples are involved. I still have two more tough
ones to learn. The text book does not explain everything too well, in my
opinion.

Thanks for your help!

"MartinW" wrote:

Hmmm sounds like homework!

Look at these formulas
=SUM(A1:A4)-MIN(A1:A4)
and
=COUNT(A1:A4)-1

Now see if you can put them together to come up
with an average.

HTH
Martin

Yeah, Textbooks often only make sense after you have learnt
what you were trying to learn in the first place!

In future posts it might be worth mentioning that you are doing
homework, that way responders can explain more of the
process rather than just spitting out an answer.

Regards
Martin

Why would your homework require you to use a less efficient solution?

I'd find a new instructor/textbook...

In article ,
Caribbrz wrote:

Thanks! This does work, however, not the formula I am looking for. I am
looking to use the SUM, MIN, and COUNT functions to get my answer.

Sorry JE

But I'm afraid I can't agree with you there. You have to learn
to walk before you can run, and any textbook/instructor, that
attempted to teach the slick(sometimes almost magical) type solutions
that are provided by yourself and others in these newsgroups,
would be doing a great dis-service to the student.
Get the basics right first and then teach the shortcuts is my motto.

Regards
Martin

I might agree if the formula I provided was a shortcut (but then, I
don't use mottos...).

I don't have any problem with teaching the basics. I don't have a
problem with demonstrating that there are multiple solutions for any
particular problem in XL.

I DO, however, have a problem with deliberately rejecting efficient
solutions using simple built-in functions in favor of less efficient
ones simply because they don't fit into someone's definition of "basic".
When I teach, I *reward* alternative solutions, not reject them.

But de gustibus non disputandum est...

In article ,
"MartinW" wrote:

Get the basics right first and then teach the shortcuts is my motto.

Mon, 7 May 2007 20:58:01 -0700 from Caribbrz
:
Can someone please help me? I am doing a grade book and have 4 test scores
say in A1, A2, A3, and A4. I want to get the average score but dropping the
lowest test score first and giving equal weight to the remaining 3 test
scores. Can someone help me with this, please?

=(sum(A1:A4)-min(A1:A4))/3

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/