JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,676
- Office Version
- 365
- Platform
- Windows
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".
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".