Averages..problem with 0s...can you please help!

Bytes37 · Mar 26th, 2003, 12:40 AM

I have a column with values in it. Some are 0s and some values are not. If I average the column, it gives me the average of all the values including the 0s. How do I make excel not count the 0s in computing an average?

Thanks!

brucesw · Mar 26th, 2003, 12:51 AM

It may not be simple, but...
Add a column with the formula;
=IF(datacell=0,"nil","excludes nil") Call the column "Flag"
Create a pivot table and drag your data into the data part of the wizard. Drag the new column "Flag" to the page part of the wizard, select finish.

If you select "excludes nil" from the dropdown next to "Flag" you'll get your answer.

Good luck

IML · Mar 26th, 2003, 12:52 AM

If you only have positve numbers, you could use
=SUM(A1:A10)/COUNTIF(A1:A10,">0")

otherwise array enter (control shift enter)
=AVERAGE(IF(A1:A4,A1:A4))

good luck

Jaafar Tribak · Mar 26th, 2003, 12:52 AM

Hi,

Assuming the range of wich you want to compute the average is A1:A10,
you can try this Array Formula:

{=SUM(IF(NOT(A1:A10),0,A1:A10))/COUNTIF(A1:A10,"<>0")}

Hope this helps.

2rrs · Mar 26th, 2003, 01:53 AM

Hi,
You can also use these;

Average contiguous cells containing zeros

SUM(range)/MAX(1,COUNT(range)-COUNTIF(range,0))

Average noncontiguous cells containing zeros

SUM(range)/COUNTIF(range,">"&0)

Hope this helps, 2rrs

NateO · Mar 26th, 2003, 02:09 AM

Howdy,

Quote:

Originally Posted by 2rrs

Average noncontiguous cells containing zeros
SUM(range)/COUNTIF(range,">"&0)

I don't think you can use countif on a noncontiguous range. But you can use something like this.

PaddyD · Mar 26th, 2003, 02:16 AM

Hi Nate

Takes a bit of work, but you can use countif on non-contiguous ranges:

http://www.mrexcel.com/board2/viewto...=136110#136110

whether you'd want to is another matter!

paddy

NateO · Mar 26th, 2003, 02:20 AM

Right Paddy, nice catch. Actually I had seen this here. I should have said, like as quoted... But yes, nice link Paddy, and I should consider my words a little more wisely...

IML · Mar 26th, 2003, 04:37 PM

Just for variety, the following non-array could also be used for positive and negative in a contiiguous range:
=SUM(A1:A10)/MAX(1,SUM(COUNTIF(A1:A10,{">0","<0"})))

Mar 26th, 2003, 12:52 AM	#4
Jaafar Tribak Join Date: Dec 2002 Location: Larache--Morocco Posts: 2,896	Hi, Assuming the range of wich you want to compute the average is A1:A10, you can try this Array Formula: {=SUM(IF(NOT(A1:A10),0,A1:A10))/COUNTIF(A1:A10,"<>0")} Hope this helps. __________________ Jaafar. Happiness is when what you think, what you say, and what you do are in harmony. http://www.laracheenelmundo.com/

Mar 26th, 2003, 02:20 AM	#8
NateO MrExcel MVP Admin Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,617	Right Paddy, nice catch. Actually I had seen this here. I should have said, like as quoted... But yes, nice link Paddy, and I should consider my words a little more wisely... __________________ Regards, Nate Oliver Microsoft Excel MVP Nate's Excel Blog

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

Mar 26th, 2003, 12:40 AM	#1
Bytes37 Join Date: Mar 2003 Posts: 20	Averages..problem with 0s...can you please help! I have a column with values in it. Some are 0s and some values are not. If I average the column, it gives me the average of all the values including the 0s. How do I make excel not count the 0s in computing an average? Thanks!

Mar 26th, 2003, 12:51 AM	#2
brucesw Join Date: Nov 2002 Location: Brisneyland, Australia Posts: 275	It may not be simple, but... Add a column with the formula; =IF(datacell=0,"nil","excludes nil") Call the column "Flag" Create a pivot table and drag your data into the data part of the wizard. Drag the new column "Flag" to the page part of the wizard, select finish. If you select "excludes nil" from the dropdown next to "Flag" you'll get your answer. Good luck

Mar 26th, 2003, 12:52 AM	#3
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	If you only have positve numbers, you could use =SUM(A1:A10)/COUNTIF(A1:A10,">0") otherwise array enter (control shift enter) =AVERAGE(IF(A1:A4,A1:A4)) good luck

Mar 26th, 2003, 01:53 AM	#5
2rrs Join Date: Dec 2002 Posts: 346	Hi, You can also use these; Average contiguous cells containing zeros SUM(range)/MAX(1,COUNT(range)-COUNTIF(range,0)) Average noncontiguous cells containing zeros SUM(range)/COUNTIF(range,">"&0) Hope this helps, 2rrs

Mar 26th, 2003, 02:16 AM	#7
PaddyD MrExcel MVP Join Date: May 2002 Posts: 12,554	Hi Nate Takes a bit of work, but you can use countif on non-contiguous ranges: http://www.mrexcel.com/board2/viewto...=136110#136110 whether you'd want to is another matter! paddy

Mar 26th, 2003, 04:37 PM	#9
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	Just for variety, the following non-array could also be used for positive and negative in a contiiguous range: =SUM(A1:A10)/MAX(1,SUM(COUNTIF(A1:A10,{">0","<0"})))