Susan Harkins
ssharkins at gmail.com
Mon Aug 27 08:30:19 CDT 2007
Bill Patten sent me a simpler solution: =SUM(range)/COUNTIF(range,">0") It works well, but I didn't test it thoroughly because I realized it doesn't work for me. My situation is more complex than I stated because I wasn't really thinking. I need a ytd expression that includes only the passed months -- a passed month could include a 0 value, so Bill's expression doesn't work for me. I don't see any way around a separate expression for each month, but I did want to share Bill's expression with you guys because it's so simple -- I should've thought of it myself! ;) Bill's expression does exactly what I requested -- doesn't include 0's in a range. Susan H. You need to create an "array formula" (q.v. in Help.) "An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula." If your values are in cells C2:C7 enter the following into cell C8: =AVERAGE(IF(C2:C7<>0,C2:C7,"")) Instead of pressing Enter once you have typed this, press Ctrl+Shift+Enter. On 26 Aug 2007 at 14:07, Susan Harkins wrote: > Excel's AVG function uses 0's. Is there an easy way tell Excel to > ignore the 0's? I only want to average existing values, but I don't > want to update the range. > > Susan H. > > _______________________________________________ > dba-Tech mailing list > dba-Tech at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-tech > Website: http://www.databaseadvisors.com _______________________________________________ dba-Tech mailing list dba-Tech at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-tech Website: http://www.databaseadvisors.com No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.484 / Virus Database: 269.12.2/966 - Release Date: 8/22/2007 9:05 AM