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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|