 |
 | 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 / April 2008Follow-up AVERAGEIF question
Office Excel 2007 The other day, someone here pointed me to the AVERAGEIF function as a way of averaging a range of cells, but excluding from the computation any cell in the range that had a zero value. I've been able to get that to work...partially. But if I insert new rows, the formula does not update to include those rows. For example, if I have number entries in cells A1 through A5, and if cell A6 is =AVERAGEIF(A1:A4,"<>0"), I get a result in A6. However, if I then insert a new row above A6 and enter a value there...of course the old A6 becomes A7, but the formula in the new A7 still read =AVERAGEIF(A1:A4,"<>0"). I would have expected it to change to (A1:A5,"<>0") This is what happens with the AVERAGE function. (Or the SUM function, for that matter.) How do I get the AVERAGEIF function to update to include newly-added rows? Thanks! Ken Isaacson SILENT COUNSEL, a legal thriller www.KenIsaacson.com One way: A6: =AVERAGEIF(A$1:INDEX(A:A,ROW()-2),"<>0") > Office Excel 2007 > [quoted text clipped - 21 lines] > SILENT COUNSEL, a legal thriller > www.KenIsaacson.com It depends on where you insert the new row. You said: >if I then insert a new row above A6 and enter a value there But you didn't say *exactly* where you inserted the new row. If you insert a new row that is *within* the referenced range then Excel will include that row in the calculation and adjust the range references accordingly. For example: >AVERAGEIF(A1:A4,"<>0") If you insert a new row 5 Excel does nothing since row 5 is not *within* the referenced range. If you insert a new row 1 Excel adjusts the range to A2:A5. If you insert a new row 4 then Excel will include that new row 4 and adjust the references to A1:A5.  SignatureBiff Microsoft Excel MVP > Office Excel 2007 > [quoted text clipped - 22 lines] > SILENT COUNSEL, a legal thriller > www.KenIsaacson.com
|
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.