My Named Range returns #VALUE! on Average

[stooner]

So I have the challenge of getting an average if disjointed cells. I thought I'd be tricking Excel into this by creating a Named Range for my values:

numbers
1 (-MyRange
(blank)
2 (-MyRange
(blank)
3 (-MyRange
(blank)
4 (-MyRange
(blank)
5 (-MyRange
(blank)
0 (-MyRange
When I do the formula:
=AVERAGE(If(MyRange>0,MyRange))
I get #VALUE!
but:
=AVERAGE(MyRange)
returns a number.
I hope this makes sense.
Thanks!

 

[Norie]

Enter the formula with CTRL+SHIFT+ENTER.

 

[stooner]

I thought this as well, but it still returns the value error.

 

[Aladin Akyurek]

So I have the challenge of getting an average if disjointed cells. I thought I'd be tricking Excel into this by creating a Named Range for my values:

numbers
1 (-MyRange
(blank)
2 (-MyRange
(blank)
3 (-MyRange
(blank)
4 (-MyRange
(blank)
5 (-MyRange
(blank)
0 (-MyRange
When I do the formula:
=AVERAGE(If(MyRange>0,MyRange))
I get #VALUE!
but:
=AVERAGE(MyRange)
returns a number.
I hope this makes sense.
Thanks!

If MyRange consists of non-contiguous (that is, how I interpret "disjointed") cells, try the following if there are no negative values:

=SUM(MyRange)/INDEX(FREQUENCY(MyRange,0),2)

Is this on the right track?

 

[stooner]

That's perfect! Thanks!

 

[Aladin Akyurek]

That's perfect! Thanks!

You are welcome. Thanks for providing feedback.