# calculate averages

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

2. ## Re: 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" <inspiredtoo@discussions.microsoft.com> wrote in message
news:B89C9DA9-FAB9-452A-A43F-DDD5EEA5F862@microsoft.com...
> 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

3. ## RE: 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

4. ## Re: 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: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 !
----------------------------------------------------------------------------
-------------------

"inspiredtoo" <inspiredtoo@discussions.microsoft.com> wrote in message
news:B89C9DA9-FAB9-452A-A43F-DDD5EEA5F862@microsoft.com...
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

5. ## Re: 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" <inspiredtoo@discussions.microsoft.com> wrote in message
news:B89C9DA9-FAB9-452A-A43F-DDD5EEA5F862@microsoft.com...
> 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

6. ## RE: 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

7. ## Re: 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" <inspiredtoo@discussions.microsoft.com> wrote in message
> news:B89C9DA9-FAB9-452A-A43F-DDD5EEA5F862@microsoft.com...
> > 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

>
>
>

8. ## Re: 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: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 !
> ----------------------------------------------------------------------------
> -------------------
>
> "inspiredtoo" <inspiredtoo@discussions.microsoft.com> wrote in message
> news:B89C9DA9-FAB9-452A-A43F-DDD5EEA5F862@microsoft.com...
> 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
>
>
>

9. ## Re: 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" <inspiredtoo@discussions.microsoft.com> wrote in message
> news:B89C9DA9-FAB9-452A-A43F-DDD5EEA5F862@microsoft.com...
> > 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

>
>
>

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1