Average Function but exclude info
 

Hi Everyone,

Excel 2003. I have a list of numbers. I use the Insert/Function then drag
the cells I want to create my formula for averaging this column. I would
like to have another cell with the average of this column but want to
exclude any number greater than 10 days. Right now my only option (because
of lack of knowldege) is to resort my list and manually exclude anything I
see that is greater than 10 days. For this particular sheet my formula is
=AVERAGE(Jan!G2:G7).

Thanks,
Linda

Average Function but exclude info
 

The numbers you want to average are in column G
What column has the dates?
Do you want the average on the same sheet (ie the Jan sheet) or else where?
Want version of Excel ?
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Linda RQ" wrote in message
...
Hi Everyone,

Excel 2003. I have a list of numbers. I use the Insert/Function then
drag the cells I want to create my formula for averaging this column. I
would like to have another cell with the average of this column but want
to exclude any number greater than 10 days. Right now my only option
(because of lack of knowldege) is to resort my list and manually exclude
anything I see that is greater than 10 days. For this particular sheet my
formula is =AVERAGE(Jan!G2:G7).

Thanks,
Linda

Average Function but exclude info
 

"Linda RQ" wrote:
I would like to have another cell with the average of this
column but want to exclude any number greater than 10 days.

Your requirements are unclear, but if the following does not do exactly what
you want, perhaps you can use it as a paradigm. Enter the following array
formula[*]:

=AVERAGE(IF(Jan!G2:G7<=10,Jan!G2:G7))
[*] Enter an array formula by pressing ctrl+shift+Enter instead of just
Enter. In the Formula Bar, the formula will be enclosed by curly braces,
viz. {=formula}. You cannot type the curly braces yourself; that is just
Excel's way of displaying an array formula. Note that many array formulas
will return a value if you simply press Enter; but with sufficient testing,
you will discover that it is the wrong result. If you make a mistake,
select the cell, press F2, edit as needed, then press ctrl+shift+Enter.


----- original message -----

"Linda RQ" wrote in message
...
Hi Everyone,

Excel 2003. I have a list of numbers. I use the Insert/Function then
drag the cells I want to create my formula for averaging this column. I
would like to have another cell with the average of this column but want
to exclude any number greater than 10 days. Right now my only option
(because of lack of knowldege) is to resort my list and manually exclude
anything I see that is greater than 10 days. For this particular sheet my
formula is =AVERAGE(Jan!G2:G7).

Thanks,
Linda

Average Function but exclude info
 

Hi Bernard,

Thanks for helping me explain better....The cells I am averaging are the
number of days and are in number format with 2 decimals, so it's the numbers
in column G that I want to average but I want it to not count any numbers
greater than 10. I am using Excel 2003.

Linda

"Bernard Liengme" wrote in message
...
The numbers you want to average are in column G
What column has the dates?
Do you want the average on the same sheet (ie the Jan sheet) or else
where?
Want version of Excel ?
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Linda RQ" wrote in message
...
Hi Everyone,

Excel 2003. I have a list of numbers. I use the Insert/Function then
drag the cells I want to create my formula for averaging this column. I
would like to have another cell with the average of this column but want
to exclude any number greater than 10 days. Right now my only option
(because of lack of knowldege) is to resort my list and manually exclude
anything I see that is greater than 10 days. For this particular sheet
my formula is =AVERAGE(Jan!G2:G7).

Thanks,
Linda

Average Function but exclude info
 

Hi Linda,

Use this formula beside g2:g7 (Column H): =IF($G2<10,"LESS10","10")
This is to tag your values if it is less 10 or more than 10.

on another column or whatever cell you want to place your average value less
the numbers greater than 10 use this formula:

=SUMIF(G2:G8,"LESS10",F2:F8)/COUNTIF(G2:G8,"LESS10")

"Linda RQ" wrote:

Hi Everyone,

Excel 2003. I have a list of numbers. I use the Insert/Function then drag
the cells I want to create my formula for averaging this column. I would
like to have another cell with the average of this column but want to
exclude any number greater than 10 days. Right now my only option (because
of lack of knowldege) is to resort my list and manually exclude anything I
see that is greater than 10 days. For this particular sheet my formula is
=AVERAGE(Jan!G2:G7).

Thanks,
Linda


.