Extracting a column from a named range

[Andy Chan]

Dear all,

I have named a range "Scores" across 3 sheets (i.e.
Sheet1:Sheet3!$A$1:$B$10). How can I find the median of values in "Scores"
in Sheet1 ONLY? I don't know how to provide the target range in the function
MEDIAN().

Best Regards,
Andy Chan

 

[Ron Rosenfeld]

Dear all,

I have named a range "Scores" across 3 sheets (i.e.
Sheet1:Sheet3!$A$1:$B$10). How can I find the median of values in "Scores"
in Sheet1 ONLY? I don't know how to provide the target range in the function
MEDIAN().

Best Regards,
Andy Chan

=MEDIAN(Sheet3:Sheet1!A1:B10)


--ron

 

[Bob Phillips]

You won't be able to use the name if it spans many sheets, you will have to
use the cell references

=MEDIAN(Sheet1!$A$1:$B$10)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Andy Chan]

Dear Bob,

Why not? I just follow the instruction here:

http://office.microsoft.com/en-us/assistance/HP052015401033.aspx

It works. However, I don't know how to select the first "column".

Best Regards,
Andy

 

[Ron Rosenfeld]

=MEDIAN(Sheet3:Sheet1!A1:B10)


--ron

I neglected to mention that you can define scores as that cell reference:

scores =Sheet3:Sheet1!$A$1:$B$10


--ron

 

[Andy Chan]

I only want to compute the median of the data in Sheet1 only... But I want
to use the name of the range (Scores)... How can I do it?

 

[Niek Otten]

I'm sure it is possible one way or another.
But i do find you get yourself into trouble by choosing a rather complex way
to name a range and then want to use that name but mean something else.
The logic escapes me.
Can you explain what you're trying to do which makes this necessay?

 

[Ron Rosenfeld]

I only want to compute the median of the data in Sheet1 only... But I want
to use the name of the range (Scores)... How can I do it?

If I understand you correctly, you wish to use the name Scores on each of
several sheets, and have it refer the that sheet only.

One way to do this is to enter three names:

Navigate to Sheet1 and then define a name:

Instead of NAMEing as Scores, use the NAME Sheet1!Scores and set it equal to
A1:B10.

Then navigate to Sheet2 and define a name as Sheet2!Scores referring to
Sheet2!A1:B10.

The same for Sheet3.

If you are on the 'native' sheet, you can refer to just Scores -- e.g
=MEDIAN(Scores).

If you are on, let us say, Sheet1 and you want to refer to sheet3, you could
use the formula: =MEDIAN(Sheet3!Scores)


--ron

 

[Andy Chan]

Dear Niek,

My situation is like this: I want to maintain a file containing
information of 80 classes of students, the information of students of each
class is contained in one sheet. Every sheet has a similar structure, say
columns A to I are for name, gender, scores of each subject, etc. For
example, F1:F20 of each sheet contain scores of math exams of the
corresponding class. I want to find the 80 medians of scores of math exams,
as well as the overall median. Therefore, I want to name these ranges. Is it
clear? Thanks in advance!

Best Regards,
Andy

 

[Andy Chan]

Dear Ron,

Yes! That's what I want! Thanks!

Best Regards,
Andy

 

[Ron Rosenfeld]

Dear Ron,

Yes! That's what I want! Thanks!

Best Regards,
Andy

Glad to help. I'm also glad I finally understood the nature of your problem;
sorry it took me so long.


--ron