How to use non-contiguous named range in AVERAGEIF function

[CrazyBritGuy]

The syntax for AVERAGEIF states:
Range Required. One or more cells to average, including numbers or names, arrays, or references that contain numbers.

But when I create a named range nonContig as =Sheet1!$F$5:$F$10,Sheet1!$F$13:$F$17

and in cell G3 enter =AVERAGEIF(noncontig,"<10") I get #VALUE

What's wrong with me?

 

[mrshl9898]

Can you not have the range as =Sheet1!$F$5:$F$17? The formula will work

 

[EFANYoutube]

Hi CrazyBritGuy
I wonder why you need to seperate these ranges, this should work just fine because you are removing blanks using your criteria

=AVERAGEIF(F5:F17,">10",F5:F17)

 

[Michael M]

Why not include the blank cells in the range so it's not non contigious??

 

[CrazyBritGuy]

Why not include the blank cells in the range so it's not non contigious??

Sorry, the example I used obfuscated the point I was assing about.

The point is that I trying to get is that on the Microsoft Support site: AVERAGEIF syntax definition

The AVERAGEIF function syntax has the following arguments:

• Range Required. One or more cells to average, including numbers or names, arrays, or references that contain numbers.
• Criteria Required. The criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.
• Average_range Optional. The actual set of cells to average. If omitted, range is used.

states that "Names" (which I assume means "NAMED RANGES") can be used but when I use one it return the #VALUE error.

So, maybe my question s/b How do you use NAMED RANGES

 

[CrazyBritGuy]

Can you not have the range as =Sheet1!$F$5:$F$17? The formula will work

See my reply to Michael M. response.

 

[Fluff]

You can use named ranges as you have done, but some functions will not work on non-contiguous ranges.

 

[CrazyBritGuy]

Hi CrazyBritGuy
I wonder why you need to seperate these ranges, this should work just fine because you are removing blanks using your criteria

=AVERAGEIF(F5:F17,">10",F5:F17)

See my reply to Michael M. response.

 

[CrazyBritGuy]

You can use named ranges as you have done, but some functions will not work on non-contiguous ranges.

See my reply to Michael M. response.

 

[Fluff]

I already have. Hence post#7