Average less MIN formula help needed

[Guest]

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!

 

[JE McGimpsey]

One way:

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

 

[Guest]

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.

 

[Guest]

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

 

[MartinW]

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

 

[Guest]

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

 

[Guest]

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!

 

[JE McGimpsey]

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

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

 

[MartinW]

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

 

[MartinW]

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

 

[JE McGimpsey]

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...

 

[MartinW]

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...

Hi JE

I don't believe the OP rejected your solution out of hand, i'm fairly
sure he/she took it on board as valuable information, however, if the
question
was along the lines of, 'Show how the following problem can be solved
by using the SUM, MIN and COUNT functions', then your response
leaves them short.
Of course, the best answer would be to say, 'here' is the answer as asked,
however, a better way to solve this problem, would be 'this'.

ex animo
Martin

 

[Stan Brown]

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

 

[Guest]

JE, I did use your solution. Then when I re-read the task, it specifically
said to use the MIN, SUM, and COUNT to get your answer. I would have much
rather used your solution....I actaully understood it more. I had the other
answer on my work sheet for 3 hours trying to firgure out what I was doing
wrong beucase I was not getting the correct answer...(I was missing the -1 at
the end of my formula) But, with my luck, I would have gotten docked for
using a formula that had not yet been taught.

And I also agree, I should have explained more in my question. This was my
first time using this board and realized after the fact that I should have
included my formula so someone could explain what I was doing wrong. That
was my bad.

Anyway, thank you all for your help. Now, back to the drawing board to try
and figure out two more fomulas. I might be back asking for help, again.

Thanks again!