Loading

Calculating Averages but excluding zero's

Report thread as spam

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?action=getinfo&userid=31941 View this thread: http://www.excelforum.com/showthread.php?threadid=555103

This question was started by fodman 6 years ago

Try this:

=AVERAGE(IF(A1:A6>0,A1:A6))

Enter this as an array formula. Use CTRL-SHIFT-ENTER instead of just Enter.

HTH, Elkar

>

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?action=getinfo&userid=31941 View this thread: http://www.excelforum.com/showthread.php?threadid=555103

This response was posted by Elkar 6 years ago

One way is with this array formula:

=AVERAGE(IF(A1:A6<>0,A1:A6))

-- Array formulas must be entered with CSE, , instead of the regular , 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" fodman.29vdvz_1151091004.4666@excelforum-nospam.com wrote in message news:fodman.29vdvz_1151091004.4666@excelforum-nospam.com... >

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?action=getinfo&userid=31941 View this thread: http://www.excelforum.com/showthread.php?threadid=555103

This response was posted by Ragdyer 6 years ago

Other excel threads