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
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
=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
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
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
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
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
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
>
>
>
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
>
>
>
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
>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks