# Getting Excel to use just one cell in named range

#### JenniferMurphy

##### Well-known Member
In the table below, cell D13 has been assigned the name "Score1", representing the first score in a series of scores. The entire D column (\$D:\$D) has been assigned the name "Score". In Col E, I can use that named range in an expression and Excel will use just the value on that row, not the entire range. But in Col F, that same name is treated as the entire range. To get the average from the current row to the end, I have to use the actual cell address as shows in Col G. I cannot use the named range.

Is there any way I can get Excel to use just the value from the current row in Average function?

 R/C C D E F G H 3 Date Scores =Score =AVERAGE(Score:Score1) =AVERAGE(D*:Score1) =AVERAGE(Score) 4 03/03/16 5 02/29/16 6 02/26/16 7 02/23/16 8 02/20/16 22 22 21.83 21.83 21.83 9 02/17/16 32 32 21.83 21.80 21.83 10 02/14/16 17 17 21.83 19.25 21.83 11 02/11/16 30 30 21.83 20.00 21.83 12 02/08/16 20 20 21.83 15.00 21.83 13 02/05/16 10 10 21.83 10.00 21.83

<tbody>
</tbody>

This seems inconsistent to me. Since "Score" is already "\$D:\$D", "(Score:Score1)" should resolve to "(\$D:\$D:D13)". Oddly, "=Average(\$D:\$D:D13)" does calculate the average for the entire column, but the syntax makes no sense to me.

The whole purpose of named ranges is to make formulas easier to read. This behavior by Excel seems to defeat that purpose.

It is also redundant. If I wanted the entire range, I just code "=Average(score)" as shown in Col H. In that case, I would have chosen the name "Scores" (plural), not "Score".

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
You would need:

=AVERAGE(INDEX(Score,ROW()):Score1)

You would need:

=AVERAGE(INDEX(Score,ROW()):Score1)

That's what I was afraid of. Does that make any sense to you? It sure defeats the objective os making formulas easier to read.

Thanks

When referenced alone a name returns the item in the relative row. But that's not the case when it's passed as an argument to a function. If it did you wouldn't be able to use e.g. =SUM(Score). So it makes perfect sense to me.

When referenced alone a name returns the item in the relative row. But that's not the case when it's passed as an argument to a function. If it did you wouldn't be able to use e.g. =SUM(Score). So it makes perfect sense to me.

Really? Then how do you explain "=sqrt(score)"?

The SQRT function expects a single value, not an array. The AVERAGE and SUM functions accept an array. Wouldn't you be concerned if you couldn't use SUM with a name that referred to multiple cells?

The SQRT function expects a single value, not an array. The AVERAGE and SUM functions accept an array. Wouldn't you be concerned if you couldn't use SUM with a name that referred to multiple cells?

What you said was, "But that's not the case when it's passed as an argument to a function.". I just showed you a function where that is the case. You even had one in your original example with the ROW function.

Yes, of course, AVERAGE & SUM are different than SQRT & SIN. Similarly, in the "a:b" syntax, both "a" and "b" are generally assumed to be scalars, just like the arguments to scalar functions like SQRT. If Excel were well designed, which it is nowhere close to being, AVERAGE(A:B) would treat both "A" and "B" as scalars, even if they were defined as arrays, just like they are treated in SQRT. On the other hand, AVERAGE(A) would treat A as an array if it could.

The current implementation is confusing, inconsistent, and typical of M\$FT.

OK, I should have said some functions. Rant over?

OK, I should have said some functions.
I think you should have differentiated between array and scalar functions.

Rant over?
Hardly. When it comes to M\$FT, the rant-worthy material is endless. I know it annoys the M\$FT apologists, it sounds like you are one, but the Office products are generally badly designed, and riddled with bugs, many of which have been around for 20+ years. Woody Leonhard has made a career out of lampooning M\$FT products and documenting bugs that never get fixed.

Do you disagree that the Office products are buggy and badly designed?

Name cell D4 as a named range, say DFour

Formula:
=AVERAGE(DFour:Score1)

(\$D:\$D:D13) is not a valid reference, pretty sure no one considers that a bug.

As a test: Change your Score name reference to D4:D13 instead of the whole D column.
In cell E14 type the formula =sqrt(score), do you get #VALUE?
Type =sum(score) do you get #VALUE, no, why not?

Do you disagree that the Office products are buggy and badly designed?
Yes.

Implicit intersections.

Last edited:

Replies
5
Views
219
Replies
5
Views
134
Replies
3
Views
160
Replies
1
Views
435
Replies
15
Views
225

1,218,746
Messages
6,144,264
Members
450,533
Latest member
xoxo1998

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back