Getting Excel to use just one cell in named range

[JenniferMurphy]

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?

[TABLE="class: grid, width: 614"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Scores[/TD]
[TD="align: center"]=Score[/TD]
[TD="align: center"]=AVERAGE(Score:Score1)[/TD]
[TD="align: center"]=AVERAGE(D*:Score1)[/TD]
[TD="align: center"]=AVERAGE(Score)[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]03/03/16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]02/29/16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]02/26/16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]02/23/16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]02/20/16[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]21.83[/TD]
[TD="align: center"]21.83[/TD]
[TD="align: center"]21.83[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]02/17/16[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]21.83[/TD]
[TD="align: center"]21.80[/TD]
[TD="align: center"]21.83[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]02/14/16[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]21.83[/TD]
[TD="align: center"]19.25[/TD]
[TD="align: center"]21.83[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]02/11/16[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]21.83[/TD]
[TD="align: center"]20.00[/TD]
[TD="align: center"]21.83[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]02/08/16[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]21.83[/TD]
[TD="align: center"]15.00[/TD]
[TD="align: center"]21.83[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]02/05/16[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]21.83[/TD]
[TD="align: center"]10.00[/TD]
[TD="align: center"]21.83[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[JenniferMurphy]

Here's a table from a sheet:
[TABLE="class: grid, width: 650"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Values[/TD]
[TD="align: center"]=SQRT(Values)[/TD]
[TD="align: center"]=SQRT(ValuesCol)[/TD]
[TD="align: center"]=SUM(ValuesCol)[/TD]
[TD="align: center"]=SUM(Values)[/TD]
[TD="align: center"]=SUM(Values:C_8)[/TD]
[TD="align: center"]=SUM(C_4:Values)[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1.0000[/TD]
[TD="align: center"]1.0000[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1.4142[/TD]
[TD="align: center"]1.4142[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1.7321[/TD]
[TD="align: center"]1.7321[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]2.0000[/TD]
[TD="align: center"]2.0000[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]2.2361[/TD]
[TD="align: center"]2.2361[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]=SUM(C_4:C_8)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]=SUM(C4:C8:C8)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
It has the following names defined:
[TABLE="class: grid, width: 155"]
<tbody>[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]Range[/TD]
[/TR]
[TR]
[TD="align: center"]Values[/TD]
[TD="align: center"]$C$4:$C$8[/TD]
[/TR]
[TR]
[TD="align: center"]ValuesCol[/TD]
[TD="align: center"]$C:$C[/TD]
[/TR]
[TR]
[TD="align: center"]C_4[/TD]
[TD="align: center"]$C$4[/TD]
[/TR]
[TR]
[TD="align: center"]C_8[/TD]
[TD="align: center"]$C$8[/TD]
[/TR]
</tbody>[/TABLE]

Name cell D4 as a named range, say DFour

Formula:
=AVERAGE(DFour:Score1)

If you are saying that named cells can replace explicit addresses, I agree, but I don't know what the point is. I show this in F10 with the formula in G10.

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

I put something similar in F11 (the formula is shown in G11). Excel happily executed it, so, no, it is a valid reference. It shouldn't be, but it is. And, yes, I, at least, consider it a bug, so it's not "no one".

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?

In Col D, I use the named range VALUES, which is just those 5 cells. In Col E, I use the named range VALUESCOL, which is the entire column. Both columns get the exact same results. I do not get #VALUE.

Do you?

What is your point?

Type =sum(score) do you get #VALUE, no, why not?

In Cols F & G, I do sums with both named ranges. Both work the same.

Again, what is your point?

Implicit intersections.

This is a nice link, thanks. But I don't know what new information it provides other than the intersection with the named columns and the named row. Unless I am missing something, I don't see what it has to do with my original question about a scalar value from a named array working in some situations And not in others where it should.

In my opinion, the values in Col H should be 15, 14, 12, 9, 5 and the ones in Col I should be 1, 3, 6, 10, 15. Excel should treat either side of a range expression ("A:B") as a scalar value, not a range.

Please let me know if I missed something.