A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Average Function but exclude info



 
 
Thread Tools Display Modes
  #1  
Old April 16th, 2010, 08:18 PM posted to microsoft.public.excel.newusers
Linda RQ[_2_]
external usenet poster
 
Posts: 76
Default 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


  #2  
Old April 16th, 2010, 08:26 PM posted to microsoft.public.excel.newusers
Bernard Liengme
external usenet poster
 
Posts: 516
Default 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

  #3  
Old April 16th, 2010, 08:42 PM posted to microsoft.public.excel.newusers
Joe User[_2_]
external usenet poster
 
Posts: 757
Default 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


  #4  
Old April 16th, 2010, 08:55 PM posted to microsoft.public.excel.newusers
Linda RQ[_2_]
external usenet poster
 
Posts: 76
Default 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



  #5  
Old April 16th, 2010, 09:17 PM posted to microsoft.public.excel.newusers
Mitch
external usenet poster
 
Posts: 159
Default Average Function but exclude info

Hi Linda,

Use this formula beside g2:g7 (Column H): =IF($G210,"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


.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:05 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.