Averaging non-contiguous cells with zeros

S

Scabby

New Member

Joined

Aug 19, 2002

Messages

3

• Feb 19, 2003

• #2

bump

 

Upvote 0

N

needhelp1

New Member

Joined

Feb 18, 2003

Messages

1

• Feb 19, 2003

• #3

bump it up

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Feb 19, 2003

• #4

Hi scabby:

Would this do? (I did change some of your cell entries)

Book1
	A	B	C	D	E
1		12
2				15.66667
3
4		15
5
6
7		0
8
9
10		20
Sheet4

 

Upvote 0

NateO

Legend

Joined

Feb 17, 2002

Messages

9,700

• Feb 19, 2003

• #5

Howdy, I placed the cells in a named range called "MyRange" and played with the following:

Book3
	B	C	D	E
1	5		5	DavidHagerArray
2			5	SeparateCountIfs
3	5		#VALUE!	Yogi'ssuggestion(WhathaveIdonewrong?)
4
5	0
6
7	5
Sheet1

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by nateo on 2003-02-19 23:38

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Feb 19, 2003

• #6

Hi Nate:

For me it seems to work fine ...

Book1
	A	B	C	D	E
1		5
2
3		5		5
4
5		0
6
7		5
8
Sheet4 (2)

I can't say as of now why our results don't agree -- but let us continue to look at it.

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Feb 19, 2003

• #7

Hi Nate:

Just curious -- What cells does your MyRange cover? Mine is B1:B7.

 

Upvote 0

NateO

Legend

Joined

Feb 17, 2002

Messages

9,700

• Feb 19, 2003

• #8

That's probably why, MyRange in mine covers {B1,B3,B5,B7}...

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Feb 19, 2003

• #9

Hi Nate:

Indeed! -- the COUNTIF function that I used needs a continuous range.

 

Upvote 0