calculate averages

[Guest]

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

 

[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)

 

[Guest]

This should be easy but you need to be more specific.

Greg

 

[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

 

[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

 

[Guest]

For a non-array formula:

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

Regards,
Greg

 

[Guest]

Thanks Bob, I will give it a whirl
Dan

=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)

 

[Guest]

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

 

[Guest]

Thanks Biff. You brought out a good point about duplicate lows.