Average Function but exclude info

[Linda RQ]

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

 

[Bernard Liengme]

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

 

[Joe User]

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]

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

 

[Mitch]

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")