calculate averages
 

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

calculate averages
 

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

"inspiredtoo" wrote in message
...
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

calculate averages
 

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

Greg


"inspiredtoo" wrote:

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

calculate averages
 

Why does this sound like homework to me?

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

=AVERAGE(IF(A1:A5SMALL(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 !
----------------------------------------------------------------------------
-------------------

"inspiredtoo" wrote in message
...
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

calculate averages
 

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

"inspiredtoo" wrote in message
...
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

calculate averages
 

For a non-array formula:

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

Regards,
Greg

"inspiredtoo" wrote:

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

calculate averages
 

Thanks Bob, I will give it a whirl
Dan

"Bob Phillips" wrote:

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

"inspiredtoo" wrote in message
...
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

calculate averages
 

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

"RagDyeR" wrote:

Why does this sound like homework to me?

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

=AVERAGE(IF(A1:A5SMALL(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 !
----------------------------------------------------------------------------
-------------------

"inspiredtoo" wrote in message
...
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

calculate averages
 

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

"Biff" wrote:

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

"inspiredtoo" wrote in message
...
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