 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2005Special function
Hi, I have a range of cells (100+), which should all have a value of around 3600, so the average should also be around 3600 (minimum should not be lower then 300, maximum shouldn't be higher than 10000). But sometimes some cells contain values of -2000000 or +/-2 or +2000000. So when I calculate the average (using the AVERAGE Function) I get abnormal results. Is there a function that can automaticly reconize the excessive values and exclude them from the Average calculation. Thanks, Bart Bart, Array enter using Ctrl-Shift-Enter: =AVERAGE(IF((A1:A100>300)*(A1:A100<10000),A1:A100)) Change range to match your actual values. HTH, Bernie MS Excel MVP > Hi, > [quoted text clipped - 9 lines] > > Bart This works for me =SUMPRODUCT(--(A1:A100>=300),--(A1:A100<=10000),A1:A100)/SUMPRODUCT(--(A1:A100>=300),--(A1:A100<=10000))  SignatureBernard V Liengme www.stfx.ca/people/bliengme remove caps from email > Hi, > [quoted text clipped - 10 lines] > > Bart There is no statistical/analitical function to do this. A function that recognizes high or low values compared to the rest and excludes them? Bart > This works for me > =SUMPRODUCT(--(A1:A100>=300),--(A1:A100<=10000),A1:A100)/SUMPRODUCT(--(A1:A100>=300),--(A1:A100<=10000)) [quoted text clipped - 14 lines] >> >> Bart No, there is no built-in function that knows about 'outliers' SignatureBernard V Liengme www.stfx.ca/people/bliengme remove caps from email > There is no statistical/analitical function to do this. A function that > recognizes high or low values compared to the rest and excludes them? [quoted text clipped - 19 lines] >>> >>> Bart >There is no statistical/analitical function to do this. A function that >recognizes high or low values compared to the rest and excludes them? > >Bart Take a look at HELP for the TRIMMEAN function. This can help you eliminate outliers. --ron |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.