average formula

bricky · Apr 19th, 2002, 02:17 PM

I need to average the reults of 7 columns. Some columns are 0. How do I average columns with values greater than 0?

Aladin Akyurek · Apr 19th, 2002, 02:20 PM

Quote:

On 2002-04-19 13:17, bricky wrote:
I need to average the reults of 7 columns. Some columns are 0. How do I average columns with values greater than 0?

=SUM(A2:G100)/MAX(1,COUNT(A2:G100)-COUNTIF(A2:G100,0))

Aladin

John McGraw · Apr 19th, 2002, 02:21 PM

[ This Message was edited by: John McGraw on 2002-04-19 13:23 ]

Mark W. · Apr 19th, 2002, 02:22 PM

{=AVERAGE(IF(range>0,range))}

Or, if you meant <>0 simply...

{=AVERAGE(IF(range,range))}

Note: These are array formulas which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

bricky · Apr 19th, 2002, 02:26 PM

Thanks Aladin, it worked. Have a great day!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 19th, 2002, 02:17 PM	#1
bricky New Member Join Date: Apr 2002 Location: Bill Posts: 2	I need to average the reults of 7 columns. Some columns are 0. How do I average columns with values greater than 0?

Apr 19th, 2002, 02:21 PM	#3
John McGraw Board Regular Join Date: Feb 2002 Posts: 76	[ This Message was edited by: John McGraw on 2002-04-19 13:23 ]

Apr 19th, 2002, 02:22 PM	#4
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	{=AVERAGE(IF(range>0,range))} Or, if you meant <>0 simply... {=AVERAGE(IF(range,range))} Note: These are array formulas which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

Apr 19th, 2002, 02:26 PM	#5
bricky New Member Join Date: Apr 2002 Location: Bill Posts: 2	Thanks Aladin, it worked. Have a great day!