Getting Excel to use just one cell in named range

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,447
Office Version
  1. 365
Platform
  1. 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".
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

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,447
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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,447
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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,447
Office Version
  1. 365
Platform
  1. 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,130,187
Messages
5,640,693
Members
417,161
Latest member
Devon150

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top