+ Reply to Thread
Results 1 to 9 of 9

calculate averages

  1. #1
    inspiredtoo
    Guest

    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. #2
    Bob Phillips
    Guest

    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. #3
    Greg Wilson
    Guest

    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. #4
    RagDyeR
    Guest

    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. #5
    Biff
    Guest

    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. #6
    Greg Wilson
    Guest

    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. #7
    inspiredtoo
    Guest

    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. #8
    inspiredtoo
    Guest

    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. #9
    inspiredtoo
    Guest

    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

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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