Calculating Averages but excluding zero's
Hi, i want to calculate the average of a column but exclude anyzeros in that column. For example: If column A1 contained 2, 2, 0, 2, 2, 0 then the average would be 2. (8/4) NOT (8/6) Thanks. -- fodman ------------------------------------------------------------------------ fodman's Profile: http://www.excelforum.com/member.php...o&userid=31941 View this thread: http://www.excelforum.com/showthread...hreadid=555103 |
Calculating Averages but excluding zero's
Try this:
=AVERAGE(IF(A1:A60,A1:A6)) Enter this as an array formula. Use CTRL-SHIFT-ENTER instead of just Enter. HTH, Elkar "fodman" wrote: Hi, i want to calculate the average of a column but exclude anyzeros in that column. For example: If column A1 contained 2, 2, 0, 2, 2, 0 then the average would be 2. (8/4) NOT (8/6) Thanks. -- fodman ------------------------------------------------------------------------ fodman's Profile: http://www.excelforum.com/member.php...o&userid=31941 View this thread: http://www.excelforum.com/showthread...hreadid=555103 |
Calculating Averages but excluding zero's
One way is with this *array* formula:
=AVERAGE(IF(A1:A6<0,A1:A6)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "fodman" wrote in message ... Hi, i want to calculate the average of a column but exclude anyzeros in that column. For example: If column A1 contained 2, 2, 0, 2, 2, 0 then the average would be 2. (8/4) NOT (8/6) Thanks. -- fodman ------------------------------------------------------------------------ fodman's Profile: http://www.excelforum.com/member.php...o&userid=31941 View this thread: http://www.excelforum.com/showthread...hreadid=555103 |
All times are GMT +1. The time now is 06:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com