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

B

brucesw

Active Member

Joined

Nov 12, 2002

Messages

301

• Mar 25, 2003

• #2

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

 

Upvote 0

I

IML

MrExcel MVP

Joined

Feb 15, 2002

Messages

1,743

• Mar 25, 2003

• #3

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

 

Upvote 0

Jaafar Tribak

Well-known Member

Joined

Dec 5, 2002

Messages

9,604

Office Version

1. 2016

Platform

1. Windows

• Mar 25, 2003

• #4

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.

 

Upvote 0

2

2rrs

Active Member

Joined

Dec 25, 2002

Messages

346

• Mar 25, 2003

• #5

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

 

Upvote 0

NateO

Legend

Joined

Feb 17, 2002

Messages

9,700

• Mar 25, 2003

• #6

Howdy,

2rrs said:

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

Click to expand...

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

 

Upvote 0

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Mar 25, 2003

• #7

Hi Nate

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

http://www.mrexcel.com/board2/viewtopic.php?p=136110#136110

whether you'd want to is another matter!

paddy

 

Upvote 0

NateO

Legend

Joined

Feb 17, 2002

Messages

9,700

• Mar 25, 2003

• #8

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...

 

Upvote 0

I

IML

MrExcel MVP

Joined

Feb 15, 2002

Messages

1,743

• Mar 26, 2003

• #9

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"})))

 

Upvote 0