Formula explaination

[Guest]

A person typed some numbers in column A (line 2 to line 10)
then in line 12 she wanted to show the maximum number in the column

to show that value, the only formula I know is .. =MAX(B6:B30)

However, she used the following formula:
=R:R+MAX(B6:B30)

can any one tell me what the "R:R" stands for? and why there is a plus mark?

Thank you

 

[Guest]

As written, the R:R+ does nothing. The R:R refers to the entire column R and
the plus is just a plus (addition symbol). It has the ultimate effect of
adding 0 to your Maximum number.

Where you'd use R:R would be inside a function that calls for a range. Such
as SUM(R:R) would give you the sum of all numbers in column R.

HTH,
Elkar

 

[Niek Otten]

I assume you confused A2:A10 and B6:B30?

You can see what happens if you click the Trace Precedents button. If there is nothing in the same row in column R, R:R results in
zero and you get the result you require. But it is strange she did it that way. R also means Row in the R1C1 reference style. The
way it is used here is an example of implicit intersection. Both addressing techniques are used very rarely.

I wonder why your colleague did it this way. I don't know of course, but I wouldn't be surprised if she just had no clue at all.
But then, your information wasn't exactly precise either.......

Please let us know if you find out.
--
Kind regards,

Niek Otten



|A person typed some numbers in column A (line 2 to line 10)
| then in line 12 she wanted to show the maximum number in the column
|
| to show that value, the only formula I know is .. =MAX(B6:B30)
|
| However, she used the following formula:
| =R:R+MAX(B6:B30)
|
| can any one tell me what the "R:R" stands for? and why there is a plus mark?
|
| Thank you

 

[Biff]

As written, the R:R+ does nothing.

Actually, it does the equivalent of:

R1+MAX(B6:B30)

Of course, if R1 was empty then it would be:

0+MAX(B6:B30)

If the formula:

=R:R+MAX(B6:B30)

was entered as an array into a range of cells then it would be the
equivalent of:

R1+MAX(B6:B30)
R2+MAX(B6:B30)
R3+MAX(B6:B30)
R4+MAX(B6:B30)
etc
etc

Biff

 

[Guest]

Hmm... my test resulted in no change regardless of what data was entered in
cell R1. Your response prompted further testing. You are correct only if
the formula is also entered in row 1.

So, to amend my original response, the R:R+ does do something after all. It
will take the value found in Column R of the same row that the formula is
entered into. Then add that value to the Maximum number.

So, if the formula is entered into cell B31, then the result would be the
value of cell R31 + the maximum value found in (B6:B30).

HTH,
Elkar

 

[Biff]

You are correct only if the formula is also entered in row 1

Try this:

Enter some random numbers in column R.

Enter some random numbers in B3:B30

Select the range A10:A15

Enter this formula as an array:

R:R+MAX(B3:B30)

A10 = R1+MAX(B3:B30)
A11 = R2+MAX(B3:B30)
A12 = R3+MAX(B3:B30)
etc

Biff

 

[Guest]

I agree that when entered as an array formula it will return the Max of
B6:B30 + R1, but when not entered as an array formula, it returns the max of
B6:B30 plus whatever is in the same row of column R (as Elkar said). So if
the formula is in B31, it will add R31 (or the 31st element from the array).

Interesting - I've never seen a formula written like this. It's either
genius or madness.