calculate averages

I

inspiredtoo

How can I calculate averages automatically subtracting the lowest number in a
range?
I was told to use the SUM, MIN and Count function
Thanks
 
B

Bob Phillips

=AVERAGE(IF(A1:A10<>MIN(A1:A10),A1:A10))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
R

RagDyeR

Why does this sound like homework to me?

Here's an *array* formula, using *none* of the functions you mentioned:

=AVERAGE(IF(A1:A5>SMALL(A1:A5,1),A1:A5))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

How can I calculate averages automatically subtracting the lowest number in
a
range?
I was told to use the SUM, MIN and Count function
Thanks
 
B

Biff

What if there are duplicate lowest numbers? Do you want to exclude all of
them or just one of them?

This will exclude just one of them:

=SUM(A1:A10,-MIN(A1:A10))/(COUNT(A1:A10)-1)

Biff
 
G

Greg Wilson

For a non-array formula:

=(SUM(A1:A12)-MIN(A1:A12))/(COUNT(A1:A12) - 1)

Regards,
Greg
 
I

inspiredtoo

Thanks Bob, I will give it a whirl
Dan

Bob Phillips said:
=AVERAGE(IF(A1:A10<>MIN(A1:A10),A1:A10))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
Click to expand...
 
I

inspiredtoo

You figured it out!! It is homework, sort of. An online class that make a
quantum leap from examples to homework. But then, in the real world, the
best solution is often knowing how to look for help. I had most of it, but
didn't figure out the double brackets...we hadn't covered that yet.

Thanks for your help.
Dan
 
Top