Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate averages not including zero values | Excel Discussion (Misc queries) | |||
any formula to auto calculate 1st-12th is 12 days pls? | Excel Discussion (Misc queries) | |||
compare adjacent cells and calculate averages | Excel Worksheet Functions | |||
calculate averages | Excel Discussion (Misc queries) | |||
Spreadsheet Won't Calculate | Excel Discussion (Misc queries) |