 |
 | 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 / Worksheet Functions / October 2006Average if
How do I calculate the averages of numbers in 2 sets of ranges... excluding zero vaules? Thanks Hi! Is this what you mean? Average A1:A5 and F1:F5? If so, try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(CHOOSE({1,2},A1:A5,F1:F5)<>0,CHOOSE({1,2},A1:A5,F1:F5))) Biff > How do I calculate the averages of numbers in 2 sets of ranges... > excluding > zero vaules? > > Thanks Thanks But when I use the formula {=AVERAGE(IF(CHOOSE({1,2},B9:B17,B24:B55)<>0,CHOOSE({1,2},B9:B17,B24:B55)))} it returns a value of #N/A What am I doing wrong? > Hi! > [quoted text clipped - 16 lines] > > > > Thanks Well, that's why it's a good idea to provide as much detail as possible when posting a question. The formula I suggested only works when each range is the same size. So, instead of taking another guess at what you want: Are there any negative numbers in either range? Are there any empty cells in either range? Biff > Thanks > [quoted text clipped - 27 lines] >> > >> > Thanks Sorry new to this! Actual ranges where I need the averages are b9:b17 and b24:b53. This range can contain zero value, which I need excluded... No negative values However there is a range in a different column that des have zero and negative values where I will need to average the negative values (i.e. n9:n17 and n24: n53 > Well, that's why it's a good idea to provide as much detail as possible when > posting a question. The formula I suggested only works when each range is [quoted text clipped - 37 lines] > >> > > >> > Thanks Try Domenic's second formula. As is, it will work on your first range in column B. You'll need to modifiy it slightly to get it to work on your other column, column N: Change: >0 To: <>0 Biff > Sorry new to this! > [quoted text clipped - 48 lines] >> >> > >> >> > Thanks If you download and install the free add-in Morefunc.xll, you can use the following formula... =AVERAGE(IF(SETV(ARRAY.JOIN(B9:B17,B24:B55))>0,GETV())) Alternatively, try... =AVERAGE(IF(ISNA(MATCH(ROW(B9:B55)-ROW(B9)+1,{10,11,12,13,14,15},0)),IF(B 9:B55>0,B9:B55))) ...which will exclude from the average the 10th through 15th cell within B9:B55, relative to B9. Note that both formulas needs to be confirmed with CONTROL+SHIFT+ENTER. Also, the add-in can be download at the following link... http://xcell05.free.fr/ Hope this helps! > Thanks > [quoted text clipped - 26 lines] > > > > > > Thanks You have *uneven* range sizes! You could try this *non-array* formula if you *don't* have negative values: =SUM(B9:B17,B24:B55)/(COUNTIF(B9:B17,">0")+COUNTIF(B24:B55,">0")) If you *do* have negatives, try this *non-array* formula : =SUM(B9:B17,B24:B55)/SUM(COUNTIF(B9:B17,{">0","<0"})+COUNTIF(B24:B55,{">0"," <0"}))  SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Thanks > > But when I use the formula {=AVERAGE(IF(CHOOSE({1,2},B9:B17,B24:B55)<>0,CHOOSE({1,2},B9:B17,B24:B55)))} > it returns a value of #N/A > [quoted text clipped - 20 lines] > > > > > > Thanks Used 2nd *non-array. Thanks Ragdyer and all > You have *uneven* range sizes! > [quoted text clipped - 38 lines] > > > > > > > > Thanks Thanks for the feed-back. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Used 2nd *non-array. > [quoted text clipped - 44 lines] >> > > > >> > > > Thanks As per JMB from another thread asking exactly the same question within a few hours...... =SUM(G7:G9,G12:G15)/(SUMPRODUCT(--(G7:G9<>0))+SUMPRODUCT(--(G12:G15<>0))) It is interesting that we can check for <>0 when we use SUMPRODUCT, but we can't check for <>0 when we use COUNTIF. This is because COUNTIF will also count null and blank. Epinn How do I calculate the averages of numbers in 2 sets of ranges... excluding zero vaules? Thanks |
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.