Microsoft Office Tutorials and References

In Depth Information

**RANK**

RAN
RANK
returns the rank of a number in a list of numbers.

=RANK(number,ref,order)

As seen in Figure 11.40, the
RANK
function is used to provide the relative position of the

number you specify in relation to the other numbers in the data set.

Is the number of the rank you want to determine.

NUMBER

Is the data set of values being assessed.

REF

Is an indicator of how the numbers should be ranked. If zero (0) or omitted,

the list is ranked as if it were in descending order. If any non-zero value, the

list is ranked as if it were in ascending order.

ORDER

Although duplicate numbers are treated as having the same rank, the numbering is effected

when duplicates are present. In Figure 11.40, there are several duplicates; number 84 appears

twice. 84 is ranked third, and 81 is ranked fifth; no number is ranked fourth.

Figure 11.40

Be sure the data set is

sorted by the values,

either ascending or

descending.

RSQ
RSQ
returns the r
2
value of a linear regression line.

=RSQ(known_y’s,known_x’s)

The r
2
value is the proportion of the variance in y attributed to the variance in x. Also known

as the Pearson product moment correlation coefficient (see the
PEARSON
function earlier in

this chapter). The closer r
2
is to one (1), the better the regression line fits your data. Figure

11.41 shows an example of calculating this function. If you add a linear trendline to an Excel

chart, you can have the r
2
value display on the chart.