Getting Excel to use just one cell in named range

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,263
Office Version
365, 2016, 2007
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?

R/CCDEFGH
3DateScores=Score=AVERAGE(Score:Score1)=AVERAGE(D*:Score1)=AVERAGE(Score)
403/03/16
502/29/16
602/26/16
702/23/16
802/20/16222221.8321.8321.83
902/17/16323221.8321.8021.83
1002/14/16171721.8319.2521.83
1102/11/16303021.8320.0021.83
1202/08/16202021.8315.0021.83
1302/05/16101021.8310.0021.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".
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,263
Office Version
365, 2016, 2007
Platform
Windows
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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,263
Office Version
365, 2016, 2007
Platform
Windows
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)"?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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?
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,263
Office Version
365, 2016, 2007
Platform
Windows
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.
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,263
Office Version
365, 2016, 2007
Platform
Windows
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?
 

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
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:

Watch MrExcel Video

Forum statistics

Threads
1,095,978
Messages
5,447,679
Members
405,462
Latest member
est1989

This Week's Hot Topics

Top